Hi, Le lun. 30 oct. 2023 à 13:45, Luca Ferrari <fluca1...@gmail.com> a écrit :
> 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? > There are many reasons for a non-zero value: row updated or deleted in a rollbacked transaction, row updated or deleted in a current transaction, row locked by a SELECT FOR UPDATE, and perhaps others I don't remember right now. > 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) > > > Regards. -- Guillaume.
