Re: UPDATE modifies more rows that it should

On Sat, 17 Feb 2024 at 00:16, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> It seems to be caused by the FOR UPDATE. The first time through the
> Nested Loop finds the a=1 row, but on subsequent looks, the a=1 row is
> locked by the FOR UPDATE and hits the TM_SelfModified case in
> nodeLockRows.c which causes the goto lnext to trigger.

This probably needs more explanation than I've given... Because lock
rows does goto lnext on the 2nd execution after finding a=1 is
TM_SelfModified, we then ExecProcNode and the index gives us the a=2
row. This row happens to match the in the Seq scan, so the Nest Loop
condition passes. On the 3rd execution, the Lock rows skips a=1 and
a=2 and gets a=3 from the index, which again matches the current row
in the Seq Scan. That's why the CLUSTER order matters. You'll
notice I had to CLUSTER the table again to get it to "UPDATE 3" after
I ran the UPDATE without the FOR UPDATE in the subquery.

David