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

On Tue, Feb 28, 2023 at 9:13 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > You may find the following to be informative as well.
>
> A general comment on these examples is that we don't intend MVCC
> guarantees to hold with respect to tables that are created/dropped
> by other sessions midway through your transaction.
>

That isn't what is in question here though...but also my comment regarding
planning seems incomplete...

table1 and view1 already exist, table1 is empty
view1 is select * from table1

Given that Session 1 does:
BEGIN;
LOCK TABLE table1 IN ACCESS EXCLUSIVE MODE;
-- sessions 2-5 now issue their commands
INSERT INTO table1 SELECT i zahl,clock_timestamp() upd_dat FROM
generate_series(1,10) a(i);
COMMIT;

The command and result from Sessions 2-5 are below:

Session 2:
select * from view1;
10 Rows

Session 3:
select * from table1;
10 Rows

Session 4:
create table tbl2 as select * from view1;
SELECT 0

Session 5:
create table tbl3 as select * from table1;
SELECT 10

Why is it OK for session 4 to be different here?

The argument that it got blocked after getting an execution snapshot that
doesn't include the insertion into table1, when all the other cases were
apparently blocked somewhere before then, doesn't sit well. In particular,
the difference between it (Session 4) and Session 2. That one sent the
result rows to a newly created temporary table and the other to the client
doesn't seem like it should be affecting/affected-by MVCC.

It is unclear to me whether you were instead talking about other sessions
dropping tables as another way of saying "ACCESS EXCLUSIVE" in which case
at what lock level should this anomaly go away, and does it? (I haven't
checked).

David J.

Sessions 2 and 4:

postgres=# select * from view1;
zahl | upd_dat
------+----------------------------
1 | 2023-03-01 04:33:28.628112
2 | 2023-03-01 04:33:28.628227
3 | 2023-03-01 04:33:28.628229
4 | 2023-03-01 04:33:28.62823
5 | 2023-03-01 04:33:28.62823
6 | 2023-03-01 04:33:28.62823
7 | 2023-03-01 04:33:28.628231
8 | 2023-03-01 04:33:28.628231
9 | 2023-03-01 04:33:28.628232
postgres=# create table tbl2 as select * from view1;
SELECT 0
postgres=# select * from tbl2;
zahl | upd_dat
------+---------
(0 rows)

Sessions 3 and 5:

postgres=# select * from table1;
zahl | upd_dat
------+----------------------------
1 | 2023-03-01 04:33:28.628112
2 | 2023-03-01 04:33:28.628227
3 | 2023-03-01 04:33:28.628229
4 | 2023-03-01 04:33:28.62823
5 | 2023-03-01 04:33:28.62823
6 | 2023-03-01 04:33:28.62823
7 | 2023-03-01 04:33:28.628231
8 | 2023-03-01 04:33:28.628231
postgres=# create table tbl3 as select * from table1;
SELECT 10
postgres=# select * from tbl3;
zahl | upd_dat
------+----------------------------
1 | 2023-03-01 04:36:00.762788
2 | 2023-03-01 04:36:00.762911
3 | 2023-03-01 04:36:00.762913
4 | 2023-03-01 04:36:00.762914
5 | 2023-03-01 04:36:00.762915
6 | 2023-03-01 04:36:00.762915
7 | 2023-03-01 04:36:00.762915
8 | 2023-03-01 04:36:00.762916