| Lists: | pgsql-bugs |
|---|
| From: | "S(dot) Robert James" <srobertjames(at)vesaria(dot)com> |
|---|---|
| To: | pgsql-bugs(at)postgresql(dot)org |
| Subject: | BUG #3296: CASCADING FKs dont DEFER |
| Date: | 2007-05-22 08:24:00 |
| Message-ID: | 200705220824.l4M8O0nT067095@wwwmaster.postgresql.org |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 3296
Logged by: S. Robert James
Email address: srobertjames(at)vesaria(dot)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
| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | "S(dot) Robert James" <srobertjames(at)vesaria(dot)com> |
| Cc: | pgsql-bugs(at)postgresql(dot)org |
| Subject: | Re: BUG #3296: CASCADING FKs dont DEFER |
| Date: | 2007-05-22 13:57:49 |
| Message-ID: | 12066.1179842269@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-bugs |
"S. Robert James" <srobertjames(at)vesaria(dot)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