Re: Multiple FPI_FOR_HINT for the same block during killing btree index items

Em sex., 15 de mai. de 2020 às 18:53, Alvaro Herrera <
alvherre(at)2ndquadrant(dot)com> escreveu:

> On 2020-Apr-10, Masahiko Sawada wrote:
>
> > Okay. I think only adding the check would also help with reducing the
> > likelihood. How about the changes for the current HEAD I've attached?
>
> Pushed this to all branches. (Branches 12 and older obviously needed an
> adjustment.) Thanks!
>
> > Related to this behavior on btree indexes, this can happen even on
> > heaps during searching heap tuples. To reduce the likelihood of that
> > more generally I wonder if we can acquire a lock on buffer descriptor
> > right before XLogSaveBufferForHint() and set a flag to the buffer
> > descriptor that indicates that we're about to log FPI for hint bit so
> > that concurrent process can be aware of that.
>
> I'm not sure how that helps; the other process would have to go back and
> redo their whole operation from scratch in order to find out whether
> there's still something alive that needs killing.
>
> I think you need to acquire the exclusive lock sooner: if, when scanning
> the page, you find a killable item, *then* upgrade the lock to exclusive
> and restart the scan. This means that we'll have to wait for any other
> process that's doing the scan, and they will all give up their share
> lock to wait for the exclusive lock they need. So the one that gets it
> first will do all the killing, log the page, then release the lock. At
> that point the other processes will wake up and see that items have been
> killed, so they will return having done nothing.
>
Regarding the block, I disagree in part, because in the worst case,
the block can be requested in the last item analyzed, leading to redo all
the work from the beginning.
If we are in _bt_killitems it is because there is a high probability that
there will be items to be deleted,
why not request the block soon, if this meets the conditions?

1. XLogHintBitIsNeeded ()
2.! AutoVacuumingActive ()
3. New exclusive configuration variable option to activate the lock?

Masahiko reported that it occurs only when (autovacuum_enabled = off);

regards,
Ranier Vilela