Re: Will PG use composite index to enforce foreign keys?

"John Burger" <john@mitre.org> writes:

> Scott Marlowe wrote:
>
>>> As a secondary question, is there any way I could have answered this
>>> myself, using analyze, the system catalogs, etc?  ANALYZE DELETE
>>> doesn't seem to show the FK checking that must go on behind the  scenes.
>>
>> You could have coded up an example to see if it worked I guess.
>> Here's a short example:
>>
>> create table a (i int, j int, info text, primary key (i,j));
>> create table b (o int, p int, moreinfo text, foreign key (o,p)  references
>> a);
>> insert into a values (1,2,'abc');
>> insert into b values (1,2,'def');
>> INSERT 0 1
>> insert into b values (1,3,'def');
>> ERROR:  insert or update on table "b" violates foreign key  constraint
>> "b_o_fkey"
>> DETAIL:  Key (o,p)=(1,3) is not present in table "a".
>> delete from a;
>> ERROR:  update or delete on table "a" violates foreign key constraint
>> "b_o_fkey" on table "b"
>> DETAIL:  Key (i,j)=(1,2) is still referenced from table "b".
>
> But this doesn't really match my question - I wanted to know whether  checking
> an FK on =one= column would use a composite key on =several=  columns.
> Modifying your example:
>
>> create table a (i int PRIMARY KEY, j int, info text);
>> create table b (o int REFERENCES A, p int, moreinfo text, PRIMARY  KEY
>> (O,P));
>> insert into a values (1,2,'abc');
>> insert into b values (1,2,'def');
>>
>> delete from a where i = 1;
>
>
> Here, the FK is a simple one, and the referential integrity machinery  simply
> needs to check whether there is a row in table B with O=1.  My  question is
> whether it will use the composite PK index.
>
> I guess a generalization of my question is whether the FK-checking  machinery
> simply does a SELECT against the referencing column.

It does

> That  is, in this
> example, if the following effectively happens:
>
>   SELECT * FROM B WHERE O = 1;

Actually the query is (effectively, assuming your equality operators are named
"=" and the columns match in type)

SELECT 1
  FROM ONLY B x
 WHERE col1=?
   AND col2=?
   ...
   FOR SHARE OF x

Since it has to take a lock on the record found to ensure it doesn't disappear
before your transaction finishes.


--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's PostGIS support!