Thread: BUG #17689: Two UPDATE operators in common table expressions (CTE) perform not as expected

The following bug has been logged on the website:

Bug reference:      17689
Logged by:          Eugene Pliskin
Email address:      eugene.pliskin@gmail.com
PostgreSQL version: 14.5
Operating system:   Windows 10
Description:

The sample code below contains two UPDATE steps in a chain of common table
expressions (CTE).
First UPDATE step "t2b" modifies a row in a sample table.
Later down an unconditional UPDATE step "t5" fails to affect that row.
If step "t2a" is uncommented instead of "t2b" then step "t5" performs as
expected .

create table table1(id int, value1 int);
insert into table1 values
    (1, 10),
    (2, 20)
;
select * from table1 order by 1;
-- id  value1
-- 1    10       
-- 2    20       

do $$declare -- anonymous plpgsql block
  n2 int; n5 int;
begin
    with t1 as ( -- t1 has one row with (id = 1)
        select * from table1 where id = 1
    ), t2 as ( 
-- Experiment (t2a) does nothing:
--         select * from t1
-- Experiment (t2b) updates the first row of table1 with (value1 = 11):
        update table1 set value1 = 11 
        from t1
        where table1.id = t1.id
        returning table1.id
    ), t3 as (
        select count(*) as n2 from t2  -- n2 = 1
    ), t4 as (  -- t4 = table1, two rows
        select * from table1
    ), t5 as (   -- expect to update both rows of table1 with (value1 = 22)
and so it does after t2a
                 -- but only one row gets updated after t2b
        update table1 set value1 = 22 
        from t4
        where table1.id = t4.id
        returning table1.id
    ), t6 as (
        select count(*) as n5 from t5  -- expect n5 = 2
    ) select t3.n2, t6.n5
      from t3, t6
      into n2, n5;
  raise notice 'n2: %, n5: %', n2, n5; -- expected (n2: 1, n5: 2) but have
(n2: 1, n5: 1) in t2b
end$$ ;
-- t2a: (n5 = 2) as expected
-- t2b: unexpected (n5 = 1)

select * from table1 order by 1;
-- t2a: expected result, both rows have (value1 = 22)
-- id  value1
-- 1    22       
-- 2    22       
--
-- t2b: unexpected (value1 = 11) in the first row
-- id  value1
-- 1    11       
-- 2    22       
--------------------------------------------------

This is a documented limitation:

> Trying to update the same row twice in a single statement is not supported. Only one of the modifications takes
place,but it is not easy (and sometimes not possible) to reliably predict which one. This also applies to deleting a
rowthat was already updated in the same statement: only the update is performed. Therefore you should generally avoid
tryingto modify a single row twice in a single statement. In particular avoid writing WITH sub-statements that could
affectthe same rows changed by the main statement or a sibling sub-statement. The effects of such a statement will not
bepredictable. 

https://www.postgresql.org/docs/current/queries-with.html


.m

On 2022-Nov-18, Marko Tiikkaja wrote:

> This is a documented limitation:
> 
> > Trying to update the same row twice in a single statement is not
> > supported. Only one of the modifications takes place, but it is not
> > easy (and sometimes not possible) to reliably predict which one.
> > This also applies to deleting a row that was already updated in the
> > same statement: only the update is performed. Therefore you should
> > generally avoid trying to modify a single row twice in a single
> > statement. 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.
> 
> https://www.postgresql.org/docs/current/queries-with.html

I wonder if we should try to detect the case, and raise an error instead
of it resulting in undefined behavior.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> On 2022-Nov-18, Marko Tiikkaja wrote:
>> This is a documented limitation:
>>> Trying to update the same row twice in a single statement is not
>>> supported.

> I wonder if we should try to detect the case, and raise an error instead
> of it resulting in undefined behavior.

My recollection is that that is really fallout from an ancient and
intentional executor behavior, that we have to ignore multiple updates
in order to not get into infinite loops.  See comment about the
"Halloween problem" in nodeLockRows.c.  (I'm pretty sure there were once
more comments about that, somewhere closer to ExecUpdate/ExecDelete ---
this all dates back to Berkeley.)

            regards, tom lane