Re: Proposed feature: Selective Foreign Keys

On 3 Dec 2013, at 03:37, Robert Haas <robertmhaas(at)gmail(dot)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?

> 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