Hi all,
I have a table that presents an xmax not zeroed outside of a
transaction block, and it does not look normal to me.
I have no idea about how this happened, it is one of my "toy"
PostgreSQL virtual machines.
But how to dig an better understand why there is an xmax with a non-zero value?
I've a snapshot for doing more tests.
testdb=> select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 16.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.3.1
20221121 (Red Hat 11.3.1-4), 64-bit
(1 row)
testdb=> select txid_current() as me, xmin, xmax, pk /* table real
column */, pg_snapshot_xmin( pg_current_snapshot() ), pg_snapshot_
xmax( pg_current_snapshot() ), pg_current_snapshot() from automobili limit 5;
me | xmin | xmax | pk | pg_snapshot_xmin |
pg_snapshot_xmax | pg_current_snapshot
---------+---------+---------+---------+------------------+------------------+---------------------
1713451 | 1533610 | 1675700 | 5000001 | 1713451 |
1713451 | 1713451:1713451:
1713451 | 1533610 | 1675700 | 5000003 | 1713451 |
1713451 | 1713451:1713451:
1713451 | 1533610 | 1675700 | 5000005 | 1713451 |
1713451 | 1713451:1713451:
1713451 | 1533610 | 1675700 | 5000007 | 1713451 |
1713451 | 1713451:1713451:
1713451 | 1533610 | 1675700 | 5000009 | 1713451 |
1713451 | 1713451:1713451:
testdb=> vacuum verbose automobili;
INFO: vacuuming "testdb.luca.automobili"
INFO: launched 1 parallel vacuum worker for index cleanup (planned: 1)
INFO: finished vacuuming "testdb.luca.automobili": index scans: 0
pages: 0 removed, 12738 remain, 12738 scanned (100.00% of total)
tuples: 0 removed, 1000000 remain, 0 are dead but not yet removable
removable cutoff: 1713454, which was 0 XIDs old when operation ended
new relfrozenxid: 1713454, which is 179844 XIDs ahead of previous value
frozen: 12738 pages from table (100.00% of total) had 1000000 tuples frozen
index scan not needed: 0 pages from table (0.00% of total) had 0 dead
item identifiers removed
I/O timings: read: 273.835 ms, write: 108.286 ms
avg read rate: 25.819 MB/s, avg write rate: 25.880 MB/s
buffer usage: 12776 hits, 12711 misses, 12741 dirtied
WAL usage: 38215 records, 12741 full page images, 60502693 bytes
system usage: CPU: user: 0.56 s, system: 0.21 s, elapsed: 3.84 s
INFO: vacuuming "testdb.pg_toast.pg_toast_76512"
INFO: finished vacuuming "testdb.pg_toast.pg_toast_76512": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 1713454, which was 0 XIDs old when operation ended
new relfrozenxid: 1713454, which is 186042 XIDs ahead of previous value
frozen: 0 pages from table (100.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (100.00% of total) had 0
dead item identifiers removed
I/O timings: read: 0.520 ms, write: 0.000 ms
avg read rate: 9.902 MB/s, avg write rate: 0.000 MB/s
buffer usage: 19 hits, 1 misses, 0 dirtied
WAL usage: 1 records, 0 full page images, 188 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
testdb=> select txid_current(), xmin, xmax, pk from automobili limit 5;
txid_current | xmin | xmax | pk
--------------+---------+------+---------
1713454 | 1533610 | 0 | 5000001
1713454 | 1533610 | 0 | 5000003
1713454 | 1533610 | 0 | 5000005
1713454 | 1533610 | 0 | 5000007
1713454 | 1533610 | 0 | 5000009
(5 rows)
