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

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.

Julien Rouhaud <rjuju123(at)gmail(dot)com> 于2023年2月28日周二 21:52写道：

> 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(at)gmail(dot)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.
>