Re: complex referential integrity constraints - Mailing list pgsql-general

On Fri, 23 Feb 2007, Joris Dobbelsteen wrote:

> >-----Original Message-----
> >From: Martijn van Oosterhout [mailto:kleptog@svana.org]
> >Sent: vrijdag 23 februari 2007 9:50
> >To: Joris Dobbelsteen
> >Cc: pgsql-general@postgresql.org
> >Subject: Re: [GENERAL] complex referential integrity constraints
> >
> >On Fri, Feb 23, 2007 at 12:41:25AM +0100, Joris Dobbelsteen wrote:
> >> >Reasonably. I have no idea what visibility rules would make any
> >> >difference at all. AIUI a foreign key just takes a shared
> >lock on the
> >> >referenced row and all the magic of MVCC makes sure the row exists
> >> >when the transaction completes.
> >>
> >> Try this:
> >> (sorry for any typo's in SQL, if they exist)
> >
> ><snip>
> >
> >Well, I took a look at the RI code and the only stuff I saw
> >that looked interesting was this:
> >
> >utils/adt/ri_triggers.c:
> >        if (IsXactIsoLevelSerializable && detectNewRows)
> >        {
> >                CommandCounterIncrement();              /* be
> >sure all my own work is visible */
> >                test_snapshot = CopySnapshot(GetLatestSnapshot());
> >                crosscheck_snapshot =
> >CopySnapshot(GetTransactionSnapshot());
> >        }
> >
> >It then proceeds to use that snapshot to execute the query to
> >get the share lock.
> >
> >It's probably true that other PL's can't do this directly. Not
> >sure how to deal with that. I got confused because I thought
> >the first version of RI did use straight pl/pgsql functions,
> >so I thought that was enough.
>
> You got it right...
>
> /*
>  * SPI_execute_snapshot -- identical to SPI_execute_plan, except that we
> allow
>  * the caller to specify exactly which snapshots to use.  This is
> currently
>  * not documented in spi.sgml because it is only intended for use by RI
>  * triggers.
>  *
>  * Passing snapshot == InvalidSnapshot will select the normal behavior
> of
>  * fetching a new snapshot for each query.
>  */
> int
> SPI_execute_snapshot(void *plan,
>                      Datum *Values, const char
> *Nulls,
>                      Snapshot snapshot, Snapshot
> crosscheck_snapshot,
>                      bool read_only, long tcount)
>
> They got the point right: only intended for use by RI triggers. That's
> exactly the type I'm trying to build ;)
> They are exposed to the C versions (its in include/executor/spi.h), but
> to me it looks a bit cumbersome to have triggers written in C.

I was wondering if some sort of generator might work. Something that would
take what you're trying to do and generate the triggers for you, but I
haven't really worked out what that'd look like.

> What would be a good way to expose this to normal PL triggers? Since
> this would open a new set of possibilities...
>
> As part of a "create trigger ... for referencial integrity"?
> As an extension to a statement?
> Special construct in the languages?

I think the first thing to do is to figure out what such triggers need to
do. Does such a trigger need to potentially run some queries on the normal
snapshot? Does it potentially need different snapshots for different
statements or is only one special snapshot sufficient? And other such
questions. From there, a -hackers discussion might be meaningful.