Re: Which table stored in which file in PGDATA/base/[db-oid]

On 2013-06-01 13:04:55 +0200, Martijn van Oosterhout wrote:
> On Sat, Jun 01, 2013 at 03:27:40PM +0430, Soroosh Sardari wrote:
> > Yes, I have some files which is not in pg_class.relfilenode of any table or
> > index.
> > I want to know which table or index stored in such files.
>
> That shouldn't happen. Are you sure you're looking in the right
> database? Kan you list the filenames?

It's actually entirely normal. For some system tables the actual
relfilenode isn't stored in the system catalog but in the relation
mapper. Those are
a) tables needed to access the catalogs themselves like pg_class,
pg_attribute, pg_proc, ..
b) shared tables where we otherwise couldn't change the relfilenode from
another database

To get the actual relfilenode you actually need to do something like:
SELECT relname, pg_relation_filenode(pg_class.oid) FROM pg_class;

Greetings,

Andres Freund

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