Re: Contributing with code

On 28 December 2017 at 23:05, Peter Eisentraut <
peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:

> On 12/27/17 15:18, Antonio Belloni wrote:
> > I checked the code of pg_tablespace_databases, defined in
> > src/backend/utils/adt/misc.c, and see that it uses a much simpler
> > approach : It just reads the tablespaces directories and return the name
> > of the directories that represents databases OIDs.
> >
> > Although the function works as expected, I can see some issues not
> > addressed in the code :
> >
> > - It does not check for permissions. Any user can execute it;
> > - It does not check if the platform supports symlinks, which can cause
> > an error because the function is trying to follow the links defined in
> > base/pg_tblspc.
> >
> > I could use the same approach and write a function that goes down one
> > more level in the directory structure and find the objects' OIDs inside
> > each database directory, but I don't know if this is the better way to
> > do that.
>
> The information of what object is in what tablespace already exists in
> the system catalogs, so I don't think we need new ways to discover that
> information. What the todo item referred to, I think, was having a way
> to discover that across database boundaries. But I think that problem
> is unsolvable by design, because you can't look into other databases.
> Looking into the file system to discover the OIDs of objects might work,
> but then you can't do anything with those OIDs without having access to
> the respective database to resolve them.
>

Well, it's arguably solveable, if the solution is to allow at least limited
cross-database access to catalog relations.

I don't see any compelling reason we can't do it for catalogs, because:

- Catalog oids are compiled in and the same across all DBs;

- Catalog structure is also compiled in and the same across all DBs, using
Form_ structs and constants usable with fastgetattr

- We already have two relmapper instances (shared and per-db), so we should
be able to instantiate one for the other DB's per-db relmapper and read its
relation mapping to get oid-to-relfilenode mappings for nailed relations.

- Non-shared non-nailed relfilenodes could be discovered by reading the
other DB's pg_class. You couldn't use relcache lookups, but that shouldn't
matter.

The same is true for catalog indexes.

We can safely assume that all type oids, etc, that appear in catalog
relations are the same in our connected DB as the other DB, so it's safe to
use the syscache when reading "foreign" catalog relations.

It's nowhere near as practical to read *user* tables from another DB, since
we'd need a way to switch the active syscache and relcache, among numerous
other things. But all the restrictions we place on catalogs mean I think
cross-db catalog reads should actually be practical, at least as far as
reading the other db's pg_class and relmapper goes. Most of the juicy bits
are hardcoded.

AFAICS it'd be necessary to extend the relmapper interface to let you
instantiate new maps by dboid and pass them explicitly to the various
lookup functions. Relmapper invalidations would be a problem, I don't see
how it'd be practical to handle them, you'd have to be able to register as
interested in another DB's invals somehow. And you'd have no locking. So
you could probably only use it for things like this - mapping relfilenodes
to oids/relnames for other DBs.

Worth the effort? Probably not. But potentially fun.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services