Re: BUG #17257: (auto)vacuum hangs within lazy_scan_prune()

On Thu, 2 May 2024 at 20:36, Alena Rybakina <lena.ribackina@yandex.ru> wrote:
>
> Hi! I also investigated this issue and reproduced it using this test added to the isolated tests, where I added 2
tuples,deleted them and ran vacuum and printed the tuple_deleted and dead_tuples statistics (I attached test c to this
emailas a patch). Within 400 iterations or more, I got the results: 
>
> n_dead_tup|n_live_tup|n_tup_del ----------------+------------+------------- 0| 0| 0 (1 row)
>
> After 400 or more running cycles, I felt the differences, as shown earlier:
>
>  n_dead_tup|n_live_tup|n_tup_del
>  ----------+----------+---------
> -         0|         0|        0
> +         2|         0|        0
>  (1 row)
>
>
> I debugged and found that the test produces results with 0 dead tuples if GlobalVisTempRels.maybe_needed is less than
thex_max of the tuple. In the code, this condition works in heap_prune_satisfies_vacuum: 
>
> else if (GlobalVisTestIsRemovableXid(prstate->vistest, dead_after))
> {
>      res = HEAPTUPLE_DEAD;
> }
>
> But when GlobalVisTempRels.maybe_needed is equal to the x_max xid of the tuple, vacuum does not touch this tuple,
becausethe heap_prune_satisfies_vacuum function returns the status of the RECENTLY_DEAD tuple. 
>
> Unfortunately, I have not found any explanation why GlobalVisTempRels.maybe_needed does not change after 400
iterationsor more. I'm still studying it. Perhaps this information will help you. 

You should probably check GlobalVisTestShouldUpdate to see why it
wouldn't get updated. IIRC, GobalVis* are only updated every so often
based on a local copy of a shared variable, to reduce contention on
the shared state in the proc arrays. It is quite likely that your
backend was waiting for new other changes, when the current
GlobalVisTempRels state was already "good enough" for the current
visibility snapshot.

> I reproduced the problem on REL_16_STABLE.
>
> I reproduced this test in the master branch as well, but used a more complex test for it: I added 700 tuples to the
table,deleted half of the table, and then started vacuum. I expected to get only 350 live tuples and 0 dead and deleted
tuples,but after 800 iterations I got 350 dead tuples and 350 live tuples: n_dead_tup|n_live_tup|n_tup_del 
>  ---------------+-------------
>
> +-------------
> -                0|          350|             0
> +          350|          350|             0
> (1 row)

I think this output is expected. Concurrent tasks (such as autovacuum
and autoanalyze) can have their own visibility snapshots and
transactions, which will block VACUUM from removing rows that may be
visible to those snapshots. Did you verify there was no other
(background) process active in the PostgreSQL test node during your
tests?

Kind regards,

Matthias van de Meent
Neon (https://neon.tech)