Re: xmax not zero?

Hi,

Le lun. 30 oct. 2023 à 13:45, Luca Ferrari <fluca1978(at)gmail(dot)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.