Re: BUG #3296: CASCADING FKs dont DEFER

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
Thread:
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

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Stephan Szabo 2007-05-22 14:25:52 Re: ON DELETE CASCADE with multiple paths
Previous Message adam terrey 2007-05-22 08:45:34 Inconsistant SQL results - Suspected error with query planing or query optimisation.