deferred cascade delete re-check at end of transaction?

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)

In response to

Responses

Browse pgsql-general by date

  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