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