Re: Proposed feature: Selective Foreign Keys

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')
FORKEY 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=1width=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?

> Whether that works or not, it seems to me that a good deal of thought
> will need to be given to what dependencies get created when creating a
> constraint of this type.

Hmm, yeah I hadn’t thought about that. OTOH, it seems that at least some of the expected functionality works anyway:

tom=# alter table comment drop column parent_entity ;
ERROR:  cannot drop table comment column parent_entity because other objects depend on it
DETAIL:  constraint comment_blog_fk on table comment depends on table comment column parent_entity
constraint comment_event_fk on table comment depends on table comment column parent_entity

I guess those bits that I copied from the check constraint code must have included creating the appropriate pg_depend
entries.:) 

I’ll add some more checks to the regression tests.

Did you have other scenarios in mind?

Thanks

Tom