Re: slightly unexpected result

On Wed, Jan 10, 2024 at 8:46 AM Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Wed, Jan 10, 2024 at 12:29:54PM +0100, Torsten Förtsch wrote:
>
> >
> > To me that was a bit surprising. I would have expected it to fail with
> > something like "can't update the same row twice in the same command".
> >
> > If I check the table content after the query I see the i=i+1 part was
> executed.
> >
> > Is this expected behavior?
>
> Yes, this surprised me too.

It is mostly documented.

https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING
"""
Only one of the modifications takes place, but it is not easy (and
sometimes not possible) to reliably predict which one.
...
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.
"""

Yes, an error would be nice, but the effort put forth stops at
unpredictable, and saying just don't do it.

David J.