On Tue, Aug 23, 2022 at 9:47 AM Alvaro Herrera <alvhe...@alvh.no-ip.org> wrote:
> 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) > A bigger question I have, even with the additional filtering, is what if there are multiple constraints ? How do we decide which unique / primary key constraint to return ? Looks like there is no known SQL statements leading to such state, but should we consider such possibility ? Cheers