"mirroring" a table - PostgreSQL 7.3.2

[C. Bensend]

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

[Tom Lane]

Post by C. Bensend
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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[C. Bensend]

Post by Tom Lane
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

[Reece Hart]

Post by C. Bensend
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.

Post by C. Bensend
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

[C. Bensend]

Post by Reece Hart
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. :/

Post by Reece Hart
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?

Post by Reece Hart

Post by C. Bensend
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

[Reece Hart]

Post by C. Bensend
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