On Wed, Jan 10, 2024 at 8:46 AM Bruce Momjian <bruce@momjian.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?
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.