MIME-Version: 1.0
References: <CABPTF7X1X810eT7hjRz9M69sjMv1Vui5gXQEGhqYXmkmywhHCQ@mail.gmail.com>
 <CAAKRu_ZcK+ez71W7j+QzMo0gSafTsbGA3TU-fVSptw7yh41BEQ@mail.gmail.com>
 <6BC5DBAB-6084-4BB8-8450-52E9648AB021@gmail.com> <CAAKRu_ZCjHoRPfQ8AbMrFY8TOMCPAvZ0_m9SX7yg0edfTk45-g@mail.gmail.com>
 <7F5BCD7A-764D-4D8D-8E27-6F2CAAEA1CEE@gmail.com> <CAAKRu_a04jbDACwzRYwzDND31aPyf7Yvz9TAZrTr=+F5bK1aVA@mail.gmail.com>
 <CALdSSPjcv25jmXm29X-MRWZBae6+HwcWfVH1PE8NfD=EMTnkAg@mail.gmail.com>
 <CAAKRu_bwtBEzDwemyim1r6yYonw7FTyFr1HXG8vywCe-MdbPBQ@mail.gmail.com>
 <4379FDA3-9446-4E2C-9C15-32EFE8D4F31B@yandex-team.ru> <CAAKRu_YQd=2KvomM+RHcpeDKj0bq+peJ=3W-fip+pkvzA-Jq9w@mail.gmail.com>
 <7ib3sa55sapwjlaz4sijbiq7iezna27kjvvvar4dpgkmadml6t@gfpkkwmdnepx>
 <CAAKRu_bs+gZ83QDacmBxunPvCGnXJ05hxP2BDPJ3BGwdbGRXzg@mail.gmail.com>
 <2b09fba6-6b71-497a-96ef-a6947fcc39f6@gmail.com> <CALdSSPjUUfdb5tL0hdH9m3PzRePDSFDBV-jfAj6BdzGE-8SBtw@mail.gmail.com>
In-Reply-To: <CALdSSPjUUfdb5tL0hdH9m3PzRePDSFDBV-jfAj6BdzGE-8SBtw@mail.gmail.com>
From: Kirill Reshke <reshkekirill@gmail.com>
Date: Thu, 29 Jan 2026 18:51:23 +0500
Message-ID: <CALdSSPhnQNYwEow9SoSOdgchifSgtaA2Whpeo+sPiHF_Mk6TjQ@mail.gmail.com>
Subject: Re: eliminate xl_heap_visible to reduce WAL (and eventually set VM on-access)
To: Alexander Lakhin <exclusion@gmail.com>
Cc: Melanie Plageman <melanieplageman@gmail.com>, Andres Freund <andres@anarazel.de>, 
	Andrey Borodin <x4mmm@yandex-team.ru>, Chao Li <li.evan.chao@gmail.com>, 
	Xuneng Zhou <xunengzhou@gmail.com>, Robert Haas <robertmhaas@gmail.com>, 
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Heikki Linnakangas <hlinnaka@iki.fi>
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CALdSSPhnQNYwEow9SoSOdgchifSgtaA2Whpeo%2BsPiHF_Mk6TjQ%40mail.gmail.com>
Precedence: bulk

On Thu, 29 Jan 2026 at 18:39, Kirill Reshke <reshkekirill@gmail.com> wrote:
>
> On Thu, 29 Jan 2026 at 10:00, Alexander Lakhin <exclusion@gmail.com> wrote:
> >
> > Hello Melanie,
> >
> > 29.01.2026 01:16, Melanie Plageman wrote:
> >
> > Thanks for the review!
> > I pushed v33 0001-0003 after incorporating your feedback.
> >
> >
> > The buildfarm animal scorpion has detected an instability of the addition
> > to pg_visibility from 21796c267 [1]:
> >
> > 80/82 postgresql:pg_visibility-running / pg_visibility-running/regress             ERROR             7.23s   exit status 1
> >
> > diff -U3 /home/bf/bf-build/scorpion/HEAD/pgsql/contrib/pg_visibility/expected/pg_visibility.out /home/bf/bf-build/scorpion/HEAD/pgsql.build/testrun/pg_visibility-running/regress/results/pg_visibility.out
> > --- /home/bf/bf-build/scorpion/HEAD/pgsql/contrib/pg_visibility/expected/pg_visibility.out    2026-01-26 22:07:12.923378464 +0100
> > +++ /home/bf/bf-build/scorpion/HEAD/pgsql.build/testrun/pg_visibility-running/regress/results/pg_visibility.out    2026-01-28 20:15:13.802517085 +0100
> > @@ -213,7 +213,7 @@
> >  select pg_visibility_map_summary('test_vac_unmodified_heap');
> >   pg_visibility_map_summary
> >  ---------------------------
> > - (1,1)
> > + (0,0)
> >  (1 row)
> >
> >  -- the checkpoint cleans the buffer dirtied by freezing the sole tuple
> > @@ -237,7 +237,7 @@
> >          FROM page_header(get_raw_page('test_vac_unmodified_heap', 0));
> >   ?column?
> >  ----------
> > - t
> > + f
> >  (1 row)
> >
> >  -- vacuum sets the VM
> >
> > I've managed to reproduce it locally with the attached and:
> > echo "autovacuum_naptime = 1" > /tmp/temp.config
> > TEMP_CONFIG=/tmp/temp.config make -s check -C contrib/pg_visibility/
> > ...
> > ok 85        - pg_visibility                              30 ms
> > not ok 86    - pg_visibility                             165 ms
> > ok 87        - pg_visibility                              36 ms
> > ...
> > # 1 of 100 tests failed.
> >
> > Could you please look at this?
> >
> > Probably you'll find [2] helpful.
> >
> > [1] https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=scorpion&dt=2026-01-28%2019%3A07%3A32
> > [2] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1c64d2fcb
> >
> > Best regards,
> > Alexander
>
>
> Thanks Alexander!
> This is a good and detailed report, I was able to reproduce this.
>
> I have added some logs to my copy of postgres with your patch and I
> think problem causing this test to fail is this sequence:
>
> 1) Autovacuum starts, does its deeds, and acquiring xid = 118518
> 2) insert into test_vac_unmodified_heap values (1); executes and
> commits with xid = 118519 (from my log)
> 3) vacuum freeze starts and computes cutoff xid = 118518, because
> oldest xmin is 118518 from (1)
>
> *and we cannot freeze tuple*
>
>
> ```
>
> 2026-01-29 13:27:44.559 UTC [133670] DEBUG:  CommitTransaction(1)
> name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid:
> 118519/1/0 (used)
> ...
> 2026-01-29 13:27:44.559 UTC [133672] DEBUG:  CommitTransaction(1)
> name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid:
> 118518/1/2
> ...
> 2026-01-29 13:27:44.560 UTC [133670] INFO:  finished vacuuming
> "contrib_regression.public.test_vac_unmodified_heap": index scans: 0
>         pages: 0 removed, 1 remain, 1 scanned (100.00% of total), 0
> eagerly scanned
>         tuples: 0 removed, 1 remain, 0 are dead but not yet removable
>         removable cutoff: 118518, which was 2 XIDs old when operation ended
>         new relfrozenxid: 118518, which is 1 XIDs ahead of previous value
>         frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
>         visibility map: 0 pages set all-visible, 0 pages set
> all-frozen (0 were all-visible)
>         index scan not needed: 0 pages from table (0.00% of total) had
> 0 dead item identifiers removed
>         avg read rate: 0.000 MB/s, avg write rate: 54.253 MB/s
>         buffer usage: 22 hits, 0 reads, 3 dirtied
>
>
> ```
>
> I did not come up with a fix yet though.
>
> --
> Best regards,
> Kirill Reshke


One possible way here is to remove regression test changes made in
21796c267, and rewrite this ad TAP test. In TAP test, we can do akin
to 006_singla_autovacuum.pl:

```
# From this point, autovacuum worker will wait at startup.
$node->safe_psql('postgres',
"SELECT injection_points_attach('autovacuum-worker-start', 'wait');");

```

So, we can remove autovacuum xid acquirition from the test.

Thoughts?

-- 
Best regards,
Kirill Reshke