Re: augmenting MultiXacts to improve foreign keys

Excerpts from Tom Lane's message of mar ago 09 16:40:15 -0400 2011:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > Excerpts from Jeff Davis's message of mar ago 09 14:41:14 -0400 2011:
> >> Right now, FKs aren't really very special, they are mostly just
> >> syntactic sugar (right?). This proposal would make FKs special internal
> >> mechanisms, and I don't see the benefit in doing so.
>
> > Well, you can get the same behavior by adding the constraint triggers
> > manually. But those triggers are written in C, so you could equally
> > claim that they are "special internal" already. The SPI interface has
> > some special entry points to allow them to work correctly (for example
> > passing a snapshot for the checks to run with).
>
> Yeah, the crosscheck-snapshot logic already puts the lie to any idea
> that the RI triggers are equivalent to anything available at the SQL
> level.
>
> ISTM you could pass down the "please do this with FOR KEY UPDATE not
> just FOR SHARE" flag similarly to the way the crosscheck snapshot is
> passed down, or maybe even just do it if you see a crosscheck snapshot
> is present in the executor state (though that's a bit ugly).

You mean FOR KEY SHARE ...

Yeah, I could pass it down that way. But if we go that route, do we
need to specify any specific rowmark clause at all? Maybe we could go
back to FOR UPDATE and use the flag to tell the executor that it's
actually FOR KEY SHARE, and deprecate the FOR SHARE clause altogether.

> Like Florian, I'm considerably more concerned about the aspect of
> deciding which columns are "key columns" and whether they changed.

I will keep this in mind. I think this problem is considerably easier
to solve than the different rowmark per xact in MultiXact, anyway.
If we require the user to specify which unique indexes or constraints
are appropriate for FKs, it becomes trivial.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support