Re: Parallel vacuum workers prevent the oldest xmin from advancing

On Mon, Nov 15, 2021 at 12:38 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> I've updated the patch so that ProcArrayInstallRestoredXmin() sets
> both xmin and statusFlags only when the source proc is still running
> and xmin doesn't go backwards. IOW it doesn't happen that only one of
> them is set by this function, which seems more understandable
> behavior.
>

How have you tested this patch? As there was no test case presented in
this thread, I used the below manual test to verify that the patch
works. The idea is to generate a scenario where a parallel vacuum
worker holds back the xmin from advancing.

Setup:
-- keep autovacuum = off in postgresql.conf
create table t1(c1 int, c2 int);
insert into t1 values(generate_series(1,1000),100);
create index idx_t1_c1 on t1(c1);
create index idx_t1_c2 on t1(c2);

create table t2(c1 int, c2 int);
insert into t2 values(generate_series(1,1000),100);
create index idx_t2_c1 on t1(c1);

Session-1:
delete from t1 where c1 < 10; --this is to ensure that vacuum has some
work to do

Session-2:
-- this is done just to ensure the Session-1's xmin captures the value
of this xact
begin;
select txid_current(); -- say value is 725
insert into t2 values(1001, 100);

Session-1:
set min_parallel_index_scan_size=0;
-- attach a debugger and ensure to stop parallel worker somewhere
before it completes and the leader after launching parallel worker
vacuum t1;

Session-2:
-- commit the open transaction
commit;

Session-3:
-- attach a debugger and break at the caller of vacuum_set_xid_limits.
vacuum t2;

I noticed that before the patch the value of oldestXmin in Session-3
is 725 but after the patch it got advanced. I have made minor edits to
the attached patch. See, if this looks okay to you then please prepare
and test the patch for back-branches as well. If you have some other
way to test the patch then do share the same and let me know if you
see any flaw in the above verification method.

--
With Regards,
Amit Kapila.