deferred cascade delete re-check at end of transaction?
От | Michael Adler |
---|---|
Тема | deferred cascade delete re-check at end of transaction? |
Дата | |
Msg-id | Pine.NEB.4.44.0210141145010.23264-100000@reva.sixgirls.org обсуждение исходный текст |
Ответ на | Re: Removing {"="} privledges (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: deferred cascade delete re-check at end of transaction?
(Stephan Szabo <sszabo@megazone23.bigpanda.com>)
|
Список | 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)
В списке pgsql-general по дате отправления: