tuple-location-inconsistency-scenario-desc.txt
text/plain
Filename: tuple-location-inconsistency-scenario-desc.txt
Type: text/plain
Part: 0
postgres=# UPDATE pg_database SET datallowconn = 't' WHERE datname = 'template0';
UPDATE 1
postgres=# CREATE EXTENSION pageinspect;
CREATE EXTENSION
postgres=# CREATE TABLE xid_test (
ID int NOT NULL,
value varchar(255) NOT NULL,
PRIMARY KEY (ID)
);
CREATE TABLE
postgres=# insert into xid_test(ID, value) VALUES(1, 'test_val_11');
INSERT 0 1
postgres=# SELECT * FROM page_header(get_raw_page('xid_test', 0));
lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid | xid_base | multi_base
------------+----------+-------+-------+-------+---------+----------+---------+-----------+----------+------------
0/01850178 | 0 | 0 | 28 | 8136 | 8176 | 8192 | 5 | 0 | 752 | 0
(1 row)
postgres=# SELECT * FROM heap_page_items(get_raw_page('xid_test', 0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------------------------------
1 | 8136 | 1 | 40 | 755 | 0 | 0 | (0,1) | 2 | 2050 | 24 | | | \x0100000019746573745f76616c5f3131
(1 row)
postgres=#
postgres=# update xid_test set value = 'test_val_12' WHERE ID = 1;
UPDATE 1
postgres=# SELECT * FROM page_header(get_raw_page('xid_test', 0));
lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid | xid_base | multi_base
------------+----------+-------+-------+-------+---------+----------+---------+-----------+----------+------------
0/0189E100 | 0 | 0 | 32 | 8096 | 8176 | 8192 | 5 | 767 | 752 | 0
(1 row)
postgres=# SELECT * FROM heap_page_items(get_raw_page('xid_test', 0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------------------------------
1 | 8136 | 1 | 40 | 755 | 767 | 0 | (0,2) | 16386 | 258 | 24 | | | \x0100000019746573745f76616c5f3131
2 | 8096 | 1 | 40 | 767 | 0 | 0 | (0,2) | 32770 | 10242 | 24 | | | \x0100000019746573745f76616c5f3132
(2 rows)
postgres=# update xid_test set value = 'test_val_13' WHERE ID = 1;
UPDATE 1
postgres=# SELECT * FROM page_header(get_raw_page('xid_test', 0));
lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid | xid_base | multi_base
------------+----------+-------+-------+-------+---------+----------+---------+-----------+----------+------------
0/0189E1C8 | 0 | 0 | 36 | 8056 | 8176 | 8192 | 5 | 767 | 752 | 0
(1 row)
postgres=# SELECT * FROM heap_page_items(get_raw_page('xid_test', 0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------------------------------
1 | 8136 | 1 | 40 | 755 | 767 | 0 | (0,2) | 16386 | 1282 | 24 | | | \x0100000019746573745f76616c5f3131
2 | 8096 | 1 | 40 | 767 | 768 | 0 | (0,3) | 49154 | 8450 | 24 | | | \x0100000019746573745f76616c5f3132
3 | 8056 | 1 | 40 | 768 | 0 | 0 | (0,3) | 32770 | 10242 | 24 | | | \x0100000019746573745f76616c5f3133
(3 rows)
postgres=# DELETE FROM xid_test where id = 1;
DELETE 1
postgres=# SELECT * FROM page_header(get_raw_page('xid_test', 0));
lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid | xid_base | multi_base
------------+----------+-------+-------+-------+---------+----------+---------+-----------+----------+------------
0/018A3DF8 | 0 | 0 | 36 | 8056 | 8176 | 8192 | 5 | 767 | 752 | 0
(1 row)
postgres=# SELECT * FROM heap_page_items(get_raw_page('xid_test', 0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------------------------------
1 | 8136 | 1 | 40 | 755 | 767 | 0 | (0,2) | 16386 | 1282 | 24 | | | \x0100000019746573745f76616c5f3131
2 | 8096 | 1 | 40 | 767 | 768 | 0 | (0,3) | 49154 | 9474 | 24 | | | \x0100000019746573745f76616c5f3132
3 | 8056 | 1 | 40 | 768 | 769 | 0 | (0,3) | 40962 | 8450 | 24 | | | \x0100000019746573745f76616c5f3133
(3 rows)
pg_ctl stop
pg_resetwal -x 2147485000 -l 000000010000000000000002 $PGDATA
dd if=/dev/zero of=$PGDATA/pg_xact/000000000000800 bs=8192 count=2048
pg_ctl start
postgres=# SELECT * FROM page_header(get_raw_page('xid_test', 0));
lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid | xid_base | multi_base
------------+----------+-------+-------+-------+---------+----------+---------+-----------+----------+------------
0/018A3DF8 | -8146 | 0 | 36 | 8056 | 8176 | 8192 | 5 | 767 | 752 | 0
(1 row)
postgres=# SELECT * FROM heap_page_items(get_raw_page('xid_test', 0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------------------------------
1 | 8136 | 1 | 40 | 755 | 767 | 0 | (0,2) | 16386 | 1282 | 24 | | | \x0100000019746573745f76616c5f3131
2 | 8096 | 1 | 40 | 767 | 768 | 0 | (0,3) | 49154 | 9474 | 24 | | | \x0100000019746573745f76616c5f3132
3 | 8056 | 1 | 40 | 768 | 769 | 0 | (0,3) | 40962 | 8450 | 24 | | | \x0100000019746573745f76616c5f3133
(3 rows)
postgres=# insert into xid_test(ID, value) VALUES(2, 'test_val_21');
INSERT 0 1
postgres=# SELECT * FROM page_header(get_raw_page('xid_test', 0));
lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid | xid_base | multi_base
------------+----------+-------+-------+-------+---------+----------+---------+-----------+------------+------------
0/02001EE0 | -8146 | 0 | 40 | 8016 | 8176 | 8192 | 5 | 0 | 2147484997 | 0
(1 row)
postgres=# SELECT * FROM heap_page_items(get_raw_page('xid_test', 0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+------------+--------+----------+--------+-------------+------------+--------+--------+-------+------------------------------------
1 | 0 | 3 | 0 | | | | | | | | | |
2 | 0 | 0 | 0 | | | | | | | | | |
3 | 0 | 0 | 0 | | | | | | | | | |
4 | 8016 | 1 | 40 | 2147485000 | 0 | 0 | (0,4) | 2 | 2050 | 24 | | | \x0200000019746573745f76616c5f3231
(4 rows)
pg_waldump --path=$PGDATA/pg_wal/ --start=0/02001CD8 --end=0/02001EE0
rmgr: Standby len (rec/tot): 70/ 70, tx: 0, lsn: 0/02001CD8, prev 0/02000100, desc: RUNNING_XACTS nextXid 2147485000 latestCompletedXid 2147484999 oldestRunningXid 2147485000
rmgr: XLOG len (rec/tot): 53/ 225, tx: 2147485000, lsn: 0/02001D20, prev 0/02001CD8, desc: FPI_FOR_HINT , blkref #0: rel 1663/5/16431 blk 0 FPW
rmgr: Heap2 len (rec/tot): 72/ 72, tx: 2147485000, lsn: 0/02001E08, prev 0/02001D20, desc: PRUNE_ON_ACCESS snapshotConflictHorizon: 769, isCatalogRel: F, nplans: 0, nredirected: 0, ndead: 1, nunused: 2, dead: [1], unused: [2, 3], blkref #0: rel 1663/5/16431 blk 0
rmgr: Heap3 len (rec/tot): 60/ 60, tx: 2147485000, lsn: 0/02001E50, prev 0/02001E08, desc: BASE_SHIFT XactId delta 2147484245 , blkref #0: rel 1663/5/16431 blk 0
rmgr: Heap len (rec/tot): 75/ 75, tx: 2147485000, lsn: 0/02001E90, prev 0/02001E50, desc: INSERT off: 4, flags: 0x00, blkref #0: rel 1663/5/16431 blk 0