Re: Contributing some code

On 28 December 2017 at 01:40, Antonio Belloni <antonio(dot)belloni(at)gmail(dot)com>
wrote:

> Hi,
>
> This is my first post on the list. My name is Antonio. I am a CS grad
> student and my field of study is about databases and information retrieval.
> To get some practical knowledge, I've been studying Postgresql codebase for
> a while.
>
> Now I would like to contribute with some code and I've chosen the
> following topic of the TODO list :
>
> Allow reporting of which objects are in which tablespaces
>
> This item is difficult because a tablespace can contain objects from
> multiple databases. There is a server-side function that returns the
> databases which use a specific tablespace, so this requires a tool that
> will call that function and connect to each database to find the objects in
> each database for that tablespace.
> The topic suggests to use the pg_tablespace_databases to discover which
> database is using a specific tablespace and then connect to each database
> and find the objects in the tablespaces.
> 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.
>
>

There's a bit of a trap hidden here. The names of relation extents look
like oids, possibly with an extent number for relations bigger than 1GB.
But they aren't. They're relfilenode numbers.

PostgreSQL maps relation oids to relfilenodes. By default on a new system,
relations will often have the same relfilenode as oid. That's a pity IMO;
it'd be way less confusing if we allocated relfilenodes from a wholly
different counter, because as it is, it gives people the false impression
they can expect the filename relfilenode to be the relation oid.

In fact, what happens (per my probably imperfect understanding) is that
PostgreSQL checks pg_class (via the relcache) for the oid of the table. It
then uses RelationIsMapped to see if it's a normal relation with the
filenode number in pg_class or not. If it's a normal (non-mapped) relation,
it uses the Relation's rd_node to find the relation's physical address
tablespace, dboid, and relfilenode. If it's a mapped relation, it instead
consults the relmapper to find the relation's storage; see
src/backend/utils/cache/relmapper.c .

See also src/backend/storage/smgr/README,

This means you can't determine relation oids from ondisk state without
scanning pg_class. And pg_class is per-database, not a shared relation, so
you must look at each db in turn, since Pg doesn't support cross-DB
queries. Logical decoding handles this with the RelidByRelfilenode
function, but there are issues there around making sure you have the right
snapshot etc.

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