slightly unexpected result
Hi,
imagine a simple table with 1 row
=# table tf;
i | x
---+----
1 | xx
(1 row)
And this query:
with x as (update tf set i=i+1 returning *)
, y as (update tf set x=x||'yy' returning *)
select * from x,y;
My PG14 gives this result
i | x | i | x
---+---+---+---
(0 rows)
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?
Thanks,
Torsten
On Wed, Jan 10, 2024 at 12:29:54PM +0100, Torsten Förtsch wrote:
Hi,
imagine a simple table with 1 row
=# table tf;
i | x
---+----
1 | xx
(1 row)And this query:
with x as (update tf set i=i+1 returning *)
, y as (update tf set x=x||'yy' returning *)
select * from x,y;My PG14 gives this result
i | x | i | x
---+---+---+---
(0 rows)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. Here is a reproducible case:
CREATE TABLE tf (i INT, x TEXT);
INSERT INTO tf VALUES (1, 'x');
WITH x AS (UPDATE tf SET i=i+1 RETURNING *),
y AS (UPDATE tf SET x=x||'yy' RETURNING *)
SELECT * FROM x,y;
i | x | i | x
---+---+---+---
TABLE tf;
i | x
---+---
2 | x
I know you can cascade the returning of one table into the update of
another table, but maybe it doesn't work into the same table.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
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?
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.
On Wed, Jan 10, 2024 at 09:06:31AM -0700, David G. Johnston wrote:
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.
Oh, good to know. :-)
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.