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 INITIALLY DEFERRED
"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) REFERENCES class_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) REFERENCES class_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" FOREIGN KEY (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 DELETE CASCADE 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 DELETE CASCADE 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
