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 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
.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.
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
On Fri, Nov 18, 2022 at 11:53 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
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.)
I'm not really sure I'd want to change the behavior to perform multiple
updates even if we could. But in a green field development I would prefer
the error. Right now I'd side with introducing an error as well.
David J.