Re: Proposed feature: Selective Foreign Keys

On 12/02/2013 05:06 AM, Andres Freund wrote:
> On 2013-12-02 08:57:01 +0000, Albe Laurenz wrote:
>> What strikes me is that since foreign key constraints are implemented
>> as triggers in PostgreSQL, this solution would probably not have many
>> performance benefits over a self-written trigger that implements the
>> same functionality.  Since you need two triggers for your example,
>> the performance might even be worse than a single self-written trigger.
> Note that you cannot really write correct RI triggers without playing
> very low level games, i.e. writing C and using special kinds of
> snapshots and such.


Yeah, I really don't think that's a feasible way to to this.

The only way I have thought of as an alternative to this proposal is to 
use a partitioned table with different FK constraints for each child. 
That's certainly doable, but not without a deal of work, and even then 
you'd be giving up certain things, such as guaranteeing the uniqueness 
of the object key, at least without a lot more work.

You can think of it this way: we currently enforce FK constraints except 
when the value being constrained is NULL (or part of it is NULL in the 
MATCH SIMPLE case). This is really a user-defined extension of the 
exception condition. I have at least one case where I could have used 
this feature and saved a significant amount of work. We wanted to apply 
FK constraints to a very large table, but grandfather in certain cases 
that didn't meet the constraint. That could have been done very simply 
using this feature.

cheers

andrew