Updating PK and all FKs to it in CTE - Mailing list pgsql-general

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