Meaning of pg_constraint.conindid for foreign keys

From: Roy Brokvam <roy(dot)brokvam(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Meaning of pg_constraint.conindid for foreign keys
Date: 2017-04-27 10:29:50
Message-ID: CADhin7Xj7FFp3N+d_J=4ya8iqo+hnoyX23vYEA35guMDDuGzbg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Trying to write a query to find unindexed foreign keys referencing a table,
I tried to do this:

SELECT *
FROM pg_constraint
WHERE contype = 'f'
AND confrelid = <the oid of the referenced table>
AND conindid = 0

To my surprise, the query did not return any rows, even though I knew there
existed indexless foreign keys referencing my table. After investigating
further, saw that conindid contained the oid of the referenced table's
primary key, not the oid of the index "implementing" the foreign key.

I'm running PostgreSQL 9.5 running on Ubuntu linux 3.19.0-49-generic.

Before posting this as a bug, I wanted to check here whether my
understanding of conind for foreign keys is correct. The documentation for
conindid (
https://www.postgresql.org/docs/9.5/static/catalog-pg-constraint.html) says

conindid oid pg_class
<https://www.postgresql.org/docs/9.5/static/catalog-pg-class.html>.oid The
index supporting this constraint, if it's a unique, primary key, foreign
key, or exclusion constraint; else 0
Regards,
Roy Brokvam

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Andrew Gierth 2017-04-27 10:57:45 Re: Meaning of pg_constraint.conindid for foreign keys
Previous Message Andrew Gierth 2017-04-27 10:00:26 Re: BUG #14632: Plus and minus operators inconsistency with leap years and year intervals.