PostgreSQL OID

前言

在 PostgreSQL 内部，所有的数据库对象都通过相应的对象标识符（Object Identifiers, OID）进行管理，这些标识符是无符号的 4 字节整型。数据库对象与相应 OID 之间的关系存储在相应的系统目录中，依具体的对象类型而异。 例如数据库和堆表对象的 OID 分别存储在 pg_database 和 pg_class 中，如果需要获取 OID 时，可以执行以下查询：

SELECT datname, oid FROM pg_database WHERE datname = '&dbname';

SELECT relname, oid FROM pg_class WHERE relname = '&relname';

SELECT pg_relation_filepath('&tabname');

注意

rename table/database 对表或者 db 进行重命名操作都不会变更 relfilenode

127.0.0.1:54323; wxj@demo > select schemaname, tablename, pg_relation_filepath(tablename::text) from pg_tables where schemaname = 'bookings';
+------------+-----------------+----------------------+
| schemaname |    tablename    | pg_relation_filepath |
+------------+-----------------+----------------------+
| bookings   | ticket_flights  | base/16417/16467     |
| bookings   | boarding_passes | base/16417/16440     |
| bookings   | aircrafts_data  | base/16417/16421     |
| bookings   | flights         | base/16417/16446     |
| bookings   | airports_data   | base/16417/16431     |
| bookings   | seats           | base/16417/16463     |
| bookings   | tickets         | base/16417/16472     |
| bookings   | bookings        | base/16417/16443     |
+------------+-----------------+----------------------+

127.0.0.1:54323; wxj@demo > alter database demo rename to demo01;
ERROR:  current database cannot be renamed
Time: 1.187 ms
127.0.0.1:54323; wxj@demo > \c testdb
You are now connected to database "testdb" as user "wxj".
127.0.0.1:54323; wxj@testdb > alter database demo rename to demo01;
ALTER DATABASE
Time: 2.038 ms
127.0.0.1:54323; wxj@testdb > select schemaname, tablename, pg_relation_filepath(tablename::text) from pg_tables where schemaname = 'bookings';
+------------+-----------+----------------------+
| schemaname | tablename | pg_relation_filepath |
+------------+-----------+----------------------+
+------------+-----------+----------------------+
(0 rows)

Time: 75.493 ms
127.0.0.1:54323; wxj@testdb > \c demo01
You are now connected to database "demo01" as user "wxj".
127.0.0.1:54323; wxj@demo01 > select schemaname, tablename, pg_relation_filepath(tablename::text) from pg_tables where schemaname = 'bookings';
+------------+-----------------+----------------------+
| schemaname |    tablename    | pg_relation_filepath |
+------------+-----------------+----------------------+
| bookings   | ticket_flights  | base/16417/16467     |
| bookings   | boarding_passes | base/16417/16440     |
| bookings   | aircrafts_data  | base/16417/16421     |
| bookings   | flights         | base/16417/16446     |
| bookings   | airports_data   | base/16417/16431     |
| bookings   | seats           | base/16417/16463     |
| bookings   | tickets         | base/16417/16472     |
| bookings   | bookings        | base/16417/16443     |
+------------+-----------------+----------------------+
(8 rows)

Time: 7.055 ms