pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp with pl/pgsql, triggers, and foreign keys
4+ messages / 4 participants
[nested] [flat]
* Help with pl/pgsql, triggers, and foreign keys
@ 2000-01-29 04:22 Roland Roberts <roberts@panix.com>
0 siblings, 1 reply; 4+ messages in thread
From: Roland Roberts @ 2000-01-29 04:22 UTC (permalink / raw)
To: pgsql-hackers@postgreSQL.org; pgsql-sql
-----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-----
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [HACKERS] Help with pl/pgsql, triggers, and foreign keys
@ 2000-01-29 05:56 Don Baccus <dhogaza@pacifier.com>
0 siblings, 0 replies; 4+ messages in thread
From: Don Baccus @ 2000-01-29 05:56 UTC (permalink / raw)
To: roberts@panix.com; pgsql-hackers@postgresql.org; pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [HACKERS] Help with pl/pgsql, triggers, and foreign keys
@ 2000-01-29 08:37 Ed Loehr <eloehr@austin.rr.com>
parent: Roland Roberts <roberts@panix.com>
0 siblings, 1 reply; 4+ messages in thread
From: Ed Loehr @ 2000-01-29 08:37 UTC (permalink / raw)
To: roberts@panix.com; +Cc: pgsql-hackers@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [HACKERS] Help with pl/pgsql, triggers, and foreign keys
@ 2000-01-31 09:49 Jan Wieck <wieck@debis.com>
parent: Ed Loehr <eloehr@austin.rr.com>
0 siblings, 0 replies; 4+ messages in thread
From: wieck@debis.com @ 2000-01-31 09:49 UTC (permalink / raw)
To: Ed Loehr <eloehr@austin.rr.com>; +Cc: roberts@panix.com; pgsql-hackers@postgreSQL.org; pgsql-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@debis.com (Jan Wieck) #
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2000-01-31 09:49 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-01-29 04:22 Help with pl/pgsql, triggers, and foreign keys Roland Roberts <roberts@panix.com>
2000-01-29 08:37 ` Ed Loehr <eloehr@austin.rr.com>
2000-01-31 09:49 ` Jan Wieck <wieck@debis.com>
2000-01-29 05:56 Re: [HACKERS] Help with pl/pgsql, triggers, and foreign keys Don Baccus <dhogaza@pacifier.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox