Thread: Updating PK and all FKs to it in CTE

Hi all,

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:

According to [1], I should be able to update all FKs and the PK within a 
single CTE. My CTE looks like this (a few tables left out for readability, they
follow the same pattern):

WITH update_catmaid_deep_link AS (
     UPDATE catmaid_deep_link
     SET active_skeleton_id = 12
     WHERE active_skeleton_id = 16150756
), update_class_instance_class_instance_a AS (
     UPDATE class_instance_class_instance
     SET class_instance_a = 12
     WHERE class_instance_a = 16150756
),
[…]
), update_catmaid_skeleton_summary AS (
     UPDATE catmaid_skeleton_summary
     SET skeleton_id = 12
     WHERE skeleton_id = 16150756
     AND project_id = 1
)
UPDATE class_instance
                                                     
 
SET id = 12
WHERE id = 16150756
AND project_id = 1;

However, when I try this, I still get an error about a conflict with a FK
constraint in a table updated in the CTE:

ERROR:  23503: update or delete on table "class_instance" violates foreign key constraint
"catmaid_skeleton_summary_skeleton_id_fkey"on table "catmaid_skeleton_summary"
 
DETAIL:  Key (id)=(16150756) is still referenced from table 
"catmaid_skeleton_summary".

It seems like the CTE change wasn't reflected when checking the constraint (?).

As can be seen in the table definition [2], the primary key comes with an
INCLUDE statement. Is this potentially a problem? Is such an update maybe not
always possible without DDL?

Thanks for any insight!

Cheers,
Tom

[1] https://stackoverflow.com/a/34383663/1665417
[2] Table definition:

# \d class_instance
                                      Table "public.class_instance"
     Column     |           Type           | Collation | Nullable |               Default               
---------------+--------------------------+-----------+----------+-------------------------------------
  id            | bigint                   |           | not null | nextval('concept_id_seq'::regclass)
  user_id       | integer                  |           | not null | 
  project_id    | integer                  |           | not null | 
  creation_time | timestamp with time zone |           | not null | now()
  edition_time  | timestamp with time zone |           | not null | now()
  txid          | bigint                   |           | not null | txid_current()
  class_id      | bigint                   |           | not null | 
  name          | character varying(255)   |           | not null | 
Indexes:
     "class_instance_id_pkey" PRIMARY KEY, btree (id) INCLUDE (class_id, project_id)
     "class_instance_class_id" btree (class_id)
     "class_instance_name_trgm_idx" gin (name gin_trgm_ops)
     "class_instance_project_id" btree (project_id)
     "class_instance_upper_name_idx" btree (upper(name::text))
     "class_instance_user_id" btree (user_id)
Foreign-key constraints:
     "class_instance_class_id_fkey" FOREIGN KEY (class_id) REFERENCES class(id) DEFERRABLE INITIALLY DEFERRED
     "class_instance_project_id_fkey" FOREIGN KEY (project_id) REFERENCES project(id) ON DELETE CASCADE DEFERRABLE
INITIALLYDEFERRED
 
     "class_instance_user_id_fkey" FOREIGN KEY (user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
     TABLE "catmaid_deep_link" CONSTRAINT "catmaid_deep_link_active_skeleton_id_fkey" FOREIGN KEY (active_skeleton_id)
REFERENCESclass_instance(id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
 
     TABLE "catmaid_sampler" CONSTRAINT "catmaid_sampler_skeleton_id_fkey" FOREIGN KEY (skeleton_id) REFERENCES
class_instance(id)ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
 
     TABLE "catmaid_skeleton_summary" CONSTRAINT "catmaid_skeleton_summary_skeleton_id_fkey" FOREIGN KEY (skeleton_id)
REFERENCESclass_instance(id) DEFERRABLE INITIALLY DEFERRED
 
     TABLE "class_instance_class_instance" CONSTRAINT "class_instance_class_instance_class_instance_a_fkey" FOREIGN KEY
(class_instance_a)REFERENCES class_instance(id) DEFERRABLE INITIALLY DEFERRED
 
     TABLE "class_instance_class_instance" CONSTRAINT "class_instance_class_instance_class_instance_b_fkey" FOREIGN KEY
(class_instance_b)REFERENCES class_instance(id) DEFERRABLE INITIALLY DEFERRED
 
     TABLE "connector_class_instance" CONSTRAINT "connector_class_instance_class_instance_id_fkey" FOREIGN KEY
(class_instance_id)REFERENCES class_instance(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
 
     TABLE "point_class_instance" CONSTRAINT "point_class_instance_class_instance_id_fkey" FOREIGN KEY
(class_instance_id)REFERENCES class_instance(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
 
     TABLE "region_of_interest_class_instance" CONSTRAINT "region_of_interest_class_instance_class_instance_id_fkey"
FOREIGNKEY (class_instance_id) REFERENCES class_instance(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
 
     TABLE "review" CONSTRAINT "review_skeleton_id_fkey" FOREIGN KEY (skeleton_id) REFERENCES class_instance(id) ON
DELETECASCADE DEFERRABLE INITIALLY DEFERRED
 
     TABLE "skeleton_origin" CONSTRAINT "skeleton_origin_skeleton_id_fkey1" FOREIGN KEY (skeleton_id) REFERENCES
class_instance(id)ON DELETE CASCADE
 
     TABLE "stack_class_instance" CONSTRAINT "stack_class_instance_class_instance_id_fkey" FOREIGN KEY
(class_instance_id)REFERENCES class_instance(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
 
     TABLE "stack_group_class_instance" CONSTRAINT "stack_group_class_instance_class_instance_id_fkey" FOREIGN KEY
(class_instance_id)REFERENCES class_instance(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
 
     TABLE "treenode_class_instance" CONSTRAINT "treenode_class_instance_class_instance_id_fkey" FOREIGN KEY
(class_instance_id)REFERENCES class_instance(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
 
     TABLE "treenode_connector" CONSTRAINT "treenode_connector_skeleton_id_fkey" FOREIGN KEY (skeleton_id) REFERENCES
class_instance(id)ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
 
     TABLE "treenode" CONSTRAINT "treenode_skeleton_id_fkey" FOREIGN KEY (skeleton_id) REFERENCES class_instance(id) ON
DELETECASCADE DEFERRABLE INITIALLY DEFERRED
 
     TABLE "volume_class_instance" CONSTRAINT "volume_class_instance_class_instance_id_fkey" FOREIGN KEY
(class_instance_id)REFERENCES class_instance(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
 
     TABLE "volume_origin" CONSTRAINT "volume_origin_volume_id_fkey" FOREIGN KEY (volume_id) REFERENCES
class_instance(id)ON DELETE CASCADE
 

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

On Thu, Jul 29, 2021 at 10:51:09AM -0400, Tom Lane wrote:
>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?

Thanks very much for trying this. I failed to create a self-contained 
example and just now realized what the problem is: a trigger function 
accidentally created an entry with the old ID (changing the primary key 
was not anticipated until now), throwing off the constraint check. I 
noticed when running this explicitly in a transaction and then 
inspecting the table that triggered the error and also got the same 
error with ON UPDATE CASCADE. My apologies, it just took a while for me 
to realize where this entry came from and that it was not the CTE or ON 
UPDATE CASCADE causing this. It's all resolved now and I am glad the CTE 
behaves as expected.

Kind regards,
Tom