Re: 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.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.