Обсуждение: "mirroring" a table - PostgreSQL 7.3.2

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"

"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

> 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"

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

> 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"

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