>-----Original Message----- >From: Martijn van Oosterhout [mailto:kleptog@xxxxxxxxx] >Sent: vrijdag 23 februari 2007 9:50 >To: Joris Dobbelsteen >Cc: pgsql-general@xxxxxxxxxxxxxx >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
