pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
slightly 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