"David G. Johnston" <david.g.johnston@gmail.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