Hi all,
On 07.07.2025 11:17, Evgeny Voropaev wrote:
Do-side:
1. Having page ABC with several tuples.
2. Starting to perform insertion of new tuple
2.1. In the case of an inappropriate xid_base, trying to fit base
2.1.1 Freezing and pruning tuples without further repairing
fragmentation.
2.1.2 All tuples have been pruned (no alive tuples on the page
since this moment)
3. Inserting a new tuple and setting XLOG_HEAP_INIT_PAGE, assuming
that the only tuple located at the bottom of the page (assuming that
fragmentation has been performed).
Result: We have the ABC page with the new tuple inserted somewhere in
the MIDDLE of the page and surrounded with garbage from dead and
unused tuples. At the same time we have an xlog record bringing the
XLOG_HEAP_INIT_PAGE bit.
Redo-side
1. Observing XLOG_HEAP_INIT_PAGE
2. Creating a new page and inserting the new tuple into the first
position of the page.
Evgeny, I've tried to reproduce described scenario, but I can't get
XLOG_HEAP_INIT_PAGE bit set.
Short steps description.
1. create test table, insert tuple, then exec few update operations,
then exec delete operation,
all tuples placed on one page and "select * from test_table;" return 0
rows.
2. stop postgres and use pg_resetwal to promote xid. This will help us
to prune tuples.
3. start postgres and insert new tuple on page, old tuples pruning
occurs and here we expect
XLOG_HEAP_INIT_PAGE bit set in xlog rec. As I understand, it is the
reason of further tuple location
inconsistency.
But as I see, XLOG_HEAP_INIT_PAGE bit isn't set. The reason is condition
if (ItemPointerGetOffsetNumber(&(heaptup->t_self)) ==
FirstOffsetNumber &&
PageGetMaxOffsetNumber(page) == FirstOffsetNumber)
{
info |= XLOG_HEAP_INIT_PAGE;
bufflags |= REGBUF_WILL_INIT;
}
As I see, new heaptup offset isn't equal to FirstOffsetNumber and it is
quite enough to skip bit set.
I've used waldump to check bit set
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
, so offset is 4 and desc: INSERT, in case XLOG_HEAP_INIT_PAGE is set,
it should be INSERT+INIT.
In attached file you can find detailed test description with results, so
you can repeat it.
Maybe I missed something in your scenario, can you please clarify?
Or maybe it is't reproducable at all.
Best regards,
Maksim Melnikov
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
