So why not just drop the constraint in the partition? Seems rather useless. If there's a valid reason to have a constraint that's different from the one in the parent table, then you can rename it.I guess we could add a check to "pg_upgrade --check" to report this kind of thing ... I'm not really convinced of this though. Anyhow, I don't feel inclined to try to have pg_upgrade/pg_dump handle this case cleanly.
Thanks for the reply, and sorry for the long delay in responding. You're right that the constraint on the partition can simply be dropped or renamed. However, since the failure occurs during pg_upgrade and the error message is not very obvious, I think it would be helpful to detect this situation with "pg_upgrade --check" beforehand, as you suggested. Attached is a patch that implements this check. It detects NOT VALID foreign keys on partitions that share a name with a foreign key on the parent partitioned table and were not linked to it (conparentid = 0). Such constraints may cause a name conflict during restore: pg_dump emits them as separate ALTER TABLE ADD CONSTRAINT statements for both the parent and the partition, and when the parent's constraint is restored first, it is inherited by the partition, so the subsequent attempt to create the same-named constraint on the partition errors out with "constraint ... for relation ... already exists". Whether the failure actually occurs depends on the order in which pg_dump emits the constraints, which is determined by alphabetical sorting of table names; the check flags both cases. Only NOT VALID foreign keys are affected. VALID foreign keys on partitions are matched and linked (conparentid is set) by tryAttachPartitionForeignKey() during ATTACH PARTITION, so pg_dump does not emit them separately. This matching logic excluded NOT VALID constraints until PG 18, where it was fixed by commit b663b9436e7. The check therefore applies to old clusters from PG 11 (when conparentid was introduced) through PG 17. Other constraint types are not affected: CHECK constraints can have duplicate names on partitions, but pg_dump emits them inline in CREATE TABLE; PRIMARY KEY, UNIQUE, and EXCLUDE constraints create indexes, so duplicate names are prevented by an index name conflict; NOT NULL constraints as named entries in pg_constraint exist only since PG 18. The patch is for the master branch. I did not add a separate test because I did not find dedicated tests for other similar check functions, but I am ready to add one if needed. Best regards, Arseny Kositsyn.
From c4b873b3eba8583827148cc07c89ad035eb60300 Mon Sep 17 00:00:00 2001 From: Arseny Kositsyn <[email protected]> Date: Wed, 22 Jul 2026 13:32:43 +0300 Subject: [PATCH] pg_upgrade: check for duplicate FK names on partitions When a partition contains a NOT VALID foreign key with the same name as a foreign key on its parent partitioned table, and that foreign key was created independently (conparentid = 0), restore during pg_upgrade may fail with "constraint ... for relation ... already exists". The failure occurs because pg_dump emits such foreign keys as separate ALTER TABLE ADD CONSTRAINT statements for both the parent and the partition. When the parent's constraint is restored first, it is inherited by the partition, and the subsequent attempt to create the same-named constraint on the partition errors out. Whether the failure actually occurs depends on the order in which pg_dump emits the constraints, which is determined by alphabetical sorting of table names; this check flags both cases. Only NOT VALID foreign keys are affected. VALID foreign keys on partitions are matched and linked (conparentid is set) by tryAttachPartitionForeignKey() during ATTACH PARTITION, so pg_dump does not emit them separately. This matching logic excluded NOT VALID constraints (via a convalidated check) until PG 18, where it was fixed by commit b663b9436e7 ("Allow NOT VALID foreign key constraints on partitioned tables"). The check therefore only applies to old clusters from PG 11 (when conparentid was introduced) through PG 17. Only foreign keys are affected by this problem. CHECK constraints can have duplicate names on partitions, but pg_dump emits them inline in CREATE TABLE, so no conflict arises. PRIMARY KEY, UNIQUE, and EXCLUDE constraints create indexes, so duplicate names on partitions are prevented by an index name conflict. NOT NULL constraints as named entries in pg_constraint exist only since PG 18, so they are not relevant here. --- src/bin/pg_upgrade/check.c | 106 +++++++++++++++++++++++++++++++++++++ 1 file changed, 106 insertions(+) diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c index 2155b01b11f..0858e60a692 100644 --- a/src/bin/pg_upgrade/check.c +++ b/src/bin/pg_upgrade/check.c @@ -27,6 +27,7 @@ static void check_for_user_defined_postfix_ops(ClusterInfo *cluster); static void check_for_incompatible_polymorphics(ClusterInfo *cluster); static void check_for_tables_with_oids(ClusterInfo *cluster); static void check_for_not_null_inheritance(ClusterInfo *cluster); +static void check_for_duplicate_fk_names(ClusterInfo *cluster); static void check_for_gist_inet_ops(ClusterInfo *cluster); static void check_for_new_tablespace_dir(void); static void check_for_user_defined_encoding_conversions(ClusterInfo *cluster); @@ -635,6 +636,16 @@ check_and_dump_old_cluster(void) if (GET_MAJOR_VERSION(old_cluster.major_version) <= 1800) check_for_not_null_inheritance(&old_cluster); + /* + * Pre-PG 18 left NOT VALID foreign keys on partitions unlinked to the + * parent (conparentid = 0). If such a constraint shares its name with + * a foreign key on the parent, schema restore may fail due to a name + * conflict. The check starts from PG 11, when conparentid was introduced. + */ + if (GET_MAJOR_VERSION(old_cluster.major_version) >= 1100 && + GET_MAJOR_VERSION(old_cluster.major_version) <= 1700) + check_for_duplicate_fk_names(&old_cluster); + /* * The btree_gist extension contains gist_inet_ops and gist_cidr_ops * opclasses that do not reliably give correct answers. We want to @@ -1700,6 +1711,101 @@ check_for_not_null_inheritance(ClusterInfo *cluster) check_ok(); } +/* + * Callback function for processing results of query for + * check_for_duplicate_fk_names. + */ +static void +process_duplicate_fk_names(DbInfo *dbinfo, PGresult *res, void *arg) +{ + UpgradeTaskReport *report = (UpgradeTaskReport *) arg; + int ntups = PQntuples(res); + int i_nspname = PQfnumber(res, "nspname"); + int i_relname = PQfnumber(res, "relname"); + int i_conname = PQfnumber(res, "conname"); + + if (ntups == 0) + return; + + if (report->file == NULL && + (report->file = fopen_priv(report->path, "w")) == NULL) + pg_fatal("could not open file \"%s\": %m", report->path); + + fprintf(report->file, "In database: %s\n", dbinfo->db_name); + + for (int rowno = 0; rowno < ntups; rowno++) + { + fprintf(report->file, " %s.%s.%s\n", + PQgetvalue(res, rowno, i_nspname), + PQgetvalue(res, rowno, i_relname), + PQgetvalue(res, rowno, i_conname)); + } +} + +/* + * check_for_duplicate_fk_names() + * + * Check for NOT VALID foreign keys on partitions that have the same name as + * a foreign key on the parent partitioned table and were not linked to it + * (conparentid = 0). Such constraints may cause a name conflict during + * restore, because pg_dump emits them as separate ALTER TABLE ADD CONSTRAINT + * statements for both the parent and the partition. + */ +static void +check_for_duplicate_fk_names(ClusterInfo *cluster) +{ + UpgradeTaskReport report; + UpgradeTask *task; + const char *query; + + prep_status("Checking for foreign keys on partitions with duplicate names"); + + report.file = NULL; + snprintf(report.path, sizeof(report.path), "%s/%s", + log_opts.basedir, + "duplicate_fk_names.txt"); + + query = "SELECT n.nspname, c.relname, con.conname " + "FROM pg_constraint con " + "JOIN pg_class c ON con.conrelid = c.oid " + "JOIN pg_namespace n ON c.relnamespace = n.oid " + "JOIN pg_inherits i ON i.inhrelid = c.oid " + "JOIN pg_class p ON i.inhparent = p.oid " + "JOIN pg_constraint pcon ON pcon.conrelid = p.oid " + "WHERE con.conparentid = 0 " + " AND pcon.conname = con.conname " + " AND pcon.contype = con.contype " + " AND con.contype = 'f' " + " AND NOT con.convalidated " + " AND c.relispartition"; + + task = upgrade_task_create(); + upgrade_task_add_step(task, query, + process_duplicate_fk_names, + true, &report); + upgrade_task_run(task, cluster); + upgrade_task_free(task); + + if (report.file) + { + fclose(report.file); + pg_log(PG_REPORT, "fatal"); + pg_fatal("Your installation contains NOT VALID foreign keys on partitions that have\n" + "the same name as a foreign key on the parent partitioned table.\n" + "The upgrade may fail because the parent foreign key is inherited by\n" + "the partition during restore, causing a name conflict.\n" + "You can fix this by dropping or renaming the foreign key on the\n" + "partition, using:\n" + " ALTER TABLE ONLY partition_name DROP CONSTRAINT constraint_name;\n" + "or\n" + " ALTER TABLE ONLY partition_name RENAME CONSTRAINT constraint_name TO new_name;\n" + "A list of the problem constraints is in the file:\n" + " %s", report.path); + } + else + check_ok(); +} + /* * Callback function for processing results of query for * check_for_gist_inet_ops()'s UpgradeTask. If the query returned any rows -- 2.43.0
