Re: Proposed feature: Selective Foreign Keys

On 2013-12-02 12:10:32 +0000, Florian Pflug wrote:
> On Dec2, 2013, at 10:06 , Andres Freund <andres(at)2ndquadrant(dot)com> 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.
>
> Very true. I'm unsure whether that's an argument in favour of extending
> the built-in FK triggers, or to expose the necessary functionality at the
> SQL level, though ;-)

Pretty much neither ;). I was just commenting on the fact that I don't
think Albe's argument has much merit in the current state of
postgresql. I haven't thought sufficiently thought about the issue to
have a clear plan what I think is right.

> I once tried to do the latter, in a way, by removing the need for the
> cross-checking logic (which is the only real low-level game that the
> built-in FK triggers play) altogether. That, unfortunately, didn't pan
> out - it would have required enlarging the on-disk tuple size to be
> able to record to last transaction which locked a tuple even after the
> transaction completes.

That infrastructure kinda is there now though, in the form of multixacts
we have now. I haven't followed the idea back then, but maybe that could
be reused.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services