Mapping of oid and relfilenode in PostgreSQL

By Li Chuancheng
China PG Branch certified experts , Senior kernel R & D Engineer of Henkel software
https://zhuanlan.zhihu.com/p/342466054

PostgreSQL The table in will have a RelFileNode Value specifies the file name of the table on disk （ External table 、 Except for partition tables ）. Usually in pg_class Tabular relfilenode Field can find out the value , But there are some specific tables in relfilenode The query result of the field is 0, This blog will explore these special tables relfilenode Kernel processing of .

Normal table Relfilenode

When we create a normal table , stay pg_class It can be found in the system table relfilenode, You can see that when the table was first created, it oid and relfilenode All are 16808, It can also be found on the disk 16808 This file . in fact , This file stores our table t2 Inserted data .

postgres=# create table t2(i int);
CREATE TABLE
postgres=# select oid,relname,relfilenode from pg_class where relname = 't2';
  oid  | relname | relfilenode 
-------+---------+-------------
 16808 | t2      |       16808
(1 row)

postgres=# \q
movead@movead-PC:/h2/pgpgpg/bin$ ll ../data/base/12835/16808 
-rw-------+ 1 movead movead 0 12 month  31 17:11 ../data/base/12835/16808
movead@movead-PC:/h2/pgpgpg/bin$

Before we execute on a table truncate,vacuum full After operation , Will rewrite the data in this table , Will trigger this table relfilenode Change in value . The following test shows that truncate after ,t2 Tabular relfilenode from 16808 Change into 16811.

postgres=# truncate t2;
TRUNCATE TABLE
postgres=# select oid,relname,relfilenode from pg_class where relname = 't2';
  oid  | relname | relfilenode 
-------+---------+-------------
 16808 | t2      |       16811
(1 row)

postgres=# checkpoint;
CHECKPOINT
postgres=# \q
movead@movead-PC:/h2/pgpgpg/bin$ ll ../data/base/12835/16808
ls:  cannot access '../data/base/12835/16808':  There is no file or directory 
movead@movead-PC:/h2/pgpgpg/bin$ ll ../data/base/12835/16811
-rw-------+ 1 movead movead 0 12 month  31 17:16 ../data/base/12835/16811
movead@movead-PC:/h2/pgpgpg/bin$

Nail Tabular Relfilenode

postgres=# select oid, relname, relfilenode,reltablespace
from pg_class
where relfilenode = 0 and relkind = 'r'
order by reltablespace;
 oid  |        relname        | relfilenode | reltablespace 
------+-----------------------+-------------+---------------
 1247 | pg_type               |           0 |             0
 1255 | pg_proc               |           0 |             0
 1249 | pg_attribute          |           0 |             0
 1259 | pg_class              |           0 |             0
 3592 | pg_shseclabel         |           0 |          1664
 1262 | pg_database           |           0 |          1664
 2964 | pg_db_role_setting    |           0 |          1664
 1213 | pg_tablespace         |           0 |          1664
 1261 | pg_auth_members       |           0 |          1664
 1214 | pg_shdepend           |           0 |          1664
 2396 | pg_shdescription      |           0 |          1664
 1260 | pg_authid             |           0 |          1664
 6000 | pg_replication_origin |           0 |          1664
 6100 | pg_subscription       |           0 |          1664
(14 rows)

postgres=#

The above query shows that , from pg_class These tables are found in the system table relfilenode by 0. among pg_type、pg_proc、pg_attribute、pg_class It's a non shared table , In the kernel they are called Nail surface . The rest of the tables are in pg_global Shared tables in a table space .

pg_class In the table relfilenode The meaning of the field is to tell the program , The file name of a table stored on disk . For example, we query t2 Table time , I'll be there first pg_class Get its... From the system table relfilenode, Then go to the disk and find this file , Then turn it on and scan . But if we want to check pg_class The file name of the system table on the disk , Where should I find it relfilenode？ stay PostgreSQL Provides a set of function interfaces for oid and relfilenode The transformation of .

postgres=# select pg_relation_filenode(1259);
 pg_relation_filenode 
----------------------
                16475
(1 row)

postgres=# select pg_filenode_relation(0,16475);
 pg_filenode_relation 
----------------------
 pg_class
(1 row)

postgres=# select pg_filenode_relation(0,16475)::oid;
 pg_filenode_relation 
----------------------
                 1259
(1 row)

postgres=#

adopt pg_relation_filenode() Can be oid Turn into relfilenode,
adopt pg_filenode_relation Can be relfilenode Turn into oid.
since pg_class The table does not store oid and relfilenode Correspondence of , that PostgreSQL How to save this mapping relationship ？

Nail surface Relfilenode The storage mechanism of

After research, we found that , There are... In the data directory pg_filenode.map file , As shown below .

movead@movead-PC:/h2/pgpgpg/data/base/12835$ ll pg_filenode.map 
-rw-------+ 1 movead movead 512 12 month  31 15:10 pg_filenode.map
movead@movead-PC:/h2/pgpgpg/data/base/12835$
movead@movead-PC:/h2/pgpgpg/data/global$ ll pg_filenode.map 
-rw-------+ 1 movead movead 512 12 month  31 15:10 pg_filenode.map
movead@movead-PC:/h2/pgpgpg/data/global$

stay global In the catalog pg_filenode.map It's stored in the file shared Tabular oid and relfilenode The mapping relation of ,12835 It's stored in the directory OID by 12835 In the database nail Tabular oid and relfilenode The mapping relation of .
pg_filenode.map The structure of the file is ：

typedef struct RelMapping
{
    
    Oid         mapoid;         /* OID of a catalog */
    Oid         mapfilenode;    /* its filenode number */
} RelMapping;

typedef struct RelMapFile
{
    
    int32       magic;          /* always RELMAPPER_FILEMAGIC */
    int32       num_mappings;   /* number of valid RelMapping entries */
    RelMapping  mappings[MAX_MAPPINGS];
    pg_crc32c   crc;            /* CRC of all above */
    int32       pad;            /* to make the struct size be 512 exactly */
} RelMapFile;

Conclusion

This blog focuses on PostgreSQL Middle table oid and relfilenode Two different forms of mapping , You just remember to use pg_relation_filenode() Always get the right results , from pg_class Query in system table may get wrong result .

Learn more about PostgreSQL Technical dry cargo 、 Hot papers 、 Industry trends 、 News and information 、 Wonderful activities , Please visit China PostgreSQL Community websites ：www.postgresqlchina.com