Robert,
* Robert Haas (robertmhaas@gmail.com) wrote:
> On Sun, Dec 31, 2017 at 1:51 PM, Stephen Frost <sfrost@snowman.net> wrote:
> > The todo entry even talks about why it's difficult to do and what the
> > expected way to go about doing it is (that is, connect to each database
> > that has objects in the tablespace and query it to find out what's in
> > the tablespace). Craig's suggestion is an interesting alternative way
> > though and I'm not sure that it'd be all that bad, but it would be
> > limited to catalog tables.
>
> I think it'd be pretty bad. There's nothing in the system that
> actually guarantees that the system catalog structure matches across
> every database. Of course, if you change structural properties, then
> the system will probably crash, but attstorage and attacl values could
> be different, as could relfilenode, relpages, reltuples,
> relallvisible, relfroxenxid, relminmxid, and relacl. I don't think
> it's wise to let this work on the theory that none of that stuff
> matters. Even if that's true, or can be made true with a crowbar,
> it's a fragile assumption that might turn false in the future.
I'm surprised to hear it described as fragile when I would certainly
expect a huge amount of push-back from the developer community if
someone suggested making template1 have a different catalog structure
from the other databases for some reason. This seems more like an
unwritten rule to me than a 'it happens to work that way' kind of thing.
> > If we'd extend the system to allow transparent usage of postgres_fdw to
> > access other databases which are part of the same cluster, then this
> > could end up being much simpler (eg: select * from
> > otherdatabase.pg_catalog.pg_class ...).
>
> It would probably be better to use background workers for this than
> postgres_fdw to avoid for example making sure you can authenticate,
> but even then this is a pretty significant body of work for what I'd
> consider a fairly marginal benefit.
We could address the authentication issue, I believe, internally such
that users wouldn't have to actually worry about it (perhaps a special
entry in pg_hba.conf and something done through shared memory), which
would definitely be part of the point- what I was trying to get at above
is that we could possibly solve this in a much more general way by
supporting, more-or-less transparently, cross-database queries, which
would be a terribly useful feature, imv.
I agree that it'd be a significant body of work, but we would gain a
great deal more than just the ability to query the catalog of other
databases and that seems quite worthwhile to me.
Thanks!
Stephen