Re: Using indexUnchanged with nbtree

On Fri, Jun 25, 2021 at 1:43 AM Simon Riggs
<simon(dot)riggs(at)enterprisedb(dot)com> wrote:
> Seems a little bizarre to have _bt_check_unique() call back into the
> heap block we literally just unpinned.

I suppose it is a little bizarre.

> This is another case of the UPDATE scan and later heap/index
> insertions not working together very well.
> This makes this case even harder to solve:
> https://www.postgresql.org/message-id/CA%2BU5nMKzsjwcpSoqLsfqYQRwW6udPtgBdqXz34fUwaVfgXKWhA%40mail.gmail.com

I wasn't aware of that thread, but I suspected that something like
that was going on in some cases myself.

> If an UPDATE interferes with its own ability to kill_prior_tuple(),
> then we should fix it, not allow pointless work to be performed
> somewhere else instead just because it has some beneficial side
> effect.

Definitely true. But the fact is that this is where we are today, and
that complicates this business with bypassing _bt_check_unique().

> If an UPDATE scans via a index and remembers the block in
> so->currPos.currPage then we could use that to optimize the
> re-insertion by starting the insertion scan at that block (since we
> know the live unique key is either there or somewhere to the right).
> By connecting those together, we would then be able to know that the
> change in LSN was caused by ourself and allow the items to be killed
> correctly at that time.
>
> Do you think there is benefit in having PK UPDATEs as a special plan
> that links these things more closely together?

I think that it might be worth hinting to the index scan that it is
feeding a ModifyTable node, and that it should not drop its pin per
the optimization added to avoid blocking VACUUM (in commit
2ed5b87f96d). We can just not do that if for whatever reason we don't
think it's worth it - the really important cases for that optimization
involve cursors, things like that.

It's not like the code that deals with this (that notices LSN change)
cannot just recheck by going to the heap. The chances of it really
being VACUUM are generally extremely low.

OTOH I wonder if the whole idea of holding a pin on a leaf page to
block VACUUM is one that should be removed entirely.

--
Peter Geoghegan