By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,178 Members | 971 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,178 IT Pros & Developers. It's quick & easy.

Tables spanning db's in cluster

P: n/a
Is it possible to define a table that spans all db's in a cluster similar to pg_database, pg_users?
Perhaps by altering relfilenode in pg_class of all db's to point to the same shared table...

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
William Harazim wrote:
Is it possible to define a table that spans all db's in a cluster
similar to pg_database, pg_users? Perhaps by altering relfilenode
in pg_class of all db's to point to the same shared table...


Uh, no, I don't think that is possible. You could try setting
pg_class.relisshared, but you would have to move the file over into the
/global directory --- not sure if that would work either, though.

--
Bruce Momjian | http://candle.pha.pa.us
pg***@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #2

P: n/a
Bruce Momjian <pg***@candle.pha.pa.us> writes:
William Harazim wrote:
Is it possible to define a table that spans all db's in a cluster
similar to pg_database, pg_users? Perhaps by altering relfilenode
in pg_class of all db's to point to the same shared table...
Uh, no, I don't think that is possible. You could try setting
pg_class.relisshared, but you would have to move the file over into the
/global directory --- not sure if that would work either, though.


Just for fun, I spent some time experimenting with this. It seems to be
possible but it's definitely in the "not ready for prime time" category.

What seems to work:

Create table in template1. Do a checkpoint (essential if you created
any indexes for the table, because dirty buffers must get flushed before
you move the table). Update pg_class to set relisshared=true for the
table, all of its indexes, and its toast table and toast index if any.
Quit and shut down postmaster. Move the physical files from template1
($PGDATA/base/1) into $PGDATA/global (you'll need to have made note of
their relfilenode numbers so you can do this). Restart postmaster.

The table will now be visible in template1 and all databases
subsequently created from template1. Beware however that you cannot
alter the table schema (eg add/remove columns or indexes or foreign
keys) because there isn't any good way to propagate such changes into
other databases. Only updates to the table contents are sharable by
this mechanism. I suppose you could make schema changes in template1
and then propagate them by deleting/recreating other DBs, but that
doesn't sound real practical. Another problem is that pg_dump won't
understand what you've done and will dump the table as a separate
entity in each database.

I was really expecting to find that this couldn't work at all; I had a
vague recollection that there was some showstopper reason why shared
tables had to be created at initdb time. I didn't run into any such
problem once I'd worked out the above recipe for creation procedure,
though.

If you need to share data, consider whether you shouldn't be using
multiple schemas in one database rather than multiple separate
databases...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.