Re: keeping 3 tables in sync w/ each other

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(dot)Mun(dot)Heng(at)wdc(dot)com>:
> >
> > (...)
> >
> > > 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.