| 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: | Whole Thread | Raw Message | 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)
| 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 |