Re: Proposed feature: Selective Foreign Keys

On Mon, Dec 2, 2013 at 6:08 PM, Tom Dunstan <pgsql@tomd.cc> wrote:
> On 3 Dec 2013, at 03:37, Robert Haas <robertmhaas@gmail.com> wrote:
>> I also like this feature.   It would be really neat if a FOREIGN KEY
>> constraint with a WHERE clause could use a *partial* index on the
>> foreign table provided that the index would be guaranteed to be predOK
>> for all versions of the foreign key checking query.  That might be
>> hard to implement, though.
>
> Well, with this patch, under the hood the FK query is doing (in the case of RESTRICT):
>
> SELECT 1 FROM ONLY "public"."comment" x WHERE (the id) OPERATOR(pg_catalog.=) "parent_id" AND (parent_entity =
'event')FOR KEY SHARE OF x;
 
>
> If we stick a partial index on the column, disable seq scans and run the query, we get:
>
> tom=# create index comment_event_id on comment (parent_id) where parent_entity = 'event';
> CREATE INDEX
> tom=# set enable_seqscan = off;
> SET
> tom=# explain SELECT 1 FROM ONLY "public"."comment" x WHERE 20 OPERATOR(pg_catalog.=) "parent_id" AND (parent_entity
='event') FOR KEY SHARE OF x;
 
>                                        QUERY PLAN
> ----------------------------------------------------------------------------------------
>  LockRows  (cost=0.12..8.15 rows=1 width=6)
>    ->  Index Scan using comment_event_id on comment x  (cost=0.12..8.14 rows=1 width=6)
>          Index Cond: (20 = parent_id)
>          Filter: (parent_entity = 'event'::commentable_entity)
> (4 rows)
>
> Is that what you had in mind?

Yeah, more or less, but the key is ensuring that it wouldn't let you
create the constraint in the first place if the partial index
specified *didn't* match the WHERE clause.  For example, suppose the
partial index says WHERE parent_entity = 'event' but the constraint
definition is WHERE parent_event = 'somethingelse'.  That ought to
fail, just as creating a regular foreign constraint will fail if
there's no matching unique index.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company