Re: [BUG] parenting a PK constraint to a self-FK one (Was: Self FK oddity when attaching a partition)

On 2022-Aug-23, Zhihong Yu wrote:

> I was thinking of the following patch.
> Basically, if there is only one matching constraint. we still return it.
>
> diff --git a/src/postgres/src/backend/catalog/pg_constraint.c
> b/src/postgres/src/backend/catalog/pg_constraint.c
> index f0726e9aa0..ddade138b4 100644
> --- a/src/postgres/src/backend/catalog/pg_constraint.c
> +++ b/src/postgres/src/backend/catalog/pg_constraint.c
> @@ -1003,7 +1003,8 @@ get_relation_idx_constraint_oid(Oid relationId, Oid
> indexId)
> constrForm = (Form_pg_constraint) GETSTRUCT(tuple);
> if (constrForm->conindid == indexId)
> {
> - constraintId = HeapTupleGetOid(tuple);
> + if (constraintId == InvalidOid || constrForm->confrelid == 0)
> + constraintId = HeapTupleGetOid(tuple);
> break;
> }
> }

We could do this, but what do we gain by doing so? It seems to me that
my proposed formulation achieves the same and is less fuzzy about what
the returned constraint is. Please try to write a code comment that
explains what this does and see if it makes sense.

For my proposal, it would be "return the OID of a primary key or unique
constraint associated with the given index in the given relation, or OID
if no such index is catalogued". This definition is clearly useful for
partitioned tables, on which the unique and primary key constraints are
useful elements. There's nothing that cares about foreign keys.

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"La virtud es el justo medio entre dos defectos" (Aristóteles)