Re: BUG #17812: LOCK TABLE IN ACCESS EXCLUSIVE MODE with a view returns an empty tuple set

On Wed, Mar 01, 2023 at 10:01:29AM +0800, Keyerror Smart wrote:
> But when I removed the TRUNCATE STATEMENT in session1, session2 will still
> not have data:
>
> ```sql
> --------[ Sessions1 ]--------
> DROP TABLE IF EXISTS table1 CASCADE;
>
> CREATE TABLE table1 (
> zahl integer,
> upd_dat timestamp without time zone
> );
>
> CREATE OR REPLACE VIEW view1 as select zahl,upd_dat from table1;
>
> BEGIN;
> LOCK TABLE table1 IN ACCESS EXCLUSIVE MODE;
>
> --------[ Sessions 2 ]--------
> DROP TABLE IF EXISTS table2 CASCADE;
>
> CREATE TEMP TABLE table2 AS select zahl,upd_dat from view1;
> -- this will hang now waiting for a lock form session1
>
> --------[ Sessions 1 ]--------
> INSERT INTO table1 SELECT i zahl,clock_timestamp() upd_dat FROM
> generate_series(1,10) a(i);
> COMMIT;
>
> --------[ Sessions 2 ]--------
> SELECT * FROM table2 limit 10;
> zahl | upd_dat
> ------+---------
> (0 rows)
> ```
>
> So I think we can not blame the TRUNCATE is not fully MVCC.

Right, I was testing with a plain SELECT instead of CREATE TABLE AS.

This is however again due to when the lock is actually conflicting, leading to
different snapshot acquisition time.

For CTAS referencing the view, the lock is conflicting during execution, so it
has a snapshot that sees the data as they were before that transaction, as
required by ACID rules.

The CTAS referencing the table conflicts during parse analysis, so when the
lock is finally acquired and the parse analysis is done, it then acquires a new
snapshot for execution, which now sees the transaction as committed and thus
the newly added rows.

I still don't think this is a bug, however I'm not sure if/where those details
are documented.