Hi Tom,
> On 26 Nov 2024, at 22:25, Tom Lane <[email protected]> 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')
