Re: BUG #17689: Two UPDATE operators in common table expressions (CTE) perform not as expected

On Fri, Nov 18, 2022 at 11:53 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> > On 2022-Nov-18, Marko Tiikkaja wrote:
> >> This is a documented limitation:
> >>> Trying to update the same row twice in a single statement is not
> >>> supported.
>
> > I wonder if we should try to detect the case, and raise an error instead
> > of it resulting in undefined behavior.
>
> My recollection is that that is really fallout from an ancient and
> intentional executor behavior, that we have to ignore multiple updates
> in order to not get into infinite loops.  See comment about the
> "Halloween problem" in nodeLockRows.c.  (I'm pretty sure there were once
> more comments about that, somewhere closer to ExecUpdate/ExecDelete ---
> this all dates back to Berkeley.)
>
>
I'm not really sure I'd want to change the behavior to perform multiple
updates even if we could.  But in a green field development I would prefer
the error.  Right now I'd side with introducing an error as well.

David J.