Re: Flaky vacuum truncate test in reloptions.sql

On Mon, Apr 5, 2021 at 5:00 AM Arseny Sher <a.sher@postgrespro.ru> wrote:
>
>
> On Fri, Apr 2, 2021 at 9:46 AM Michael Paquier <michael@paquier.xyz> wrote:
>
> > Okay, applied and back-patched down to 12 then.
>
> Thank you both. Unfortunately and surprisingly, the test still fails
> (perhaps even rarer, once in several hundred runs) under
> multimaster. After scratching the head for some more time, it seems to
> me the following happens: not only vacuum encounters locked page, but
> also there exist a concurrent backend (as the parallel schedule is run)
> who holds back oldestxmin keeping it less than xid of transaction which
> did the insertion
>
> INSERT INTO reloptions_test VALUES (1, NULL), (NULL, NULL);
>
> FreezeLimit can't be higher than oldestxmin, so lazy_check_needs_freeze
> decides there is nothing to freeze on the page. multimaster commits are
> quite heavy, which apparently shifts the timings making the issue more
> likely.
>
> Currently we are testing the rather funny attached patch which forces
> all such old-snapshot-holders to finish. It is crutchy, but I doubt we
> want to change vacuum logic (e.g. checking tuple liveness in
> lazy_check_needs_freeze) due to this issue. (it is especially crutchy in
> xid::bigint casts, but wraparound is hardly expected in regression tests
> run).

Or maybe we can remove reloptions.sql test from the parallel group.
BTW I wonder if the following tests in vacuum.sql test also have the
same issue (page skipping and oldestxmin):

-- TRUNCATE option
CREATE TABLE vac_truncate_test(i INT NOT NULL, j text)
        WITH (vacuum_truncate=true, autovacuum_enabled=false);
INSERT INTO vac_truncate_test VALUES (1, NULL), (NULL, NULL);
VACUUM (TRUNCATE FALSE) vac_truncate_test;
SELECT pg_relation_size('vac_truncate_test') > 0;
VACUUM vac_truncate_test;
SELECT pg_relation_size('vac_truncate_test') = 0;
VACUUM (TRUNCATE FALSE, FULL TRUE) vac_truncate_test;
DROP TABLE vac_truncate_test;

Should we add FREEZE to those tests as well?

Regards,

-- 
Masahiko Sawada
EDB:  https://www.enterprisedb.com/