Обсуждение: How to defer ON DELETE CASCADE
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)
=============
Arjen Nienhuis <a.g.nienhuis@gmail.com> writes:
> 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.
Yeah, this is per SQL spec as far as we can tell. Constraint checks can
be deferred till end of transaction, but "referential actions" are not
deferrable. They always happen during the triggering statement. For
instance SQL99 describes the result of a cascade deletion as being that
the referencing row is "marked for deletion" immediately, and then
15) All rows that are marked for deletion are effectively deleted
at the end of the SQL-statement, prior to the checking of any
integrity constraints.
(see 11.8 <referential constraint definition> general rules)
regards, tom lane