Re: [HACKERS] Help with pl/pgsql, triggers, and foreign keys

Ed Loehr wrote:

> At least in 6.5.2, you can definitely implement referential integrity
> (RI) via pl/pgsql.  As someone noted earlier, RI is to be released in
> 7.0, but I suspect it will take a subsequent release or two to
> stabilize before it's fit for consumption by the more conservative
> reliability-focused users among us...
   I hope that this isn't true.
   First, because FOREIGN KEY is implemented as builtin triggers   written in C.  BETA should turn out most of the
bugs, which   could still be in it.
 
   Second,  RI  cannot  get  implemented  reliable  with regular   triggers.   You  can  easily  violate  the
semantics  with   concurrently  running  transactions.   Have first transaction   inserting a reference, the trigger
checksfor  key  existence   and  finds it. Now second transaction deletes the key, and an   eventually existing ON
DELETECASCADE trigger fired  on  that   wouldn't  find the reference, because it isn't committed yet.   Second
transactioncommits, what finally removes the key. Now   first  transaction commits, making the reference visible, but
referencinga non existing key - inconsistency.
 
   So anyone who needs referential integrity is asked to  stress   the code as far as he can, at least during BETA.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #