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
