Обсуждение: keeping 3 tables in sync w/ each other
Hi, I have 3 tables foo foo_loading_source1 foo_loading_source2 which is something like create table foo (a int, b int, c int) create table foo_loading_source1 (a int, b int, c int) create table foo_loading_source2 (a int, b int, c int) Is there a way which can be made easier to keep these 3 tables DDL in sync? the loading_sourceX tables are just a temporary-in-transit table for data \copy'ied into the DB before being inserted into the main foo table. Currently, each time I add a new column to foo, I have to "remember" to add the same to the other 2 table. Can I use inheritance? References? or will I have to create a procedural function such that it will add the same ddl to all 3 tables each time a new column is defined?
2007/9/18, Ow Mun Heng <Ow.Mun.Heng@wdc.com>: > Hi, > > I have 3 tables > > foo > foo_loading_source1 > foo_loading_source2 > > which is something like > > create table foo (a int, b int, c int) > create table foo_loading_source1 (a int, b int, c int) > create table foo_loading_source2 (a int, b int, c int) > > Is there a way which can be made easier to keep these 3 tables DDL in > sync? > > the loading_sourceX tables are just a temporary-in-transit table for > data \copy'ied into the DB before being inserted into the main foo > table. > > Currently, each time I add a new column to foo, I have to "remember" to > add the same to the other 2 table. > > Can I use inheritance? References? Inheritance might work in this case. But it will be a bit weird, because you will see non-constraint data in parent unless you will SELECT ... FROM ONLY parent Try this example: create table parent ( id serial, data1 text ); create table child () inherits( parent ); \d child alter table only parent add check ( data1 like '%fits parent' ); insert into parent(data1) select 'this data fits parent'; insert into child(data1) select 'this data was inserted to child'; select * from parent; select * from only parent; select * from child; alter table parent add column data2 text default 'new column default'; \d child -- Filip Rembiałkowski
Ow Mun Heng wrote: > Hi, > > I have 3 tables > > foo > foo_loading_source1 > foo_loading_source2 > > which is something like > > create table foo (a int, b int, c int) > create table foo_loading_source1 (a int, b int, c int) > create table foo_loading_source2 (a int, b int, c int) > > Is there a way which can be made easier to keep these 3 tables DDL in > sync? > > the loading_sourceX tables are just a temporary-in-transit table for > data \copy'ied into the DB before being inserted into the main foo > table. > > Since these are temporary tables, why don't you just create them on the fly as temporary tables? CREATE TEMPORARY TABLE foo_loading_source1 (LIKE foo); CREATE TEMPORARY TABLE foo_loading_source2 (LIKE foo); Then do your loading process. Then you don't really have to worry about maintaining the loading tables at all.
> Can I use inheritance? References? inheritance
On Tue, 2007-09-18 at 09:56 +0100, Filip Rembiałkowski wrote: > 2007/9/18, Ow Mun Heng <Ow.Mun.Heng@wdc.com>: > > Hi, > > > > I have 3 tables > > > > foo > > foo_loading_source1 > > foo_loading_source2 > > > > which is something like > > > > create table foo (a int, b int, c int) > > create table foo_loading_source1 (a int, b int, c int) > > create table foo_loading_source2 (a int, b int, c int) > > > > Is there a way which can be made easier to keep these 3 tables DDL in > > sync? > > > > the loading_sourceX tables are just a temporary-in-transit table for > > data \copy'ied into the DB before being inserted into the main foo > > table. > > > > Currently, each time I add a new column to foo, I have to "remember" to > > add the same to the other 2 table. > > > > Can I use inheritance? References? > > Inheritance might work in this case. But it will be a bit weird, > because you will see non-constraint data in parent unless you will > SELECT ... FROM ONLY parent > > > Try this example: > > create table parent ( id serial, data1 text ); > create table child () inherits( parent ); > \d child > alter table only parent add check ( data1 like '%fits parent' ); > insert into parent(data1) select 'this data fits parent'; > insert into child(data1) select 'this data was inserted to child'; > select * from parent; > select * from only parent; > select * from child; > alter table parent add column data2 text default 'new column default'; > \d child Nope. Doesn't work as it should be. Note : I've removed the check as well as it's not needed for my purpose. create table parent ( id int primary key, data1 text ); create table child () inherits( parent ); insert into parent(id,data1) values (1,'parent1'); insert into parent(id,data1) values (2,'parent2'); insert into parent(id,data1) values (3,'parent3'); insert into parent(id,data1) values (4,'parent4'); insert into child(id,data1) values (6,'child1-bastard'); insert into child(id,data1) values (7,'child2-bastard'); insert into child(id,data1) values (8,'child3-bastard'); insert into child(id,data1) values (9,'child4-bastard'); => select * from parent; id | data1 ----+---------------- 1 | parent1 2 | parent2 3 | parent3 4 | parent4 6 | child1-bastard 7 | child2-bastard 8 | child3-bastard 9 | child4-bastard 1 | parent1-new 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) Doesn't do what I want which is to use the child table as a temp holding ground prior to data insertion into parent table.
On Tue, 2007-09-18 at 08:37 -0400, btober@ct.metrocast.net wrote: > Ow Mun Heng wrote: > > Hi, > > > > create table foo (a int, b int, c int) > > create table foo_loading_source1 (a int, b int, c int) > > create table foo_loading_source2 (a int, b int, c int) > > > > Is there a way which can be made easier to keep these 3 tables DDL in > > sync? > Since these are temporary tables, why don't you just create them on the > fly as temporary tables? > > CREATE TEMPORARY TABLE foo_loading_source1 (LIKE foo); > > CREATE TEMPORARY TABLE foo_loading_source2 (LIKE foo); > > Then do your loading process. Then you don't really have to worry about > maintaining the loading tables at all. > Yes, I've thought of this, but wouldn't this cause additional overhead as the loading process may be initiated between every 60sec to 30mins for a handful of tables each time. (which was why I asked the list)
2007/9/19, Ow Mun Heng <Ow.Mun.Heng@wdc.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 ... -- Filip Rembiałkowski
On Wed, 2007-09-19 at 11:05 +0100, Filip Rembiałkowski wrote: > 2007/9/19, Ow Mun Heng <Ow.Mun.Heng@wdc.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 ... Here's the obigatory Ooohhh or the simpson's "Doh!" Let me re-try this and see how it goes. Many thanks for the Ooo... ( I feel like the "green aliens" in Toy Story)
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@wdc.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.