Horton

Thread

  1. xmax not zero?

    Luca Ferrari <fluca1978@gmail.com> — 2023-10-30T12:44:22Z

    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)

  2. Re: xmax not zero?

    Guillaume Lelarge <guillaume@lelarge.info> — 2023-10-30T12:53:50Z

    Hi,

Le lun. 30 oct. 2023 à 13:45, Luca Ferrari <fluca1978@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.

  3. Re: xmax not zero?

    Laurenz Albe <laurenz.albe@cybertec.at> — 2023-10-30T13:24:45Z

    On Mon, 2023-10-30 at 13:53 +0100, Guillaume Lelarge wrote:
> Le lun. 30 oct. 2023 à 13:45, Luca Ferrari <fluca1978@gmail.com> a écrit :
> > 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.

INSERT ... ON CONFLICT could also cause this.
As long as the transaction corresponding to the "xid" is marked as "aborted",
that number is not valid and is treated like a 0.

Yours,
Laurenz Albe

  4. Re: xmax not zero?

    Luca Ferrari <fluca1978@gmail.com> — 2023-10-31T07:23:02Z

    On Mon, Oct 30, 2023 at 1:54 PM Guillaume Lelarge
<guillaume@lelarge.info> wrote:
> 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.
>

Yes, clearly. What puzzled me was that such xmax was non-zero for a
while (days). Inspecting pg_class.reloptions I found that accidentally
(?) disabled autovacuum on such table, my fault!
I should have checked pg_stat_user_tables at first!

Thanks,
Luca

  5. Re: xmax not zero?

    Laurenz Albe <laurenz.albe@cybertec.at> — 2023-10-31T08:44:54Z

    On Tue, 2023-10-31 at 08:23 +0100, Luca Ferrari wrote:
> What puzzled me was that such xmax was non-zero for a
> while (days). Inspecting pg_class.reloptions I found that accidentally
> (?) disabled autovacuum on such table, my fault!
> I should have checked pg_stat_user_tables at first!

VACUUM won't modify "xmax", nor will it remove the row.

Yours,
Laurenz Albe