Re: Updating PK and all FKs to it in CTE

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