Обсуждение: BUG #3296: CASCADING FKs dont DEFER

The following bug has been logged online:

Bug reference:      3296
Logged by:          S. Robert James
Email address:      srobertjames@vesaria.com
PostgreSQL version: 8.1
Operating system:   Linux
Description:        CASCADING FKs dont DEFER
Details:

If a FK is set to ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, it does
not honor the defer.

That is, if you DELETE a record and then INSERT it back, you still get a FK
deletion of the referencing record.  This seems to ignore DEFERRED.

I did not seem to get this on 8.2 / Windows

"S. Robert James" <srobertjames@vesaria.com> writes:
> If a FK is set to ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, it does
> not honor the defer.

> That is, if you DELETE a record and then INSERT it back, you still get a FK
> deletion of the referencing record.  This seems to ignore DEFERRED.

Actually, the cascaded delete happens immediately regardless of "defer"
mode; you can only defer constraint checks, not cascade actions.  As far
as I can tell, this is the behavior defined by the SQL spec.  Notice in
particular this statement (from SQL92 11.8 <referential constraint
definition>)

         9) 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.

which makes it clear that the committee thinks that cascade deletes are
separate from constraint checks.

            regards, tom lane