On Tue, Apr 4, 2023 at 8:08 PM Aleksander Alekseev <
aleksander@timescale.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
