On Wed, 2007-09-19 at 19:31 +0800, Ow Mun Heng wrote: > On Wed, 2007-09-19 at 11:05 +0100, Filip Rembiałkowski wrote: > > 2007/9/19, Ow Mun Heng <Ow.Mun.Heng@xxxxxxx>: > > > > (...) > > > > > simulate a delete > > > => delete from parent where id in (select id from child); > > > DELETE 6 > > > > > > => select * from parent; > > > id | data1 > > > ----+--------- > > > 2 | parent2 > > > 3 | parent3 > > > 4 | parent4 > > > > > > => select * from child; > > > id | data1 > > > ----+------- > > > (0 rows) > > > > > Yes. You can however try > > > > SELECT FROM ... ONLY parent ... > > (that's what I used in example) > > > > and > > DELETE FROM ... ONLY parent ... > > Let me re-try this and see how it goes. > I tested this last night and it works (to a fault) anyway. just FYI.. the process I'm doing.. pull from mssql \copy into PG temp table begin delete unique_id from master if exists in child insert into master from child truncate child update sync_log commit; I tested the above last night and the issue I'm seeing here is locking. and I've to rewrite the queries such that they will only read from the parent table. => select * from ONLY parent where x = Y etc.. and I can't do a : => select * from parent where x = Y etc.. as the table truncation step will lock the entire table (?) (I see an ExclusiveLock in one of the transactions) This is good to know anyway, so it's still usable, but will likely need user training etc which may be bad. Are there any other suggestions? Else I think a plpgsql function to add in new columns automatically to the 3 different tables will be a another good option as well. ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
