Обсуждение: slightly unexpected result

Поиск
Список
Период
Сортировка

slightly unexpected result

От
Torsten Förtsch
Дата:
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

Re: slightly unexpected result

От
Bruce Momjian
Дата:
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.



Re: slightly unexpected result

От
"David G. Johnston"
Дата:
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.

"""
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.

Re: slightly unexpected result

От
Bruce Momjian
Дата:
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.