Tom Kazimiers <tom@voodoo-arts.net> writes:
> I am on Postgres 13 and have a problem with updates in a CTE. While certainly
> not generally recommended, I need to update the primary key in a table that is
> referenced by a few other tables. The table definition is attached to the end
> of this email [2]. I'd like to avoid dropping and recreating the constraints or
> even columns, because these tables can become quite large. While I could define
> the FK constraints as ON UPDATE CASCADE, I wonder why an alternative solution
> using a CTE doesn't work:
I tried to reproduce your problem and failed; the attached script
acts as I'd expect. So there must be some moving part you've not
mentioned. Can yo create a similar self-contained example that
fails?
regards, tom lane
drop table if exists pt, c1, c2;
create table pt (id int primary key);
create table c1 (id int primary key, ref int references pt
deferrable initially deferred);
create table c2 (id int primary key, ref int references pt
deferrable initially deferred);
insert into pt values(11);
insert into c1 values(1, 11);
insert into c2 values(2, 11);
update pt set id = 12 where id = 11; -- fails
with u1 as (
update c1 set ref = 12 where ref = 11
)
update pt set id = 12 where id = 11; -- fails
with u1 as (
update c1 set ref = 12 where ref = 11
), u2 as (
update c2 set ref = 12 where ref = 11
)
update pt set id = 12 where id = 11; -- ok
