Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION

Hi Tom,

> On 26 Nov 2024, at 22:25, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> I would have expected an empty result too. Can you confirm that
> p_ci_pipelines used to be a partition of something? Can you show us
> the full DDL (or psql \d+ output) for the partitioned table it
> used to be part of, and for that matter also for p_ci_pipelines?
> Did the FK used to reference the whole partitioned table, or just
> this partition?
>
> I'm suspicious that our repair recipe might not have accounted
> for self-reference FKs fully, but that's just a gut feeling at
> this point.

Of course, it contains no secret data. Please find the full log below. According to the add constraint statement, it is a self reference.

Thanks for looking into it.

Cheers,
Paul

gitxp1t=# \set
AUTOCOMMIT = 'on'
...
VERSION = 'PostgreSQL 15.10 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux) 7.5.0, 64-bit'
...

gitxp1t=# SELECT conrelid::pg_catalog.regclass AS "constrained table",
gitxp1t-# conname AS constraint,
gitxp1t-# confrelid::pg_catalog.regclass AS "references",
gitxp1t-# pg_catalog.format('ALTER TABLE %s DROP CONSTRAINT %I;',
gitxp1t(# conrelid::pg_catalog.regclass, conname) AS "drop",
gitxp1t-# pg_catalog.format('ALTER TABLE %s ADD CONSTRAINT %I %s;',
gitxp1t(# conrelid::pg_catalog.regclass, conname,
gitxp1t(# pg_catalog.pg_get_constraintdef(oid)) AS "add"
gitxp1t-# FROM pg_catalog.pg_constraint c
gitxp1t-# WHERE contype = 'f' AND conparentid = 0 AND
gitxp1t-# (SELECT count(*) FROM pg_catalog.pg_constraint c2
gitxp1t(# WHERE c2.conparentid = c.oid) <>
gitxp1t-# (SELECT count(*) FROM pg_catalog.pg_inherits i
gitxp1t(# WHERE (i.inhparent = c.conrelid OR i.inhparent = c.confrelid) AND
gitxp1t(# EXISTS (SELECT 1 FROM pg_catalog.pg_partitioned_table
gitxp1t(# WHERE partrelid = i.inhparent));
constrained table | constraint | references | drop | add -------------------+-----------------+----------------+-------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
p_ci_pipelines | fk_262d4c2d19_p | p_ci_pipelines | ALTER TABLE p_ci_pipelines DROP CONSTRAINT fk_262d4c2d19_p; | ALTER TABLE p_ci_pipelines ADD CONSTRAINT fk_262d4c2d19_p FOREIGN KEY (auto_canceled_by_partition_id, auto_canceled_by_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE SET NULL;
(1 row)
gitxp1t=# ALTER TABLE p_ci_pipelines DROP CONSTRAINT fk_262d4c2d19_p;
ALTER TABLE
gitxp1t=# ALTER TABLE p_ci_pipelines ADD CONSTRAINT fk_262d4c2d19_p FOREIGN KEY (auto_canceled_by_partition_id, auto_canceled_by_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE
gitxp1t=# SELECT conrelid::pg_catalog.regclass AS "constrained table",
gitxp1t-# conname AS constraint,
gitxp1t-# confrelid::pg_catalog.regclass AS "references",
gitxp1t-# pg_catalog.format('ALTER TABLE %s DROP CONSTRAINT %I;',
gitxp1t(# conrelid::pg_catalog.regclass, conname) AS "drop",
gitxp1t-# pg_catalog.format('ALTER TABLE %s ADD CONSTRAINT %I %s;',
gitxp1t(# conrelid::pg_catalog.regclass, conname,
gitxp1t(# pg_catalog.pg_get_constraintdef(oid)) AS "add"
gitxp1t-# FROM pg_catalog.pg_constraint c
gitxp1t-# WHERE contype = 'f' AND conparentid = 0 AND
gitxp1t-# (SELECT count(*) FROM pg_catalog.pg_constraint c2
gitxp1t(# WHERE c2.conparentid = c.oid) <>
gitxp1t-# (SELECT count(*) FROM pg_catalog.pg_inherits i
gitxp1t(# WHERE (i.inhparent = c.conrelid OR i.inhparent = c.confrelid) AND
gitxp1t(# EXISTS (SELECT 1 FROM pg_catalog.pg_partitioned_table
gitxp1t(# WHERE partrelid = i.inhparent));
constrained table | constraint | references | drop | add -------------------+-----------------+----------------+-------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
p_ci_pipelines | fk_262d4c2d19_p | p_ci_pipelines | ALTER TABLE p_ci_pipelines DROP CONSTRAINT fk_262d4c2d19_p; | ALTER TABLE p_ci_pipelines ADD CONSTRAINT fk_262d4c2d19_p FOREIGN KEY (auto_canceled_by_partition_id, auto_canceled_by_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE SET NULL;
(1 row)

gitxp1t=# \d+ p_ci_pipelines
Partitioned table "public.p_ci_pipelines"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------------------------+-----------------------------+-----------+----------+------------------------------------------+----------+-------------+--------------+-------------
ref | character varying | | | | extended | | |
sha | character varying | | | | extended | | |
before_sha | character varying | | | | extended | | |
created_at | timestamp without time zone | | | | plain | | |
updated_at | timestamp without time zone | | | | plain | | |
tag | boolean | | | false | plain | | |
yaml_errors | text | | | | extended | | |
committed_at | timestamp without time zone | | | | plain | | |
project_id | integer | | | | plain | | |
status | character varying | | | | extended | | |
started_at | timestamp without time zone | | | | plain | | |
finished_at | timestamp without time zone | | | | plain | | |
duration | integer | | | | plain | | |
user_id | integer | | | | plain | | |
lock_version | integer | | | 0 | plain | | |
pipeline_schedule_id | integer | | | | plain | | |
source | integer | | | | plain | | |
config_source | integer | | | | plain | | |
protected | boolean | | | | plain | | |
failure_reason | integer | | | | plain | | |
iid | integer | | | | plain | | |
merge_request_id | integer | | | | plain | | |
source_sha | bytea | | | | extended | | |
target_sha | bytea | | | | extended | | |
external_pull_request_id | bigint | | | | plain | | |
ci_ref_id | bigint | | | | plain | | |
locked | smallint | | not null | 1 | plain | | |
partition_id | bigint | | not null | | plain | | |
id | bigint | | not null | nextval('ci_pipelines_id_seq'::regclass) | plain | | |
auto_canceled_by_id | bigint | | | | plain | | |
auto_canceled_by_partition_id | bigint | | | | plain | | |
Partition key: LIST (partition_id)
Indexes:
"p_ci_pipelines_pkey" PRIMARY KEY, btree (id, partition_id)
"p_ci_pipelines_auto_canceled_by_id_idx" btree (auto_canceled_by_id)
"p_ci_pipelines_ci_ref_id_id_idx" btree (ci_ref_id, id) WHERE locked = 1
"p_ci_pipelines_ci_ref_id_id_source_status_idx" btree (ci_ref_id, id DESC, source, status) WHERE ci_ref_id IS NOT NULL
"p_ci_pipelines_external_pull_request_id_idx" btree (external_pull_request_id) WHERE external_pull_request_id IS NOT NULL
"p_ci_pipelines_id_idx" btree (id) WHERE source = 13
"p_ci_pipelines_merge_request_id_idx" btree (merge_request_id) WHERE merge_request_id IS NOT NULL
"p_ci_pipelines_pipeline_schedule_id_id_idx" btree (pipeline_schedule_id, id)
"p_ci_pipelines_project_id_id_idx" btree (project_id, id DESC)
"p_ci_pipelines_project_id_iid_partition_id_idx" UNIQUE, btree (project_id, iid, partition_id) WHERE iid IS NOT NULL
"p_ci_pipelines_project_id_ref_id_idx" btree (project_id, ref, id DESC)
"p_ci_pipelines_project_id_ref_status_id_idx" btree (project_id, ref, status, id)
"p_ci_pipelines_project_id_sha_idx" btree (project_id, sha)
"p_ci_pipelines_project_id_source_idx" btree (project_id, source)
"p_ci_pipelines_project_id_status_config_source_idx" btree (project_id, status, config_source)
"p_ci_pipelines_project_id_status_created_at_idx" btree (project_id, status, created_at)
"p_ci_pipelines_project_id_status_updated_at_idx" btree (project_id, status, updated_at)
"p_ci_pipelines_project_id_user_id_status_ref_idx" btree (project_id, user_id, status, ref) WHERE source <> 12
"p_ci_pipelines_status_id_idx" btree (status, id)
"p_ci_pipelines_user_id_created_at_config_source_idx" btree (user_id, created_at, config_source)
"p_ci_pipelines_user_id_created_at_source_idx" btree (user_id, created_at, source)
"p_ci_pipelines_user_id_id_idx" btree (user_id, id) WHERE status::text = ANY (ARRAY['running'::character varying::text, 'waiting_for_resource'::character varying::text, 'preparing'::character varying::text, 'pending'::character varying::text, 'created'::character varying::text, 'scheduled'::character varying::text])
"p_ci_pipelines_user_id_id_idx1" btree (user_id, id DESC) WHERE failure_reason = 3
Check constraints:
"check_2ba2a044b9" CHECK (project_id IS NOT NULL)
Foreign-key constraints:
"fk_190998ef09" FOREIGN KEY (external_pull_request_id) REFERENCES external_pull_requests(id) ON DELETE SET NULL
"fk_262d4c2d19_p" FOREIGN KEY (auto_canceled_by_partition_id, auto_canceled_by_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE SET NULL
"fk_3d34ab2e06" FOREIGN KEY (pipeline_schedule_id) REFERENCES ci_pipeline_schedules(id) ON DELETE SET NULL
"fk_d80e161c54" FOREIGN KEY (ci_ref_id) REFERENCES ci_refs(id) ON DELETE SET NULL
Referenced by:
TABLE "p_ci_pipelines" CONSTRAINT "fk_262d4c2d19_p" FOREIGN KEY (auto_canceled_by_partition_id, auto_canceled_by_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE SET NULL
TABLE "ci_pipeline_chat_data" CONSTRAINT "fk_64ebfab6b3_p" FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "p_ci_builds" CONSTRAINT "fk_87f4cefcda_p" FOREIGN KEY (upstream_pipeline_partition_id, upstream_pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "p_ci_builds" CONSTRAINT "fk_a2141b1522_p" FOREIGN KEY (auto_canceled_by_partition_id, auto_canceled_by_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE SET NULL
TABLE "p_ci_builds" CONSTRAINT "fk_d3130c9a7f_p" FOREIGN KEY (partition_id, commit_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "ci_sources_pipelines" CONSTRAINT "fk_d4e29af7d7_p" FOREIGN KEY (source_partition_id, source_pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "ci_sources_pipelines" CONSTRAINT "fk_e1bad85861_p" FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "p_ci_pipeline_variables" CONSTRAINT "fk_f29c5f4380_p" FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "p_ci_stages" CONSTRAINT "fk_fb57e6cc56_p" FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "ci_sources_projects" CONSTRAINT "fk_rails_10a1eb379a_p" FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "gitlab_partitions_dynamic.ci_builds_101" CONSTRAINT "fk_rails_4540ead625_p" FOREIGN KEY (upstream_pipeline_partition_id, upstream_pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID
TABLE "gitlab_partitions_dynamic.ci_builds_102" CONSTRAINT "fk_rails_4540ead625_p" FOREIGN KEY (upstream_pipeline_partition_id, upstream_pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID
TABLE "ci_builds" CONSTRAINT "fk_rails_4540ead625_p" FOREIGN KEY (upstream_pipeline_partition_id, upstream_pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID
TABLE "gitlab_partitions_dynamic.ci_builds_101" CONSTRAINT "fk_rails_494e57ee78_p" FOREIGN KEY (auto_canceled_by_partition_id, auto_canceled_by_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE SET NULL NOT VALID
TABLE "ci_builds" CONSTRAINT "fk_rails_494e57ee78_p" FOREIGN KEY (auto_canceled_by_partition_id, auto_canceled_by_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE SET NULL NOT VALID
TABLE "gitlab_partitions_dynamic.ci_builds_102" CONSTRAINT "fk_rails_494e57ee78_p" FOREIGN KEY (auto_canceled_by_partition_id, auto_canceled_by_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE SET NULL NOT VALID
TABLE "ci_pipeline_variables" CONSTRAINT "fk_rails_507416c33a_p" FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID
TABLE "gitlab_partitions_dynamic.ci_pipeline_variables_102" CONSTRAINT "fk_rails_507416c33a_p" FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID
TABLE "ci_pipeline_metadata" CONSTRAINT "fk_rails_50c1e9ea10_p" FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "gitlab_partitions_dynamic.ci_stages_102" CONSTRAINT "fk_rails_5d4d96d44b_p" FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID
TABLE "ci_stages" CONSTRAINT "fk_rails_5d4d96d44b_p" FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID
TABLE "ci_pipeline_messages" CONSTRAINT "fk_rails_8d3b04e3e1_p" FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "p_ci_pipelines_config" CONSTRAINT "fk_rails_906c9a2533_p" FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "ci_pipeline_artifacts" CONSTRAINT "fk_rails_a9e811a466_p" FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "p_ci_builds_execution_configs" CONSTRAINT "fk_rails_c26408d02c_p" FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "ci_builds" CONSTRAINT "fk_rails_d739f46384_p" FOREIGN KEY (partition_id, commit_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID
TABLE "gitlab_partitions_dynamic.ci_builds_101" CONSTRAINT "fk_rails_d739f46384_p" FOREIGN KEY (partition_id, commit_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID
TABLE "gitlab_partitions_dynamic.ci_builds_102" CONSTRAINT "fk_rails_d739f46384_p" FOREIGN KEY (partition_id, commit_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID
TABLE "gitlab_partitions_dynamic.ci_builds_execution_configs_102" CONSTRAINT "fk_rails_e214655a86_p" FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID
TABLE "gitlab_partitions_dynamic.ci_builds_execution_configs_100" CONSTRAINT "fk_rails_e214655a86_p" FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID
TABLE "gitlab_partitions_dynamic.ci_builds_execution_configs_101" CONSTRAINT "fk_rails_e214655a86_p" FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID
TABLE "ci_daily_build_group_report_results" CONSTRAINT "fk_rails_ee072d13b3_p" FOREIGN KEY (partition_id, last_pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
Triggers:
p_ci_pipelines_loose_fk_trigger AFTER DELETE ON p_ci_pipelines REFERENCING OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION insert_into_loose_foreign_keys_deleted_records()
Partitions: ci_pipelines FOR VALUES IN ('100', '101', '102')