Skip site navigation (1) Skip section navigation (2)

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

From: wieck(at)debis(dot)com (Jan Wieck)
To: Ed Loehr <eloehr(at)austin(dot)rr(dot)com>
Cc: roberts(at)panix(dot)com, pgsql-hackers(at)postgreSQL(dot)org, pgsql-sql(at)postgreSQL(dot)org
Subject: Re: [HACKERS] Help with pl/pgsql, triggers, and foreign keys
Date: 2000-01-31 09:49:52
Message-ID: m12FDSy-0003kdC@orion.SAPserv.Hamburg.dsh.de (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-sql
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 checks for  key  existence
    and  finds it. Now second transaction deletes the key, and an
    eventually existing ON DELETE CASCADE trigger fired  on  that
    wouldn't  find the reference, because it isn't committed yet.
    Second transaction commits, what finally removes the key. Now
    first  transaction commits, making the reference visible, but
    referencing a 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(at)debis(dot)com (Jan Wieck) #



In response to

pgsql-hackers by date

Next:From: Hannu KrosingDate: 2000-01-31 10:32:53
Subject: Re: [HACKERS] Re: ORDBMS
Previous:From: Jan WieckDate: 2000-01-31 08:41:51
Subject: Re: [HACKERS] END/ABORT

pgsql-sql by date

Next:From: Peter EisentrautDate: 2000-01-31 12:21:27
Subject: Re: [SQL] inet/cidr - can this be done?
Previous:From: Tom LaneDate: 2000-01-30 22:29:41
Subject: Re: [SQL] "Group by" and "index".

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group