From: | Michael Adler <adler(at)glimpser(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | deferred cascade delete re-check at end of transaction? |
Date: | 2002-10-14 15:56:37 |
Message-ID: | Pine.NEB.4.44.0210141145010.23264-100000@reva.sixgirls.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have written a test that demonstrates a behavior that surprises me.
I store a foreign key with a deferable cascade-delete. While in a
transaction, I delete and then re-insert the referenced key. Since the key
value is back in the table, I would expect the delete to NOT cascade, but
apparently it does.
Is there another way to acheive this behavior?
-- here's the test
DROP TABLE a;
DROP TABLE b;
CREATE TABLE a (
pk INTEGER PRIMARY KEY,
this TEXT
);
CREATE TABLE b (
fk INTEGER REFERENCES a(pk) ON delete cascade DEFERRABLE INITIALLY
DEFERRED,
that TEXT
);
INSERT INTO a (pk, this) VALUES (1,'foo');
INSERT INTO a (pk, this) VALUES (2,'bar');
INSERT INTO b (fk,that) VALUES (1,'foofoo');
INSERT INTO b (fk,that) VALUES (2,'barbar');
SELECT * FROM a;
SELECT * FROM b;
BEGIN;
DELETE FROM a WHERE pk = 1;
INSERT INTO a (pk,this) VALUES (1,'foo-replacement');
SELECT * FROM a;
SELECT * FROM b;
COMMIT;
SELECT * FROM a;
SELECT * FROM b;
-- run with "psql -e -f defer_ri_test.sql defertest"
DROP TABLE a;
psql:defer_ri_test.sql:3: NOTICE: DROP TABLE implicitly drops referential
integrity trigger from table "b"
DROP
DROP TABLE b;
DROP
CREATE TABLE a (
pk INTEGER PRIMARY KEY,
this TEXT
);
psql:defer_ri_test.sql:9: NOTICE: CREATE TABLE / PRIMARY KEY will create
implicit index 'a_pkey' for table 'a'
CREATE
CREATE TABLE b (
fk INTEGER REFERENCES a(pk) ON delete cascade DEFERRABLE INITIALLY
DEFERRED,
that TEXT
);
psql:defer_ri_test.sql:14: NOTICE: CREATE TABLE will create implicit
trigger(s) for FOREIGN KEY check(s)
CREATE
INSERT INTO a (pk, this) VALUES (1,'foo');
INSERT 212767 1
INSERT INTO a (pk, this) VALUES (2,'bar');
INSERT 212768 1
INSERT INTO b (fk,that) VALUES (1,'foofoo');
INSERT 212769 1
INSERT INTO b (fk,that) VALUES (2,'barbar');
INSERT 212770 1
SELECT * FROM a;
pk | this
----+------
1 | foo
2 | bar
(2 rows)
SELECT * FROM b;
fk | that
----+--------
1 | foofoo
2 | barbar
(2 rows)
BEGIN;
BEGIN
DELETE FROM a WHERE pk = 1;
DELETE 1
INSERT INTO a (pk,this) VALUES (1,'foo-replacement');
INSERT 212771 1
SELECT * FROM a;
pk | this
----+-----------------
2 | bar
1 | foo-replacement
(2 rows)
SELECT * FROM b;
fk | that
----+--------
1 | foofoo
2 | barbar
(2 rows)
COMMIT;
COMMIT
SELECT * FROM a;
pk | this
----+-----------------
2 | bar
1 | foo-replacement
(2 rows)
SELECT * FROM b;
fk | that
----+--------
2 | barbar
(1 row)
From | Date | Subject | |
---|---|---|---|
Next Message | Francois Suter | 2002-10-14 16:14:47 | Advocacy site in French |
Previous Message | Gregory Wood | 2002-10-14 15:34:49 | Re: Asta with PostgreSQL |