pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedslightly unexpected result
4+ messages / 3 participants
[nested] [flat]
* slightly unexpected result
@ 2024-01-10 11:29 Torsten Förtsch <tfoertsch123@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Torsten Förtsch @ 2024-01-10 11:29 UTC (permalink / raw)
To: pgsql-general
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: slightly unexpected result
@ 2024-01-10 15:45 Bruce Momjian <bruce@momjian.us>
parent: Torsten Förtsch <tfoertsch123@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Bruce Momjian @ 2024-01-10 15:45 UTC (permalink / raw)
To: Torsten Förtsch <tfoertsch123@gmail.com>; +Cc: pgsql-general
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: slightly unexpected result
@ 2024-01-10 16:06 David G. Johnston <david.g.johnston@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2024-01-10 16:06 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Torsten Förtsch <tfoertsch123@gmail.com>; pgsql-general
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: slightly unexpected result
@ 2024-01-10 16:18 Bruce Momjian <bruce@momjian.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Bruce Momjian @ 2024-01-10 16:18 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Torsten Förtsch <tfoertsch123@gmail.com>; pgsql-general
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-01-10 16:18 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-01-10 11:29 slightly unexpected result Torsten Förtsch <tfoertsch123@gmail.com>
2024-01-10 15:45 ` Bruce Momjian <bruce@momjian.us>
2024-01-10 16:06 ` David G. Johnston <david.g.johnston@gmail.com>
2024-01-10 16:18 ` Bruce Momjian <bruce@momjian.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox