Hi,
If I create a DEFERRED ON DELETE CASCADE constraint, it doesn't really work as I expected. I expected it to defer the deletion to the end of the transaction, but it dosn't.
Is there a way to replace the contents of a table which has foreign keys? There's no MERGE/UPSERT/whatever either.
=========
SELECT version();
version
-------------------------------------------------------------------------------------------------------------
PostgreSQL 8.4.4 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit
(1 row)
CREATE TABLE product (id INT PRIMARY KEY);
CREATE TABLE product_item (product_id INT REFERENCES product(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED);
INSERT INTO product VALUES (5);
INSERT INTO product_item VALUES (5);
BEGIN;
DELETE FROM product;
INSERT INTO product VALUES (5);
COMMIT;
SELECT * FROM product_item;
product_id
------------
(0 rows)
=============