Thread: on cascade delete performances, transaction and set constraints deferred

I've something like

create table p (
  pid int primary key
  -- other stuff
);

create table s1 (
  s1id int primary key,
  pid int references p (pid) on delete cascade
  -- other stuff
);

begin;
set constraints all deferred;
delete from p;

-- insert into p (pid) select * atable;
-- insert into s1 (s1id, pid) select * anothertable;

commit;

s1 are proprieties of p (one 2 many).
What I'd like to obtain is reload from scratch p and s1.
I wonder if this is a good way.
Actually if I defer constraints, delete shouldn't take place... but
then I'll try to insert new records in s1 adding duplicates s1id.
Uniqueness constraint can't be deferred so I'll have a problem.

What happens is that when I delete all rows in p, it takes forever
to delete rows in s1.

I just ^C psql and it the error message told me it was executing a
DELETE statement on s1.
I thought that since constraint were deferred DELETE statements
would be executed after inserts (in actual code inserts were not
comented out). But is seems it's not happening what I expect.

Surely I could just clean s1 "manually". Since there won't be any
"where clause" it should be faster and it is going to solve my
uniqueness problem too... but that requires extra bookkeeping.
s1 isn't the only related table

I'm not sure what's really happening but why apparently the delete
statements get executed before the 2 inserts even if constraints are
deferred?
Why does it take so long?
2x old Xeon, HT but not dual core, 3.2GHz, 4Gb RAM, SCSI RAID 5,
~600K records in p and s1 > 5min.

What should be the correct way to do it without too much bookkeeping?

thanks

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

Ivan Sergio Borgonovo <mail@webthatworks.it> writes:
> I'm not sure what's really happening but why apparently the delete
> statements get executed before the 2 inserts even if constraints are
> deferred?

You didn't mark the FK constraints as deferrable --- I'm pretty sure
the default is NOT DEFERRABLE.

> Why does it take so long?

Indexes on the FK columns would probably help.

            regards, tom lane

On Mon, 18 Aug 2008 15:16:01 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Ivan Sergio Borgonovo <mail@webthatworks.it> writes:
> > I'm not sure what's really happening but why apparently the
> > delete statements get executed before the 2 inserts even if
> > constraints are deferred?
>
> You didn't mark the FK constraints as deferrable --- I'm pretty
> sure the default is NOT DEFERRABLE.

My fault. I don't know how did I succeeded to read that the default
was DEFERRABLE while it is clearly stated it is not.

I can't see a direct way to add DEFERRABLE to an already existing
constraint. Is dropping and adding the only path?

> > Why does it take so long?
>
> Indexes on the FK columns would probably help.

There is one actually. I'll post another email with a more sensible
subject to ask more advices on this, if google doesn't help me first.

Thanks.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it