Обсуждение: Help with pl/pgsql, triggers, and foreign keys
-----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-----
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 Serviceand other goodies at http://donb.photo.net.
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 NULLTHEN 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
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) #