deferred cascade delete re-check at end of transaction? - Mailing list 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)