Closed yugo-n closed 2 years ago
The cause of the problem is the way to calculate "pre-update" state of base tables. When multiple tables are updated or the view contains a self-join, we need to calculate table states that was before it is modified. Currently we look at xmin and cmin system column for this purpose, but after a tuple is frozen, its xmin no longer has any meaning, so this way doesn't work.
Also, we can find similar inconsistency when using sub-transaction because xmin values do not always monotonically increasing by command executions. Here is an example:
test=# \d+ mv
Materialized view "public.mv"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
x | integer | | | | plain | | |
y | integer | | | | plain | | |
View definition:
SELECT t.i AS x,
t2.i AS y
FROM t,
t t2;
Access method: heap
Incremental view maintenance: yes
test=# begin ;
BEGIN
test=*# savepoint p;
SAVEPOINT
test=*# insert into t values (1);
INSERT 0 1
test=*# select xmin, * from t;
xmin | i
------+---
31 | 1
(1 row)
test=*# select * from mv;
x | y
---+---
1 | 1
(1 row)
test=*# release savepoint p;
RELEASE
test=*# insert into t values (1);
INSERT 0 1
test=*# select xmin, * from t;
xmin | i
------+---
31 | 1
30 | 1
(2 rows)
test=*# select * from mv;
x | y
---+---
1 | 1
1 | 1
1 | 1
(3 rows)
test=*# select * from t, t t2;
i | i
---+---
1 | 1
1 | 1
1 | 1
1 | 1
(4 rows)
After xid wraparound, inserting a tuple into a base table causes inconsistent results.
The view definition
After inserting the first tuple, use pg_resetwal to simulate xid wraparound
$ vacuumdb -F -a; pg_ctl stop; pg_resetwal -x 0x100000010 -D data_ivm; pg_ctl start
test=# select xmin, * from t; xmin | i ------+--- 728 | 1 (1 row)
test=# select * from mv; x | y ---+--- 1 | 1 (1 row)
test=# insert into t values (2); INSERT 0 1 test=# select xmin, * from t; xmin | i ------+--- 728 | 1 27 | 2 (2 rows)
test=# select * from mv; x | y ---+--- 1 | 1 1 | 2 2 | 2 (3 rows)
test=# select * from t, t as t2; i | i ---+--- 1 | 1 1 | 2 2 | 1 2 | 2 (4 rows)