xmax not zero?

From: Luca Ferrari <fluca1978(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: xmax not zero?
Date: 2023-10-30 12:44:22
Message-ID: CAKoxK+4OkBvHteiK9ws=G5G2L5w_2QcR5+=XDzKUP6PN7JmrYw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Guillaume Lelarge 2023-10-30 12:53:50 Re: xmax not zero?
Previous Message Dimitrios Apostolou 2023-10-30 12:20:01 Re: BRIN index maintenance on table without primary key