-----BEGIN PGP SIGNED MESSAGE-----

The Postgres guide says that foreign keys can be partially emulated
via triggers.  Just how "partial" is this.  I can't seem to get the
following to work.  Would it work if I wrote it in C?  Would I need to
open a second connection to the database?  Would it work if my second
key was really in another table?

project=> CREATE TABLE task (
project->     task_id             INT PRIMARY KEY,
project->     task_pid            INT
project-> );
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'task_pkey' for table 'task'
CREATE
project=> CREATE FUNCTION check_task_pid () RETURNS OPAQUE AS '
project'>     BEGIN
project'>         IF NEW.task_pid IS NOT NULL THEN
project'>             SELECT task_id FROM task WHERE task_id = NEW.task_pid;
project'>             IF NOT FOUND THEN
project'>                 RAISE EXCEPTION ''FOREIGN KEY constraint task_pid not found'';
project'>             END IF;
project'>         END IF;
project'>         RETURN NEW;
project'>     END;
project'> ' LANGUAGE 'plpgsql';
CREATE
project=> CREATE TRIGGER iu_btrig_check_task_pid BEFORE INSERT OR UPDATE ON task
project->     FOR EACH ROW EXECUTE PROCEDURE check_task_pid();
CREATE
project=> insert into task values (1, null);
INSERT 27855 1
project=> insert into task values (2, null);
INSERT 27856 1
project=> insert into task values (3, 1);
ERROR:  unexpected SELECT query in exec_stmt_execsql()

roland
- -- 
                       PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD                  Custom Software Solutions
roberts@panix.com                      76-15 113th Street, Apt 3B
rbroberts@acm.org                          Forest Hills, NY 11375

-----BEGIN PGP SIGNATURE-----
Version: 2.6.3a
Charset: noconv
Comment: Processed by Mailcrypt 3.5.4, an Emacs/PGP interface

iQCVAwUBOJJrHeoW38lmvDvNAQHoqAP/X5oaVa3vSyBu+6WKhRMiuWVJMTN6OSDf
KMhc2kQ67qf2eULtQLe8D9YqlHg92ezHH2xGuzbDab5ha9i0vDGHLbR6Zo93EoGT
TCqriZ6xBNec4m4PgB5QQZfKRlsvrSsBgTnpnsOc6SWnKfVgNPK4If/qNYUQKmjp
3O2bwrslErE=
=+Sp8
-----END PGP SIGNATURE-----

28 Jan 2000 23:22:54 -0500
Roland Roberts <roberts@panix.com>
Help with pl/pgsql, triggers, and foreign keys
At 11:22 PM 1/28/00 -0500, Roland Roberts wrote:
>-----BEGIN PGP SIGNED MESSAGE-----
>
>The Postgres guide says that foreign keys can be partially emulated
>via triggers.  Just how "partial" is this.  I can't seem to get the
>following to work.  Would it work if I wrote it in C?  Would I need to
>open a second connection to the database?  Would it work if my second
>key was really in another table?

Given that foreign keys are implemented in the current sources and
coming out for real in v7.0 beta in two weeks, how much effort do
you really want to put into emulation???



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Fri, 28 Jan 2000 21:56:06 -0800
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] Help with pl/pgsql, triggers, and foreign keys
Roland Roberts wrote:
> 
> The Postgres guide says that foreign keys can be partially emulated
> via triggers.  Just how "partial" is this.  I can't seem to get the
> following to work.  Would it work if I wrote it in C?  Would I need to
> open a second connection to the database?  Would it work if my second
> key was really in another table?
> 
> project=> CREATE FUNCTION check_task_pid () RETURNS OPAQUE AS '
> project'>     BEGIN
> project'>         IF NEW.task_pid IS NOT NULL THEN
> project'>             SELECT task_id FROM task WHERE task_id = NEW.task_pid;
> project'>             IF NOT FOUND THEN
> project'>                 RAISE EXCEPTION ''FOREIGN KEY constraint task_pid not found'';
> project'>             END IF;
> project'>         END IF;
> project'>         RETURN NEW;
> project'>     END;
> project'> ' LANGUAGE 'plpgsql';
> CREATE
> project=> CREATE TRIGGER iu_btrig_check_task_pid BEFORE INSERT OR UPDATE ON task
> project->     FOR EACH ROW EXECUTE PROCEDURE check_task_pid();

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

As for your failing SELECT query, the following tweak to your function
makes it work as expected:

CREATE FUNCTION check_task_pid () RETURNS OPAQUE AS '
    DECLARE
        tmp RECORD;
    BEGIN
        IF NEW.task_pid IS NOT NULL THEN
            SELECT INTO tmp task_id FROM task WHERE task_id =
NEW.task_pid;
            IF NOT FOUND THEN
                RAISE EXCEPTION ''FOREIGN KEY constraint task_pid not
found'';
            END IF;
        END IF;
        RETURN NEW;
    END;
' LANGUAGE 'plpgsql';


Cheers,
Ed Loehr

Sat, 29 Jan 2000 02:37:21 -0600
Ed Loehr <eloehr@austin.rr.com>
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 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@debis.com (Jan Wieck) #



Mon, 31 Jan 2000 10:49:52 +0100 (CET)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] Help with pl/pgsql, triggers, and foreign keys