Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION - Mailing list pgsql-general
| From | Paul Foerster |
|---|---|
| Subject | Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION |
| Date | |
| Msg-id | F103529F-49ED-4A2F-9EB1-19AFC561635A@gmail.com Whole thread Raw |
| In response to | Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION
|
| List | pgsql-general |
Hi Tom,
> On 26 Nov 2024, at 22:25, Tom Lane <tgl@sss.pgh.pa.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
aself 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; |
ALTERTABLE 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; |
ALTERTABLE 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
NOTNULL
"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'::charactervarying::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)
REFERENCESp_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)
REFERENCESp_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)
REFERENCESp_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
DELETECASCADE 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
DELETECASCADE 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
DELETESET 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
DELETESET 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)
REFERENCESp_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
STATEMENTEXECUTE FUNCTION insert_into_loose_foreign_keys_deleted_records()
Partitions: ci_pipelines FOR VALUES IN ('100', '101', '102')
pgsql-general by date: