Re: complex referential integrity constraints

>-----Original Message-----
>From: Martijn van Oosterhout [mailto:kleptog(at)svana(dot)org]
>Sent: vrijdag 23 februari 2007 9:50
>To: Joris Dobbelsteen
>Cc: pgsql-general(at)postgresql(dot)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.

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?

- Joris