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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)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.