I'm trying to combind a cascading delete and a constraint deferal.
The hope is to have a cascading delete to clean up data, but only
after a commit. I can't get it to work. For example in the following
code:

create temp table primarytest (col2 integer primary key);
create temp table defertest( col2 integer references primarytest on
update cascade on delete cascade deferrable initially deferred);
insert into primarytest values(5);
insert into defertest values (5);
select * from defertest;
begin;
set constraints all deferred;
delete from primarytest;
insert into primarytest values(5);
commit;
select * from defertest;

The row in defertest is deleted even though the value (5) is in
primarytest at the end of the session. I was hoping all explicit
session transactions would be exceuted prior to nay constraints, but
apparently that's not the case.

Is there any way to do what I want without changing to an update?

Thanks
Paul

Paul wrote:
As you have found out, ON DELETE CASCADE will delete the dependent
rows as soon as the DELETE statement is executed. There is no way
to change that.

If you need to avoid an UPDATE (your example screams for it, but
I guess that you have your reasons), you could use the (default) clause
ON DELETE NO ACTION. This will *not* delete the dependent rows,
but the check can be deferred to commit time.

If you want both ("deferred delete of dependent rows"), I guess you'd
have to write it yourself, by using ON DELETE NO ACTION and explicitly
deleting the offending dependent rows before COMMIT.

Yours,
Laurenz Albe

On Thu, 04 Mar 2010 10:21:47 +0100, Laurenz Albe wrote:


Postgres has foreign keys implemented using triggers, which means that
this is really not much more expensive than the normal "ON DELETE
CASCADE" thing, unless the trigger is messed up and programmed badly.



--
http://mgogala.freehostia.com

Mladen Gogala wrote:
That won't help, because (as you have found out) trigger execution
can also not be deferred.

Yours,
Laurenz Albe