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.
Hi,
On Tue, Feb 28, 2023 at 03:12:38AM +0000, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference: 17812
> Logged by: Zhenglong Li
> Email address: smartkeyerror@gmail.com
> PostgreSQL version: 15.2
> Operating system: Ubuntu 20.04
> Description:
>
> Step to reproduce the behavior
>
> We need 2 sessions to reproduce this bug.
>
> Firstly, we create a simple view that just fetches all the data from
> table1.
>
> And then we start a transaction and lock table1 with AccessExclusive Mode in
> Read Committed Transaction Isolation Level.
>
> After that, we try to use CTAS to create a temp table table2 using the data
> from table1 in session2, and this will be blocked by AccessExclusive Lock.
>
> Finally, we insert some data into table1 in session1 and commit it, session2
> will continue, but there is no data in table2.
>
> ```sql
> [...]
> TRUNCATE TABLE table1;
> [...]
This is not a bug, this is a documented corner case. In postgres TRUNCATE is
not fully MVCC, see
https://www.postgresql.org/docs/current/sql-truncate.html and
https://www.postgresql.org/docs/current/mvcc-caveats.html:
"TRUNCATE is not MVCC-safe. After truncation, the table will appear empty to
concurrent transactions, if they are using a snapshot taken before the
truncation occurred".
I guess the difference between referencing the table rather than the view is
that the query get stuck at execution time rather than planning time, meaning
that you do get a snapshot older than the INSERT in the session 1. Change the
TRUNCATE with a DELETE and you will get the same behavior for both cases.