pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
"mirroring" a table - PostgreSQL 7.3.2
6+ messages / 3 participants
[nested] [flat]

* "mirroring" a table - PostgreSQL 7.3.2
@ 2004-01-24 19:35 C. Bensend <benny@bennyvision.com>
  2004-01-24 21:09 ` Re: "mirroring" a table - PostgreSQL 7.3.2 Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: C. Bensend @ 2004-01-24 19:35 UTC (permalink / raw)
  To: pgsql-admin


Hey folks,

   I searched the archives and didn't really come up with much, so I'm
posting my question here.

   I have two tables in the same database, whose structure I want to be
identical.  Call them 'bob' and 'test_bob' for example.

   If I make a change to the schema of 'bob', I want that exact change to
happen to 'test_bob'.  The data is irrelevant - any data in 'test_bob' is
going to be manipulated, spindled, folded, and mutilated.  The data in
'bob' must remain intact.

   For example, if I ALTER a column or ADD a column to 'bob', I need the
exact same thing to happen to 'test_bob'.

   Is this possible without the typical DROP and CREATE TABLE?  Many
thanks for any input.  :)

Benny


-- 
"Have you ever tried simply turning off the TV, sitting down with your
children, and hitting them?"                   -- Bender, "Futurama"



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: "mirroring" a table - PostgreSQL 7.3.2
  2004-01-24 19:35 "mirroring" a table - PostgreSQL 7.3.2 C. Bensend <benny@bennyvision.com>
@ 2004-01-24 21:09 ` Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-24 21:21   ` Re: "mirroring" a table - PostgreSQL 7.3.2 C. Bensend <benny@bennyvision.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 2004-01-24 21:09 UTC (permalink / raw)
  To: C. Bensend <benny@bennyvision.com>; +Cc: pgsql-admin

"C. Bensend" <benny@bennyvision.com> writes:
>    I have two tables in the same database, whose structure I want to be
> identical.  Call them 'bob' and 'test_bob' for example.

>    If I make a change to the schema of 'bob', I want that exact change to
> happen to 'test_bob'.

Possibly you could get the effect you want by making one a child of the
other.  Read up on inheritance.  There are some side-effects you'd
probably *not* want, so this isn't a perfect solution, but I can't think
of anything else.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: "mirroring" a table - PostgreSQL 7.3.2
  2004-01-24 19:35 "mirroring" a table - PostgreSQL 7.3.2 C. Bensend <benny@bennyvision.com>
  2004-01-24 21:09 ` Re: "mirroring" a table - PostgreSQL 7.3.2 Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-01-24 21:21   ` C. Bensend <benny@bennyvision.com>
  2004-01-24 22:48     ` Re: "mirroring" a table - PostgreSQL 7.3.2 Reece Hart <reece@in-machina.com>
  0 siblings, 1 reply; 6+ messages in thread

From: C. Bensend @ 2004-01-24 21:21 UTC (permalink / raw)
  To: pgsql-admin


> Possibly you could get the effect you want by making one a child of the
> other.  Read up on inheritance.  There are some side-effects you'd
> probably *not* want, so this isn't a perfect solution, but I can't think
> of anything else.

   Thanks for the quick reply, Tom.  I appreciate that.

   I've looked at this, and it seems to be on the same level as a quick
DROP/CREATE.  Is there no way to "mirror" a table structure in real time?

CREATE TABLE test_bob AS ( SELECT * FROM bob WHERE '1' = '2' );

   .. appears to also do what I want, but I'd like to be able to do this
without DROP/CREATE (just because I'm anal, no other real reason).  :)

Thoughts?

Benny


-- 
"Have you ever tried simply turning off the TV, sitting down with your
children, and hitting them?"                   -- Bender, "Futurama"



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: "mirroring" a table - PostgreSQL 7.3.2
  2004-01-24 19:35 "mirroring" a table - PostgreSQL 7.3.2 C. Bensend <benny@bennyvision.com>
  2004-01-24 21:09 ` Re: "mirroring" a table - PostgreSQL 7.3.2 Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-24 21:21   ` Re: "mirroring" a table - PostgreSQL 7.3.2 C. Bensend <benny@bennyvision.com>
@ 2004-01-24 22:48     ` Reece Hart <reece@in-machina.com>
  2004-01-25 04:14       ` Re: "mirroring" a table - PostgreSQL 7.3.2 C. Bensend <benny@bennyvision.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Reece Hart @ 2004-01-24 22:48 UTC (permalink / raw)
  To: C. Bensend <benny@bennyvision.com>; +Cc: pgsql-admin

On Sat, 2004-01-24 at 13:21, C. Bensend wrote:

>    I've looked at this, and it seems to be on the same level as a quick
> DROP/CREATE.  Is there no way to "mirror" a table structure in real time?



Benny-

If I understand what you want correctly, inheritance seems like a pretty
good option. Changes to the definition of a super table are inherited by
the sub table immediately. Data will be preserved in both tables (except
when you drop a column). This is NOT "on the same level as a quick
DROP/CREATE".

The primary gotcha for you is that selects on super tables implicitly
select from the super and all its children (see "select * from ONLY
table" to prevent this). 

Here's a proposal to get what you want: Create a super table bob_def and
two sub tables bob and bob_test. Changes to the definition of bob_def
(e.g., alter table add column...) will get immediately reflected in both
children. You'll insert data into bob and bob_test; bob_def contains no
rows. Selects on bob and bob_test are independent. In short, the
definitions will always be consistent and the data will be completely
independent. You could periodically truncate bob_test and insert ...
select to mirror the data too.

A lesser option is to have bob_test inherit from bob, then use rules to
enforce the ONLY keyword for select/insert/update on bob. The
implementation of this option is less clear to me and there are a few
likely gotchas.



> CREATE TABLE test_bob AS ( SELECT * FROM bob WHERE '1' = '2' );


I don't understand why this does what you want... it requires explicit
intervention (meaning not "real time") to mirror the table definition.


-Reece

-- 
Reece Hart, http://www.in-machina.com/~reece/, GPG:0x25EC91A0 0xD178AAF9

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: "mirroring" a table - PostgreSQL 7.3.2
  2004-01-24 19:35 "mirroring" a table - PostgreSQL 7.3.2 C. Bensend <benny@bennyvision.com>
  2004-01-24 21:09 ` Re: "mirroring" a table - PostgreSQL 7.3.2 Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-24 21:21   ` Re: "mirroring" a table - PostgreSQL 7.3.2 C. Bensend <benny@bennyvision.com>
  2004-01-24 22:48     ` Re: "mirroring" a table - PostgreSQL 7.3.2 Reece Hart <reece@in-machina.com>
@ 2004-01-25 04:14       ` C. Bensend <benny@bennyvision.com>
  2004-01-26 17:57         ` Re: "mirroring" a table - PostgreSQL 7.3.2 Reece Hart <reece@in-machina.com>
  0 siblings, 1 reply; 6+ messages in thread

From: C. Bensend @ 2004-01-25 04:14 UTC (permalink / raw)
  To: pgsql-admin


> when you drop a column). This is NOT "on the same level as a quick
> DROP/CREATE".

Hi Reece,

   My apologies to Tom - I certainly was not trying to disreguard or
"blow off" his advice.  I think my lack of understanding may have
manifested itself as dismissal.  I'm a systems/network guy, not a DBA
by any stretch of the word, and most of this is a real learning experience
for me.  :/

> Here's a proposal to get what you want: Create a super table bob_def and
> two sub tables bob and bob_test. Changes to the definition of bob_def
> (e.g., alter table add column...) will get immediately reflected in both
> children. You'll insert data into bob and bob_test; bob_def contains no
> rows. Selects on bob and bob_test are independent. In short, the
> definitions will always be consistent and the data will be completely
> independent. You could periodically truncate bob_test and insert ...
> select to mirror the data too.
>
> A lesser option is to have bob_test inherit from bob, then use rules to
> enforce the ONLY keyword for select/insert/update on bob. The
> implementation of this option is less clear to me and there are a few
> likely gotchas.

I am loath to ask for a hand-hold here, but could you explain a little
more about how to do such a thing?

>> CREATE TABLE test_bob AS ( SELECT * FROM bob WHERE '1' = '2' );
>
>
> I don't understand why this does what you want... it requires explicit
> intervention (meaning not "real time") to mirror the table definition.

This is just an example of what I need to do - just duplicate the table
structure exactly, and never mind the data.  I know it isn't the solution.
I just wanted folks to understand what I'd like to do.  :)

Thanks much for your help, Reece, it is greatly appreciated.  :)

Benny


-- 
"Have you ever tried simply turning off the TV, sitting down with your
children, and hitting them?"                   -- Bender, "Futurama"



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: "mirroring" a table - PostgreSQL 7.3.2
  2004-01-24 19:35 "mirroring" a table - PostgreSQL 7.3.2 C. Bensend <benny@bennyvision.com>
  2004-01-24 21:09 ` Re: "mirroring" a table - PostgreSQL 7.3.2 Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-24 21:21   ` Re: "mirroring" a table - PostgreSQL 7.3.2 C. Bensend <benny@bennyvision.com>
  2004-01-24 22:48     ` Re: "mirroring" a table - PostgreSQL 7.3.2 Reece Hart <reece@in-machina.com>
  2004-01-25 04:14       ` Re: "mirroring" a table - PostgreSQL 7.3.2 C. Bensend <benny@bennyvision.com>
@ 2004-01-26 17:57         ` Reece Hart <reece@in-machina.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Reece Hart @ 2004-01-26 17:57 UTC (permalink / raw)
  To: C. Bensend <benny@bennyvision.com>; +Cc: pgsql-admin

On Sat, 2004-01-24 at 20:14, C. Bensend wrote: 

> I am loath to ask for a hand-hold here, but could you explain a little
> more about how to do such a thing?


It's probably easier than you think. Briefly, it goes like this:
1) read
http://www.postgresql.org/docs/current/static/tutorial-inheritance.html
2) try those examples
3) In your case:

create table bob_def (surname text);
create table bob () inherits (bob_def);
create table bob_test () inherits (bob_def);

insert into bob (surname) values ('smith');
insert into bob_test (surname) values ('gazpacho');

\d bob
\d bob_test
alter table bob_def add column age integer;
\d bob
\d bob_test

update bob set age=104 where surname='smith';

select * from bob;



Note:
You'll need to index the subtables separately if that's important to
you.


-Reece


-- 
Reece Hart, http://www.in-machina.com/~reece/, GPG:0x25EC91A0 0xD178AAF9

^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2004-01-26 17:57 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-24 19:35 "mirroring" a table - PostgreSQL 7.3.2 C. Bensend <benny@bennyvision.com>
2004-01-24 21:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-01-24 21:21   ` C. Bensend <benny@bennyvision.com>
2004-01-24 22:48     ` Reece Hart <reece@in-machina.com>
2004-01-25 04:14       ` C. Bensend <benny@bennyvision.com>
2004-01-26 17:57         ` Reece Hart <reece@in-machina.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox