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

On Sat, Jun 1, 2013 at 3:57 PM, Andres Freund <andres(at)2ndquadrant(dot)com>wrote:

> 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
>

Dear Andres

You are right, Some tables are mapped, and some other are global.
The SQL query is really helpful.

Thanks,
Soroosh