Re: Tables spanning db's in cluster

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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