filenode
A filenode
is a unique numeric identifier used to name on-disk files belonging to a relation. It is stored in the relation's pg_class entry in the relfilenode
column as an OID datatype.
While the relfilenode
is the same as the relation's OID when the relation is created, it will change if the relation is rewritten (e.g. following a VACUUM FULL
or TRUNCATE
).
See below for functions to assist mapping filenode IDs to relations and vice-versa.
Functions
PostgreSQL provides the following functions to assist with mapping filenode IDs to relations:
pg_relation_filenode(
relation
regclass
)pg_filenode_relation(
tablespace
oid
,filenode
oid
)
pg_relation_filenode()
returns the filenode number of the specified object. Additionally, pg_relation_filepath()
will provide the full filenode path (relative to PGDATA).
pg_filenode_relation()
returns the OID of the relation associated with the specified tablespace (a value of 0
implies the default tablespace) and filenode.
Additionally, the contrib module oid2name
can also be used to map OIDs and filenodes in a data directory to object names.
relfilenode = 0
If a relation's relfilenode column in pg_class
is 0, this means it is a "mapped" relation whose on-disk filename is determined at a lower level via pg_filenode.map; in practice this means tables in the schema pg_catalog.
Source code
Internally a RelFileNode
struct is used to reference filenodes; see src/include/storage/relfilenode.h:
typedef struct RelFileNode { Oid spcNode; /* tablespace */ Oid dbNode; /* database */ Oid relNode; /* relation */ } RelFileNode;
This enables backends to determine the on-disk location of buffer pages without knowing anything about their underying tables; see e.g. comment in src/include/storage/buf_internals.h.
Examples
postgres=# CREATE TABLE filenode_test (id INT); CREATE TABLE postgres=# SELECT oid, relfilenode FROM pg_class WHERE relname='filenode_test'; oid | relfilenode -------+------------- 16461 | 16461
Here, oid
and relfilenode
are identical. This changes after a full rewrite, e.g. with VACUUM FULL:
postgres=# VACUUM FULL filenode_test; postgres=# SELECT oid, relfilenode FROM pg_class WHERE relname='filenode_test'; oid | relfilenode -------+------------- 16461 | 16464 (1 row)
Functions
Extracting filenode information from the relation:
postgres=# SELECT oid, relfilenode, pg_relation_filenode(relname::regclass), pg_relation_filepath(relname::regclass) FROM pg_class WHERE relname='filenode_test'; oid | relfilenode | pg_relation_filenode | pg_relation_filepath -------+-------------+----------------------+---------------------- 16461 | 16464 | 16464 | base/13285/16464 (1 row)
Extracting relation information from the filenode:
postgres=# SELECT pg_filenode_relation(0, 16464)::oid,
pg_filenode_relation(0, 16464); pg_filenode_relation | pg_filenode_relation ----------------------+---------------------- 16461 | filenode_test (1 row)
Determine if a set of filenode IDs belong to relations in a tablespace in the current database:
postgres=# WITH a(x) AS (VALUES (16452), (16468)) SELECT pg_filenode_relation(0, x)::REGCLASS FROM a;
0
is the default tablespace; replace with a non-default tablespace OID as required.
References
- PostgreSQL documentation: Database Object Location Functions
- PostgreSQL documentation: Database File Layout