Re: Using indexUnchanged with nbtree

On Fri, Jun 25, 2021 at 2:34 AM Peter Geoghegan  wrote:
>
> On Thu, Jun 24, 2021 at 5:39 AM Simon Riggs
>  wrote:
> > This case occurs when we are doing non-HOT UPDATEs. That command is
> > searched, so the scan will already have touched the heap and almost
> > certainly the index also, setting any LP_DEAD bits already in the most
> > frequent case.
>
> But it won't, because the restriction that I described with non-HOT
> updates in kill_prior_tuple in that old thread I linked to. This has
> been the case since commit 2ed5b87f96d from Postgres 9.5. This
> probably should probably be fixed, somehow, but for now I don't think
> you can assume anything about LP_DEAD bits being set -- they're
> clearly not set with a non-HOT update when the UPDATE's ModifyTable
> node is fed by a scan of the same index (unless we reach
> _bt_check_unique() because it's a unique index).

Seems a little bizarre to have _bt_check_unique() call back into the
heap block we literally just unpinned.
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

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.

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?

-- 
Simon Riggs                http://www.EnterpriseDB.com/