Proposed feature: Selective Foreign Keys

Hi all!

The Problem
-----------------
One case that traditional SQL doesn't handle very well is when you have a child entity which can be attached to a number of different parent entities. Examples might be comments, tags or file attachments - we might have 20 different entities in the system that we would like our users to be able add comments to, but the existing solutions for mapping this all have downsides.

Existing solution 1: Join tables ahoy
If I can have a list of comments on every other object in the system, and I want to have referrential integrity, then the obvious thing to do is create a join table between each entity and the comments table.
Pros:
- Straight forward, traditional object-with-collection-of-child SQL structure
Cons:
- If a parent object gets deleted here, we can't use foreign keys to delete e.g. a child comment, so we'll have to either explicitly do it as part of our delete logic or have a cleanup process to catch orphans. Or do a dance with delete triggers on the join tables deleting the comment.
- For n entities requiring comments in the system, we need n join tables.If we want both comments and e.g. tags and likes on all of our entities, we now have 3n join tables for what should be some relatively self-contained on-the-side data - this is could be more tables than the entire rest of the system
- It's difficult to create any kind of self-contained component for building applications in this scenario, as it will need to know about every other entity in the system, or be able to poke around inside whatever ORM or data access system that you have to work out what join tables it needs when running queries.

Existing solution 2: Enter the matrix
Instead of having n join tables, let's just mash them all together, with a column per parent object, and a check constraint to force exactly one of those columns to be set.
Pros:
- Less bloat in the number of tables
Cons:
- Doesn't solve orphan problem
- Addition of a new entity which needs comments and we now have to add another column onto it, potentially rewriting the whole thing
- Ugly

Existing solution 3: Embed the matrix
Kinda like the dependency matrix table, except that all the columns referencing potential parent objects we put into the comment table instead.
Pros:
- Everything contained in column table
- No orphans, since cascaded deletes will now delete the actual comment
Cons:
- Comment table now has references to every single type that it may be attached to
- Addition of a new entity and we probably have to rewrite the comment table now

Existing solution 4: Abandon ye all referential integrity
Have a column indicating parent type and another one for the id. In the case of comments this would be directly on the comment table itself. In the case of something like tags that we might expect to be shared between entities, it would be in a single join table.
Pros:
- Pretty self-contained
- Data model which has neither lots of empty columns or lots of tables
- Can make new entities "commentable" without rewriting anything
- Because it's self-contained, can build application components that don't need to know much about the rest of your system. For example this is the approach that the grails taggable and commentable plugins take.
Cons:
- No referential integrity, since we can't have a single column pointing to different tables with existing foreign key infrastructure
- Since there's no real db support for doing things this way, existing ORMs etc don't really know how use a single column to join against multiple different tables based on a discriminator or 'switch' column.

Existing solution 5: Everything's a thing
Make your entity hierarchy have a base level object which can have comments attached, and then everything that you need to be "commentable" has to extend that. You can do that in an ORM, or with table inheritance in the database.
Pros:
- Single top-level thing to hang your data on
Cons:
- You've polluted your object hierarchy just to hang some stuff off of the end of it rather than it being driven by behaviours
- You're going to be paying a performance penalty - everything that extends that base level object will now join against it incessantly, and you now have a global id sequence or whatever that you may not want.

Basically none of the above handle the situation very well. The cleanest is solution 4, but lack of RI sucks.

Feature Proposal: Selective foreign keys.
-------------------------------------------------
Allow foreign keys to have where clauses. The above comment example using solution 4 might then look like then following:

CREATE TABLE comment as (
id bigserial primary key,
content text not null,
parent_entity regclass not null,
parent_id int8
);
ALTER TABLE comment ADD CONSTRAINT comment_blog_fk FOREIGN KEY (parent_id) REFERENCES blog(id) WHERE (parent_entity = ‘blog');
ALTER TABLE comment ADD CONSTRAINT comment_event_fk FOREIGN KEY (parent_id) REFERENCES event(id) WHERE (parent_entity = ‘event');

At this point, the following things should work:
insert into blog(id, title, content) values (10, 'i hate mondays', 'so sad');
insert into event(id, title, location, date) values (20, 'my birthday party', 'local pub', now());
insert into comment(content, parent_entity, parent_id) values ('me too', 'blog', 10);
insert into comment(content, parent_entity, parent_id) values ('I love that pub', 'event', 20);

But the following would barf:
insert into comment(content, parent_entity, parent_id) values ('bad comment', 'blog', 20);
ERROR: insert or update on table "comment" violates foreign key constraint "comment_blog_fk"
Detail: Key (parent_id)=(20) is not present in table "blog".:

Patch
-----
I have attached a proof-of-concept patch for this feature.
What it currently does:
- Allows where clause on FK declaration
- Validates and stores the expression into pg_constraint a la check constraints, using the same validity checks
- Implements logic on insertion to FK table, and update / delete on PK tables as expected.
- Regression tests handling all of the above

Things I know are still to be done, if there is consensus that this feature is worth having:
- Handle update of columns affecting the selection expression. Currently the RI trigger doesn’t fire if the FK column isn’t modified. I’m not sure where the logic for this is implemented.
- pg_dump support
- Update some comments in a few places referring to conbin/consrc columns of pg_constraint as relevant to check constraints only
- Documentation
- There are probably a few scenarios that I’m not testing in the regression tests that I should be.

Implementation Comments
----------------------------
I borrowed the exclusion constraint where clause in the grammar, which results in the where expression needing parentheses. With a bit more work this could possibly be removed, but I’m not too upset about it tbh.

Currently I’m creating a new executor for each call of RI_FKey_check when there is a where clause, and doing other bits of work in there. It’s not 100% clear to me how expensive doing that stuff in there is, and whether it’s worth it or possible to cache some of it.

Stuff added in tablecmds.c was copied from similar stuff elsewhere, but honestly without a huge amount of understanding. Don’t know if I could do without some of it.

Comments?

Cheers

Tom

PS Thanks to Noah Misch who answered a question on the list a while ago which got me over a hump, and to the authors of the wiki pages around submitting a patch.