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