questions about wraparound

From: Luca Ferrari <fluca1978(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: questions about wraparound
Date: 2021-03-18 08:56:16
Message-ID: CAKoxK+5yPtx3-VOea_GJPp5fNHXc+EnGPUkBSh3ccx3B+0Sd=w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,
I'm doing some experiments on a cluster to see what happens at xid
wraparound, and I'm approaching it. There is no activity in any
database but testdb.
Scenario: I've a procedure that is consuming all xids, while another
connection is inserting a tuple every 20 minutes or so in a table,
just to prevent autovacuum to freeze in emergency. autovacuum is
globally turned off.

Therefore, I'm approaching wraparound:

testdb=> select datname, datfrozenxid, age( datfrozenxid ),
txid_current() from pg_database;WARNING: database "postgres" must be
vacuumed within 7989757 transactions
HINT: To avoid a database shutdown, execute a database-wide VACUUM in
that database.
You might also need to commit or roll back old prepared transactions,
or drop stale replication slots.
datname | datfrozenxid | age | txid_current
-----------+--------------+------------+--------------
postgres | 1221679879 | 2139493890 | 11951108361
backupdb | 1221679879 | 2139493890 | 11951108361
template1 | 1221679879 | 2139493890 | 11951108361
template0 | 1221679879 | 2139493890 | 11951108361
testdb | 1221679879 | 2139493890 | 11951108361
pgbench | 1221679879 | 2139493890 | 11951108361
(6 rows)

What puzzles me is that I'm somehow "locking" the testdb.wa table (by
inserting a tuple every 20 minutes), so all other tables and databases
are free to be frozen by an emergency autovacuum. And I was expecting
the problem to happen due to the testdb.wa table, and therefore the
hint message to be related to "testdb", not "postgres" database.
Digging I found that all the database are becoming old, and all the
tables in every database has the same age.
Therefore my question is: shouldn't autovacuum be able to freeze other
tables/databases? I mean, the wraparound problem in this scenario will
cause problems, but I was expecting different numbers for different
tables/databases.

The other question is: the xid is defined as a 32 bit integer:

typedef uint32 TransactionId;

but it is exposed as a 64 bit integer

typedef uint64 txid;

appending EpochFromFullTransactionId (that I'm not able to find in the
sources). The dumb question then is: why use the 32 bit machinery if
the txid is exposed as 64 bit wide value?

Thanks,
Luca

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Fabian Pijcke 2021-03-18 11:07:26 Domains and generated columns
Previous Message Andrew Anderson 2021-03-18 05:25:06 Re: WAL-files is not removing authomaticaly