Re: [PATCH] Clarify the behavior of the system when approaching XID wraparound

On Tue, Apr 4, 2023 at 8:08 PM Aleksander Alekseev <aleksander(at)timescale(dot)com>
wrote:
> [v6]

0001:

Looks good to me. I've just made some small edits for v7 and wrote a commit
message to explain how we got here. This can be backpatched all the way, as
it's simply a correction. I do want to test on v11 first just for
completeness. (The reality has already been tested by others back to 9.6
but there's no substitute for trying it yourself). I hope to commit soon
after that.

0002:

I've been testing wraparound using the v3 convenience function in [1] to
reach xidStopLimit:

-- reduce log spam
alter system set log_min_messages = error;
alter system set client_min_messages = error;
-- restart

-- no actual replication, just for testing dropping it
select pg_create_physical_replication_slot('foo', true, false);

create table t (i int);

BEGIN;
insert into t values(1);
PREPARE TRANSACTION 'trx_id_pin';

-- get to xidStopLimit
select consume_xids(1*1000*1000*1000);
insert into t values(1);
select consume_xids(1*1000*1000*1000);
insert into t values(1);
select consume_xids( 140*1000*1000);
insert into t values(1);
select consume_xids( 10*1000*1000);

SELECT datname, age(datfrozenxid) FROM pg_database;

-- works just fine
select pg_drop_replication_slot('foo');

COMMIT PREPARED 'trx_id_pin';

-- watch autovacuum take care of it automatically:
SELECT datname, age(datfrozenxid) FROM pg_database;

The consume_xids function builds easily on PG14, but before that it would
need a bit of work because data types changed. That coincidentally was the
first version to include the failsafe, which is convenient in this
scenario. I'd like to do testing on PG12/13 before commit, which would
require turning off truncation in the command (and can also be made faster
by turning off index cleanup), but I'm also okay with going ahead with just
going back to PG14 at first. That also safest.

I made some small changes and wrote a suitably comprehensive commit
message. I separated the possible uses for single-user mode into a separate
paragraph in the "Note:" , moved the justification for the 3-million xid
margin there, and restored the link to how to run it (I already mentioned
we still need this info, but didn't notice this part didn't make it back
in).

0003:

It really needs a more comprehensive change, and just making a long hint
even longer doesn't seem worth doing. I'd like to set that aside and come
back to it. I've left it out of the attached set.

[1]
https://www.postgresql.org/message-id/CAD21AoBZ3t%2BfRtVamQTA%2BwBJaapFUY1dfP08-rxsQ%2BfouPvgKg%40mail.gmail.com

--
John Naylor
EDB: http://www.enterprisedb.com