Обсуждение: [BUGS] Meaning of pg_constraint.conindid for foreign keys
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.oid | The index supporting this constraint, if it's a unique, primary key, foreign key, or exclusion constraint; else 0 |
Regards,
Roy Brokvam
>>>>> "Roy" == Roy Brokvam <roy.brokvam@gmail.com> writes: Roy> After investigating further, saw that conindid contained the oidRoy> of the referenced table's primary key, not theoid of the indexRoy> "implementing" the foreign key. FKs aren't implemented using an index on the referencing table, only on the referenced table. i.e. given: CREATE TABLE foo (id integer PRIMARY KEY); CREATE TABLE bar (foo_id integer REFERENCES foo); the constraint depends on the existence of the index foo_pkey (and it's this one which is recorded in the system catalogs), but there is no index at all on bar; even if you created an index on bar(foo_id), it would not be associated in any way with the FK constraint (though it would improve the performance of updates/deletes in foo, because the query issued by the constraint check would likely use it). So you're looking in the wrong place. -- Andrew (irc:RhodiumToad) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Roy Brokvam <roy.brokvam@gmail.com> writes:
> 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.
Not what I see here:
regression=# create table foo1(f1 int primary key);
CREATE TABLE
regression=# create table foo2(f1 int);
CREATE TABLE
regression=# create unique index on foo2(f1);
CREATE INDEX
regression=# create table bar1 (f1 int references foo1(f1));
CREATE TABLE
regression=# create table bar2 (f1 int references foo2(f1));
CREATE TABLE
regression=# select oid,conname,contype,conrelid::regclass,conindid,conindid::regclass,confrelid::regclass from
pg_constraintorder by oid desc limit 3; oid | conname | contype | conrelid | conindid | conindid | confrelid
--------+--------------+---------+----------+----------+-------------+-----------232357 | bar2_f1_fkey | f | bar2
| 232353 | foo2_f1_idx | foo2232345 | bar1_f1_fkey | f | bar1 | 232337 | foo1_pkey | foo1232338 |
foo1_pkey | p | foo1 | 232337 | foo1_pkey | -
(3 rows)
foo1's pkey constraint has OID 232338, its underlying index has
OID 232337, and the latter is what's in conindid for bar1's
foreign key constraint. It has to be that way because of the
case illustrated by foo2/bar2, where an FK depends on an index
that doesn't have a formal constraint associated with it.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs