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

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. Only one of the modifications takes place, but it is not
> > easy (and sometimes not possible) to reliably predict which one.
> > This also applies to deleting a row that was already updated in the
> > same statement: only the update is performed. Therefore you should
> > generally avoid trying to modify a single row twice in a single
> > statement. In particular avoid writing WITH sub-statements that
> > could affect the same rows changed by the main statement or a
> > sibling sub-statement. The effects of such a statement will not be
> > predictable.
>
> https://www.postgresql.org/docs/current/queries-with.html

I wonder if we should try to detect the case, and raise an error instead
of it resulting in undefined behavior.

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/