On 3 Dec 2013, at 03:37, Robert Haas <[email protected]> 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
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
