Re: pg_upgrade fails when FK constraint with same name exists on partitioned table and its partition

> 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.