Thread: How to defer ON DELETE CASCADE

How to defer ON DELETE CASCADE

From

Arjen Nienhuis

Date:

01 September 2010, 13:04:09

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)

=============

Re: How to defer ON DELETE CASCADE

From

Tom Lane

Date:

01 September 2010, 14:02:29

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