From: | Tom Dunstan <pgsql(at)tomd(dot)cc> |
---|---|
To: | Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
Cc: | "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Proposed feature: Selective Foreign Keys |
Date: | 2013-12-02 12:48:44 |
Message-ID: | 58DA09B8-6DF1-4816-B17A-1BF0505FF150@tomd.cc |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi Laurenz!
On 2 Dec 2013, at 19:27, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> 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.
Well, the main cost on insert in the FK table should be looking for matching rows in the referenced tables, which the patch avoids for non-matching rows. So while you’ll get the overhead of N triggers firing, you only pay the expected query cost (which will even use a partial index if you’ve got one set up). Each of the referenced tables is only involved in one FK, so there’s no difference in cost there.
> Now performance isn't everything, but that would mean that the benefit
> of your proposal is entirely on the usability side.
Well, I don’t think there’s much of a performance hit, and I don’t think any of the alternatives would perform much better in practice, but certainly performance wasn’t a motivating factor for this feature, it was a) correctness and b) avoiding the ugliness of the existing solutions.
> I personally don't think that it is so difficult to write a trigger
> for that functionality yourself, but I guess that the argument for
> this feature rests on how coveted such a functionality would be
> (to justify the trade-off in code complexity).
The patch is pretty small so far - and more than half of it is regression tests. So there’s not much extra code complexity IMO. I wouldn’t want to touch the FK system with anything but the lightest touch.
Cheers
Tom
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2013-12-02 12:48:45 | Re: Proposed feature: Selective Foreign Keys |
Previous Message | Sergey Burladyan | 2013-12-02 12:13:18 | Re: Draft release notes for 9.3.2 |