Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi Tom,

> On 26 Nov 2024, at 22:25, Tom Lane <tgl@xxxxxxxxxxxxx> 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')







[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux