Re: How to defer ON DELETE CASCADE

Arjen Nienhuis <a(dot)g(dot)nienhuis(at)gmail(dot)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