1.背景
OID在PostgreSQL中是一个重要的类型，全称是Object identifier。通常被用于各种系统表的主键 。顾名思议，用于对象的标识。当然，这玩意儿，还有各种各样的类型别名。每个别名的名字，都是reg{something}的形式。
通常情况下， oid类型目前被实现为一个无符号4字节整数。 因此，在大型数据库中它并不足以提供数据库范围内的唯一性，甚至在一些大型的表中也无法提供表范围内的唯一性。
如果要对OID进行全面的了解，建议还是读一读原始文档：
http://postgres.cn/docs/14/datatype-oid.html 以及：https://www.postgresql.org/docs/14/datatype-oid.html
2.实例使用
2.1 简单实例
OID能让相关查询更简单。oid类型本身除了比较之外只有很少的操作。不过，它可以被转换成整数，并且接着可以使用标准的整数操作符进行操纵（这样做时要注意有符号和无符号之间可能出现的混乱）。
OID的别名类型除了特定的输入和输出例程之外没有别的操作。这些例程可以接受并显示系统对象的符号名，而不是类型oid使用的原始数字值。别名类型使查找对象的OID值变得简单。例如，要检查与一个表mytable有关的pg_attribute行，你可以写：
SELECT * FROM pg_attribute WHERE attrelid = 'mytable'::regclass;
而不是使用下边的冗长的形式：
SELECT * FROM pg_attribute
WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'mytable');
2.2 OID具体的类型
按命名空间分组的对象的所有OID别名类型都接受模式限定名称，如果在当前搜索路径中找不到未经限定的对象，则将在输出中显示模式限定名称。例如，myschema.mytable是regclass的可接受输入（如果存在这样的表）。该值可能输出为myschema.mytable，或仅为mytable，具体取决于当前搜索路径。regproc和regoper别名类型将只接受唯一（未重载）的输入名称，因此它们的用途有限；对于大多数用途，regprocedure或regoperator 更合适。对于regoperator，通过为未使用的操作数写入NONE来标识一元操作符。
这意味着这些不同类型的对象可以使用各自独立的OID值，也意味着不同类型的OID值实际上是可以重复的。
2.3 oid2name
请注意，我们还有一个实用的客户端命令：oid2name
[03:46:41-postgres@sean-rh1:/var/lib/pgsql]$ oid2name --help
oid2name helps examining the file structure used by PostgreSQL.
Usage:
oid2name [OPTION]...
Options:
-f, --filenode=FILENODE show info for table with given file node
-i, --indexes show indexes and sequences too
-o, --oid=OID show info for table with given OID
-q, --quiet quiet (don't show headers)
-s, --tablespaces show all tablespaces
-S, --system-objects show system objects too
-t, --table=TABLE show info for named table
-V, --version output version information, then exit
-x, --extended extended (show additional columns)
-?, --help show this help, then exit
Connection options:
-d, --dbname=DBNAME database to connect to
-h, --host=HOSTNAME database server host or socket directory
-H same as -h, deprecated option
-p, --port=PORT database server port number
-U, --username=USERNAME connect as specified database user
它的目的是用于帮助检查文件结构，它可以用于实现将常用的oid值到它对应的对象的名字之间的转化。支持的是哪些类型：
索引
表
表空间
系统对象
扩展信息
1、得到 filenode与oid的信息
看看下边的例子：
[03:49:28-postgres@sean-rh1:/var/lib/pgsql]$ oid2name -f 2657 -x
From database "postgres":
Filenode Table Name Oid Schema Tablespace
--------------------------------------------------------------
2657 pg_attrdef_oid_index 2657 pg_catalog pg_default
[03:53:49-postgres@sean-rh1:/var/lib/pgsql]$ oid2name -o 2657 -x
From database "postgres":
Filenode Table Name Oid Schema Tablespace
--------------------------------------------------------------
2657 pg_attrdef_oid_index 2657 pg_catalog pg_default
这里-o 与 -f的结果是一样的。因为文件的relnodeid与oid是相同的。加上-x，会输出相关的扩展列，如schema, tablespace信息。
2、filenodeid与oid默认是一致的
未执行truncate, vacuum full操作时, 两者的值是一致的。
mydb=# \! oid2name -t t
From database "postgres":
Filenode Table Name
----------------------
mydb=# \! oid2name -t t -d mydb
From database "mydb":
Filenode Table Name
----------------------
16496 t
t表原始的filenode是16496. 我们扩展一下：
mydb=# \! oid2name -t t -d mydb -x
From database "mydb":
Filenode Table Name Oid Schema Tablespace
-------------------------------------------------
16496 t 16496 public pg_default
也能看到oid值为16496。指定了database name: mydb是关键。否则找不到。
3、filenode与oid不一致的情形
执行vaccum full
mydb=# vacuum full t;
VACUUM
mydb=# \! oid2name -t t -d mydb -x
From database "mydb":
Filenode Table Name Oid Schema Tablespace
-------------------------------------------------
16509 t 16496 public pg_default
filenode从16496变到16509. Oid保持不变。
执行truncate
mydb=# truncate t;
TRUNCATE TABLE
mydb=# \! oid2name -t t -d mydb -x
From database "mydb":
Filenode Table Name Oid Schema Tablespace
-------------------------------------------------
16513 t 16496 public pg_default
Filenode又从16509变到16513
执行cluster
mydb=# \d t
Table "public.t"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
id | integer | | not null |
col2 | character varying(64) | | |
Indexes:
"t_pkey" PRIMARY KEY, btree (id)
mydb=# cluster t using t_pkey;
CLUSTER
mydb=# \! oid2name -t t -d mydb -x
From database "mydb":
Filenode Table Name Oid Schema Tablespace
-------------------------------------------------
16516 t 16496 public pg_default
执行cluster命令之后，Filenode又从16513变到16516。
这三种操作，相当于重新创建了文件。但是文件对应的对象的原始ID仍然保持不变。
2.4 综合一下
从上一节，你会发现oid2name功能确实很强大。给定表名、Filenode、oid之类的，它统统能给你一股脑子输出。我们看看它能不能与相应的系统表查询对应起来。
1、针对pg_class表
mydb=# select oid, oid::regclass as NAME, relname, relkind from pg_class limit 5;
oid | name | relname | relkind
-------+--------------+--------------+---------
16501 | t1_seq | t1_seq | S
16502 | t1 | t1 | r
16496 | t | t | r
16499 | t_pkey | t_pkey | i
2619 | pg_statistic | pg_statistic | r
(5 rows)
看看上边，使用oid::regclass可以直接转化出相关的“关系”名。可以批量得到。
2、针对pg_collation表
mydb=# select oid, oid::regcollation as _name, collname, collnamespace, collowner from pg_collation limit 5;
oid | _name | collname | collnamespace | collowner
-------+--------------------+-----------+---------------+-----------
100 | "default" | default | 11 | 10
950 | "C" | C | 11 | 10
951 | "POSIX" | POSIX | 11 | 10
12545 | ucs_basic | ucs_basic | 11 | 10
12546 | pg_catalog."aa_DJ" | aa_DJ | 11 | 10
(5 rows)
请注意一下：oid::regcollation这一列: _name
但是同时，我们也发现namespace, collowner需要进一步的查询。
collowner似乎是能转化的：(collowner::regrole)
mydb=# select oid, oid::regcollation as _name, collname, collnamespace, collowner::regrole from pg_collation limit 5;
oid | _name | collname | collnamespace | collowner
-------+--------------------+-----------+---------------+-----------
100 | "default" | default | 11 | postgres
950 | "C" | C | 11 | postgres
951 | "POSIX" | POSIX | 11 | postgres
12545 | ucs_basic | ucs_basic | 11 | postgres
12546 | pg_catalog."aa_DJ" | aa_DJ | 11 | postgres
(5 rows)
3、针对pg_roles表
这个表里头也存储着oid与rolname的对应关系
mydb=# \d pg_roles
View "pg_catalog.pg_roles"
Column | Type | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+---------
rolname | name | | |
rolsuper | boolean | | |
rolinherit | boolean | | |
rolcreaterole | boolean | | |
rolcreatedb | boolean | | |
rolcanlogin | boolean | | |
rolreplication | boolean | | |
rolconnlimit | integer | | |
rolpassword | text | | |
rolvaliduntil | timestamp with time zone | | |
rolbypassrls | boolean | | |
rolconfig | text[] | C | |
oid | oid | | |
mydb=# select oid, oid::regrole as _name, rolname, rolpassword from pg_roles limit 5;
oid | _name | rolname | rolpassword
------+----------------------+----------------------+-------------
6171 | pg_database_owner | pg_database_owner | ********
6181 | pg_read_all_data | pg_read_all_data | ********
6182 | pg_write_all_data | pg_write_all_data | ********
3373 | pg_monitor | pg_monitor | ********
3374 | pg_read_all_settings | pg_read_all_settings | ********
(5 rows)
4、针对pg_namespace表
PostgreSQL命名实际上比较偏学院派。本来这个namespace也蛮好，但偏偏有另外一个schema的东西，两者指的是同一个东西。我们估且就叫命名空间。不会引起歧义。
mydb=# \d pg_namespace
Table "pg_catalog.pg_namespace"
Column | Type | Collation | Nullable | Default
----------+-----------+-----------+----------+---------
oid | oid | | not null |
nspname | name | | not null |
nspowner | oid | | not null |
nspacl | aclitem[] | | |
Indexes:
"pg_namespace_oid_index" PRIMARY KEY, btree (oid)
"pg_namespace_nspname_index" UNIQUE CONSTRAINT, btree (nspname)
mydb=# select oid, oid::regnamespace as _name, nspname, nspowner, nspowner::regrole from pg_namespace;
oid | _name | nspname | nspowner | nspowner
-------+--------------------+--------------------+----------+----------
99 | pg_toast | pg_toast | 10 | postgres
11 | pg_catalog | pg_catalog | 10 | postgres
2200 | public | public | 10 | postgres
14120 | information_schema | information_schema | 10 | postgres
(4 rows)
-- 建一个临时表，增加两个schema (namespace)
mydb=# create temporary table tmp1(id int);
CREATE TABLE
mydb=# select oid, oid::regnamespace as _name, nspname, nspowner, nspowner::regrole from pg_namespace;
oid | _name | nspname | nspowner | nspowner
-------+--------------------+--------------------+----------+----------
99 | pg_toast | pg_toast | 10 | postgres
11 | pg_catalog | pg_catalog | 10 | postgres
2200 | public | public | 10 | postgres
14120 | information_schema | information_schema | 10 | postgres
16520 | pg_temp_3 | pg_temp_3 | 10 | postgres
16521 | pg_toast_temp_3 | pg_toast_temp_3 | 10 | postgres
(6 rows)
我们注意观察，nspowner::regrole一样可以将对应的nspowner OID值转化成具体的role的名字。
5、pg_ts_config
此表用于文本搜索的配置
mydb=# \d pg_ts_config
Table "pg_catalog.pg_ts_config"
Column | Type | Collation | Nullable | Default
--------------+------+-----------+----------+---------
oid | oid | | not null |
cfgname | name | | not null |
cfgnamespace | oid | | not null |
cfgowner | oid | | not null |
cfgparser | oid | | not null |
Indexes:
"pg_ts_config_oid_index" PRIMARY KEY, btree (oid)
"pg_ts_config_cfgname_index" UNIQUE CONSTRAINT, btree (cfgname, cfgnamespace)
查询实例：
mydb=# select * from pg_ts_config limit 7 ;
oid | cfgname | cfgnamespace | cfgowner | cfgparser
-------+------------+--------------+----------+-----------
3748 | simple | 11 | 10 | 3722
14065 | arabic | 11 | 10 | 3722
14067 | armenian | 11 | 10 | 3722
14069 | basque | 11 | 10 | 3722
14071 | catalan | 11 | 10 | 3722
14073 | danish | 11 | 10 | 3722
14075 | dutch | 11 | 10 | 3722
mydb=# select oid, oid::regconfig as _name, cfgname, cfgnamespace::regnamespace, cfgowner::regrole, cfgparser from pg_ts_config limit 7;
oid | _name | cfgname | cfgnamespace | cfgowner | cfgparser
-------+----------+----------+--------------+----------+-----------
3748 | simple | simple | pg_catalog | postgres | 3722
14065 | arabic | arabic | pg_catalog | postgres | 3722
14067 | armenian | armenian | pg_catalog | postgres | 3722
14069 | basque | basque | pg_catalog | postgres | 3722
14071 | catalan | catalan | pg_catalog | postgres | 3722
14073 | danish | danish | pg_catalog | postgres | 3722
14075 | dutch | dutch | pg_catalog | postgres | 3722
(7 rows)
6、pg_ts_dict
用于文本检索中的词典信息
mydb=# \d pg_ts_dict
Table "pg_catalog.pg_ts_dict"
Column | Type | Collation | Nullable | Default
----------------+------+-----------+----------+---------
oid | oid | | not null |
dictname | name | | not null |
dictnamespace | oid | | not null |
dictowner | oid | | not null |
dicttemplate | oid | | not null |
dictinitoption | text | C | |
Indexes:
"pg_ts_dict_oid_index" PRIMARY KEY, btree (oid)
"pg_ts_dict_dictname_index" UNIQUE CONSTRAINT, btree (dictname, dictnamespace)
mydb=# select * from pg_ts_dict limit 5;
oid | dictname | dictnamespace | dictowner | dicttemplate | dictinitoption
-------+---------------+---------------+-----------+--------------+-----------------------
3765 | simple | 11 | 10 | 3727 |
14064 | arabic_stem | 11 | 10 | 14063 | language = 'arabic'
14066 | armenian_stem | 11 | 10 | 14063 | language = 'armenian'
14068 | basque_stem | 11 | 10 | 14063 | language = 'basque'
14070 | catalan_stem | 11 | 10 | 14063 | language = 'catalan'
(5 rows)
mydb=# select oid, oid::regdictionary as _name, dictname, dictnamespace::regnamespace, dictowner::regrole, dicttemplate::regdictionary from pg_ts_dict limit 5;
oid | _name | dictname | dictnamespace | dictowner | dicttemplate
-------+---------------+---------------+---------------+-----------+--------------
3765 | simple | simple | pg_catalog | postgres | 3727
14064 | arabic_stem | arabic_stem | pg_catalog | postgres | 14063
14066 | armenian_stem | armenian_stem | pg_catalog | postgres | 14063
14068 | basque_stem | basque_stem | pg_catalog | postgres | 14063
14070 | catalan_stem | catalan_stem | pg_catalog | postgres | 14063
(5 rows)
7、pg_operator
用于运算操作符. 分带参数类型的regoperator和不带参数类型的regoper.
mydb=# \d pg_operator
Table "pg_catalog.pg_operator"
Column | Type | Collation | Nullable | Default
--------------+---------+-----------+----------+---------
oid | oid | | not null |
oprname | name | | not null |
oprnamespace | oid | | not null |
oprowner | oid | | not null |
oprkind | "char" | | not null |
oprcanmerge | boolean | | not null |
oprcanhash | boolean | | not null |
oprleft | oid | | not null |
oprright | oid | | not null |
oprresult | oid | | not null |
oprcom | oid | | not null |
oprnegate | oid | | not null |
oprcode | regproc | | not null |
oprrest | regproc | | not null |
oprjoin | regproc | | not null |
Indexes:
"pg_operator_oid_index" PRIMARY KEY, btree (oid)
"pg_operator_oprname_l_r_n_index" UNIQUE CONSTRAINT, btree (oprname, oprleft, oprright, oprnamespace)
mydb=# select oid, oprname, oprnamespace, oprowner, oprresult, oprleft, oprright, oprcode from pg_operator limit 5;
oid | oprname | oprnamespace | oprowner | oprresult | oprleft | oprright | oprcode
-----+---------+--------------+----------+-----------+---------+----------+---------
15 | = | 11 | 10 | 16 | 23 | 20 | int48eq
36 | <> | 11 | 10 | 16 | 23 | 20 | int48ne
37 | < | 11 | 10 | 16 | 23 | 20 | int48lt
76 | > | 11 | 10 | 16 | 23 | 20 | int48gt
80 | <= | 11 | 10 | 16 | 23 | 20 | int48le
(5 rows)
mydb=# select oid::regoper as _oper, oid::regoperator as _oper2, oid, oprname, oprnamespace::regnamespace, oprowner::regrole, oprresult::oid::regoper from pg_operator limit 5;
_oper | _oper2 | oid | oprname | oprnamespace | oprowner | oprresult
---------------+--------------------+-----+---------+--------------+----------+-----------
pg_catalog.= | =(integer,bigint) | 15 | = | pg_catalog | postgres | 16
pg_catalog.<> | <>(integer,bigint) | 36 | <> | pg_catalog | postgres | 16
pg_catalog.< | <(integer,bigint) | 37 | < | pg_catalog | postgres | 16
pg_catalog.> | >(integer,bigint) | 76 | > | pg_catalog | postgres | 16
pg_catalog.<= | <=(integer,bigint) | 80 | <= | pg_catalog | postgres | 16
(5 rows)
8、pg_proc
用于存储过程, 带参数和不带参数。带参数为regprocedure，不带参数为regproc。
mydb=# \d pg_proc
Table "pg_catalog.pg_proc"
Column | Type | Collation | Nullable | Default
-----------------+--------------+-----------+----------+---------
oid | oid | | not null |
proname | name | | not null |
pronamespace | oid | | not null |
proowner | oid | | not null |
prolang | oid | | not null |
procost | real | | not null |
prorows | real | | not null |
provariadic | oid | | not null |
prosupport | regproc | | not null |
prokind | "char" | | not null |
prosecdef | boolean | | not null |
proleakproof | boolean | | not null |
proisstrict | boolean | | not null |
proretset | boolean | | not null |
provolatile | "char" | | not null |
proparallel | "char" | | not null |
pronargs | smallint | | not null |
pronargdefaults | smallint | | not null |
prorettype | oid | | not null |
proargtypes | oidvector | | not null |
proallargtypes | oid[] | | |
proargmodes | "char"[] | | |
proargnames | text[] | C | |
proargdefaults | pg_node_tree | C | |
protrftypes | oid[] | | |
prosrc | text | C | not null |
probin | text | C | |
prosqlbody | pg_node_tree | C | |
proconfig | text[] | C | |
proacl | aclitem[] | | |
Indexes:
"pg_proc_oid_index" PRIMARY KEY, btree (oid)
"pg_proc_proname_args_nsp_index" UNIQUE CONSTRAINT, btree (proname, proargtypes, pronamespace)
示例：
mydb=# select oid, oid::regprocedure as _procedure, oid::regproc as _proc, proname, pronamespace::regnamespace, proowner::regrole,prosupport from pg_proc limit 5;
oid | _procedure | _proc | proname | pronamespace | proowner | prosupport
------+------------------+----------+----------+--------------+----------+------------
1242 | boolin(cstring) | boolin | boolin | pg_catalog | postgres | -
1243 | boolout(boolean) | boolout | boolout | pg_catalog | postgres | -
1244 | byteain(cstring) | byteain | byteain | pg_catalog | postgres | -
31 | byteaout(bytea) | byteaout | byteaout | pg_catalog | postgres | -
1245 | charin(cstring) | charin | charin | pg_catalog | postgres | -
(5 rows)
9、pg_authid
此表用于：数据库中的角色名. 对应的oid名为regrole。
mydb=# \d pg_authid
Table "pg_catalog.pg_authid"
Column | Type | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+---------
oid | oid | | not null |
rolname | name | | not null |
rolsuper | boolean | | not null |
rolinherit | boolean | | not null |
rolcreaterole | boolean | | not null |
rolcreatedb | boolean | | not null |
rolcanlogin | boolean | | not null |
rolreplication | boolean | | not null |
rolbypassrls | boolean | | not null |
rolconnlimit | integer | | not null |
rolpassword | text | C | |
rolvaliduntil | timestamp with time zone | | |
Indexes:
"pg_authid_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"
"pg_authid_rolname_index" UNIQUE CONSTRAINT, btree (rolname), tablespace "pg_global"
Tablespace: "pg_global"
注意看下，它位于pg_global表空间当中。
示例：
mydb=# select oid, oid::regrole as role_name, rolname, rolsuper, rolcreatedb,rolreplication from pg_authid;
oid | role_name | rolname | rolsuper | rolcreatedb | rolreplication
-------+---------------------------+---------------------------+----------+-------------+----------------
6171 | pg_database_owner | pg_database_owner | f | f | f
6181 | pg_read_all_data | pg_read_all_data | f | f | f
6182 | pg_write_all_data | pg_write_all_data | f | f | f
3373 | pg_monitor | pg_monitor | f | f | f
3374 | pg_read_all_settings | pg_read_all_settings | f | f | f
3375 | pg_read_all_stats | pg_read_all_stats | f | f | f
3377 | pg_stat_scan_tables | pg_stat_scan_tables | f | f | f
4569 | pg_read_server_files | pg_read_server_files | f | f | f
4570 | pg_write_server_files | pg_write_server_files | f | f | f
4571 | pg_execute_server_program | pg_execute_server_program | f | f | f
4200 | pg_signal_backend | pg_signal_backend | f | f | f
10 | postgres | postgres | t | t | t
16385 | mydb | mydb | f | f | f
(13 rows)
看看上边的结果，非常清晰。
10、pg_type
此表用于系统中用到的类型。对应OID的名为regtype
mydb=# \d pg_type
Table "pg_catalog.pg_type"
Column | Type | Collation | Nullable | Default
----------------+--------------+-----------+----------+---------
oid | oid | | not null |
typname | name | | not null |
typnamespace | oid | | not null |
typowner | oid | | not null |
typlen | smallint | | not null |
typbyval | boolean | | not null |
typtype | "char" | | not null |
typcategory | "char" | | not null |
typispreferred | boolean | | not null |
typisdefined | boolean | | not null |
typdelim | "char" | | not null |
typrelid | oid | | not null |
typsubscript | regproc | | not null |
typelem | oid | | not null |
typarray | oid | | not null |
typinput | regproc | | not null |
typoutput | regproc | | not null |
typreceive | regproc | | not null |
typsend | regproc | | not null |
typmodin | regproc | | not null |
typmodout | regproc | | not null |
typanalyze | regproc | | not null |
typalign | "char" | | not null |
typstorage | "char" | | not null |
typnotnull | boolean | | not null |
typbasetype | oid | | not null |
typtypmod | integer | | not null |
typndims | integer | | not null |
typcollation | oid | | not null |
typdefaultbin | pg_node_tree | C | |
typdefault | text | C | |
typacl | aclitem[] | | |
Indexes:
"pg_type_oid_index" PRIMARY KEY, btree (oid)
"pg_type_typname_nsp_index" UNIQUE CONSTRAINT, btree (typname, typnamespace)
示例：
mydb=# select oid, oid::regtype as _type, typname, typnamespace::regnamespace,typowner::regrole, typelem::regtype,typarray::regtype,typinput, typcollation::regcollation from pg_type limit 5;
oid | _type | typname | typnamespace | typowner | typelem | typarray | typinput | typcollation
-----+---------+---------+--------------+----------+---------+-----------+----------+--------------
16 | boolean | bool | pg_catalog | postgres | - | boolean[] | boolin | -
17 | bytea | bytea | pg_catalog | postgres | - | bytea[] | byteain | -
18 | "char" | char | pg_catalog | postgres | - | "char"[] | charin | -
19 | name | name | pg_catalog | postgres | "char" | name[] | namein | "C"
20 | bigint | int8 | pg_catalog | postgres | - | bigint[] | int8in | -
(5 rows)
2.5 全局对象怎么处理？
从上边的10组情况来看，我们会发现，pg_authid使用的是全局表空间。对于pg_database, pg_tablespace中，其实还是有一些对象的。它们的相关信息如何得到？
1、pg_database
mydb=# \d pg_database
Table "pg_catalog.pg_database"
Column | Type | Collation | Nullable | Default
---------------+-----------+-----------+----------+---------
oid | oid | | not null |
datname | name | | not null |
datdba | oid | | not null |
encoding | integer | | not null |
datcollate | name | | not null |
datctype | name | | not null |
datistemplate | boolean | | not null |
datallowconn | boolean | | not null |
datconnlimit | integer | | not null |
datlastsysoid | oid | | not null |
datfrozenxid | xid | | not null |
datminmxid | xid | | not null |
dattablespace | oid | | not null |
datacl | aclitem[] | | |
Indexes:
"pg_database_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"
"pg_database_datname_index" UNIQUE CONSTRAINT, btree (datname), tablespace "pg_global"
Tablespace: "pg_global"
2、pg_tablespace
mydb=# \d pg_tablespace
Table "pg_catalog.pg_tablespace"
Column | Type | Collation | Nullable | Default
------------+-----------+-----------+----------+---------
oid | oid | | not null |
spcname | name | | not null |
spcowner | oid | | not null |
spcacl | aclitem[] | | |
spcoptions | text[] | C | |
Indexes:
"pg_tablespace_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"
"pg_tablespace_spcname_index" UNIQUE CONSTRAINT, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"
这两个对象，没有对应的oid名。所以， 目前只能通过查询表或者用oid2name来得到。
mydb=# \! oid2name
All databases:
Oid Database Name Tablespace
----------------------------------
16384 mydb pg_default
14486 postgres pg_default
14485 template0 pg_default
1 template1 pg_default
2.6 系统表或视图的OID值
因为系统表在一建库的时候，就已经确定下来了，所以，它们通常都是固定值。而且那251行的值也是一样的。
mydb=# select oid, oid::regclass as _name, relname from pg_class where relname ~ 'pg_' and oid::regclass::varchar !~ '\.' order by oid;
-- 表视图及各种“关系”对象
mydb2=# select oid, oid::regclass as _name, relname, relkind from pg_class where relname ~ 'pg_' and oid::regclass::varchar !~ '\.' order by oid;
oid | _name | relname | relkind
-------+-----------------------------------------------+-----------------------------------------------+---------
112 | pg_foreign_data_wrapper_oid_index | pg_foreign_data_wrapper_oid_index | i
113 | pg_foreign_server_oid_index | pg_foreign_server_oid_index | i
174 | pg_user_mapping_oid_index | pg_user_mapping_oid_index | i
175 | pg_user_mapping_user_server_index | pg_user_mapping_user_server_index | i
548 | pg_foreign_data_wrapper_name_index | pg_foreign_data_wrapper_name_index | i
549 | pg_foreign_server_name_index | pg_foreign_server_name_index | i
826 | pg_default_acl | pg_default_acl | r
827 | pg_default_acl_role_nsp_obj_index | pg_default_acl_role_nsp_obj_index | i
828 | pg_default_acl_oid_index | pg_default_acl_oid_index | i
1213 | pg_tablespace | pg_tablespace | r
1214 | pg_shdepend | pg_shdepend | r
1232 | pg_shdepend_depender_index | pg_shdepend_depender_index | i
1233 | pg_shdepend_reference_index | pg_shdepend_reference_index | i
1247 | pg_type | pg_type | r
1249 | pg_attribute | pg_attribute | r
1255 | pg_proc | pg_proc | r
1259 | pg_class | pg_class | r
1260 | pg_authid | pg_authid | r
1261 | pg_auth_members | pg_auth_members | r
1262 | pg_database | pg_database | r
1417 | pg_foreign_server | pg_foreign_server | r
1418 | pg_user_mapping | pg_user_mapping | r
2187 | pg_inherits_parent_index | pg_inherits_parent_index | i
2224 | pg_sequence | pg_sequence | r
2228 | pg_range_rngmultitypid_index | pg_range_rngmultitypid_index | i
2328 | pg_foreign_data_wrapper | pg_foreign_data_wrapper | r
2396 | pg_shdescription | pg_shdescription | r
2397 | pg_shdescription_o_c_index | pg_shdescription_o_c_index | i
2579 | pg_constraint_conparentid_index | pg_constraint_conparentid_index | i
2600 | pg_aggregate | pg_aggregate | r
2601 | pg_am | pg_am | r
2602 | pg_amop | pg_amop | r
2603 | pg_amproc | pg_amproc | r
2604 | pg_attrdef | pg_attrdef | r
2605 | pg_cast | pg_cast | r
2606 | pg_constraint | pg_constraint | r
2607 | pg_conversion | pg_conversion | r
2608 | pg_depend | pg_depend | r
2609 | pg_description | pg_description | r
2610 | pg_index | pg_index | r
2611 | pg_inherits | pg_inherits | r
2612 | pg_language | pg_language | r
2613 | pg_largeobject | pg_largeobject | r
2615 | pg_namespace | pg_namespace | r
2616 | pg_opclass | pg_opclass | r
2617 | pg_operator | pg_operator | r
2618 | pg_rewrite | pg_rewrite | r
2619 | pg_statistic | pg_statistic | r
2620 | pg_trigger | pg_trigger | r
2650 | pg_aggregate_fnoid_index | pg_aggregate_fnoid_index | i
2651 | pg_am_name_index | pg_am_name_index | i
2652 | pg_am_oid_index | pg_am_oid_index | i
2653 | pg_amop_fam_strat_index | pg_amop_fam_strat_index | i
2654 | pg_amop_opr_fam_index | pg_amop_opr_fam_index | i
2655 | pg_amproc_fam_proc_index | pg_amproc_fam_proc_index | i
2656 | pg_attrdef_adrelid_adnum_index | pg_attrdef_adrelid_adnum_index | i
2657 | pg_attrdef_oid_index | pg_attrdef_oid_index | i
2658 | pg_attribute_relid_attnam_index | pg_attribute_relid_attnam_index | i
2659 | pg_attribute_relid_attnum_index | pg_attribute_relid_attnum_index | i
2660 | pg_cast_oid_index | pg_cast_oid_index | i
2661 | pg_cast_source_target_index | pg_cast_source_target_index | i
2662 | pg_class_oid_index | pg_class_oid_index | i
2663 | pg_class_relname_nsp_index | pg_class_relname_nsp_index | i
2664 | pg_constraint_conname_nsp_index | pg_constraint_conname_nsp_index | i
2665 | pg_constraint_conrelid_contypid_conname_index | pg_constraint_conrelid_contypid_conname_index | i
2666 | pg_constraint_contypid_index | pg_constraint_contypid_index | i
2667 | pg_constraint_oid_index | pg_constraint_oid_index | i
2668 | pg_conversion_default_index | pg_conversion_default_index | i
2669 | pg_conversion_name_nsp_index | pg_conversion_name_nsp_index | i
2670 | pg_conversion_oid_index | pg_conversion_oid_index | i
2671 | pg_database_datname_index | pg_database_datname_index | i
2672 | pg_database_oid_index | pg_database_oid_index | i
2673 | pg_depend_depender_index | pg_depend_depender_index | i
2674 | pg_depend_reference_index | pg_depend_reference_index | i
2675 | pg_description_o_c_o_index | pg_description_o_c_o_index | i
2676 | pg_authid_rolname_index | pg_authid_rolname_index | i
2677 | pg_authid_oid_index | pg_authid_oid_index | i
2678 | pg_index_indrelid_index | pg_index_indrelid_index | i
2679 | pg_index_indexrelid_index | pg_index_indexrelid_index | i
2680 | pg_inherits_relid_seqno_index | pg_inherits_relid_seqno_index | i
2681 | pg_language_name_index | pg_language_name_index | i
2682 | pg_language_oid_index | pg_language_oid_index | i
2683 | pg_largeobject_loid_pn_index | pg_largeobject_loid_pn_index | i
2684 | pg_namespace_nspname_index | pg_namespace_nspname_index | i
2685 | pg_namespace_oid_index | pg_namespace_oid_index | i
2686 | pg_opclass_am_name_nsp_index | pg_opclass_am_name_nsp_index | i
2687 | pg_opclass_oid_index | pg_opclass_oid_index | i
2688 | pg_operator_oid_index | pg_operator_oid_index | i
2689 | pg_operator_oprname_l_r_n_index | pg_operator_oprname_l_r_n_index | i
2690 | pg_proc_oid_index | pg_proc_oid_index | i
2691 | pg_proc_proname_args_nsp_index | pg_proc_proname_args_nsp_index | i
2692 | pg_rewrite_oid_index | pg_rewrite_oid_index | i
2693 | pg_rewrite_rel_rulename_index | pg_rewrite_rel_rulename_index | i
2694 | pg_auth_members_role_member_index | pg_auth_members_role_member_index | i
2695 | pg_auth_members_member_role_index | pg_auth_members_member_role_index | i
2696 | pg_statistic_relid_att_inh_index | pg_statistic_relid_att_inh_index | i
2697 | pg_tablespace_oid_index | pg_tablespace_oid_index | i
2698 | pg_tablespace_spcname_index | pg_tablespace_spcname_index | i
2699 | pg_trigger_tgconstraint_index | pg_trigger_tgconstraint_index | i
2701 | pg_trigger_tgrelid_tgname_index | pg_trigger_tgrelid_tgname_index | i
2702 | pg_trigger_oid_index | pg_trigger_oid_index | i
2703 | pg_type_oid_index | pg_type_oid_index | i
2704 | pg_type_typname_nsp_index | pg_type_typname_nsp_index | i
2753 | pg_opfamily | pg_opfamily | r
2754 | pg_opfamily_am_name_nsp_index | pg_opfamily_am_name_nsp_index | i
2755 | pg_opfamily_oid_index | pg_opfamily_oid_index | i
2756 | pg_amop_oid_index | pg_amop_oid_index | i
2757 | pg_amproc_oid_index | pg_amproc_oid_index | i
2964 | pg_db_role_setting | pg_db_role_setting | r
2965 | pg_db_role_setting_databaseid_rol_index | pg_db_role_setting_databaseid_rol_index | i
2995 | pg_largeobject_metadata | pg_largeobject_metadata | r
2996 | pg_largeobject_metadata_oid_index | pg_largeobject_metadata_oid_index | i
3079 | pg_extension | pg_extension | r
3080 | pg_extension_oid_index | pg_extension_oid_index | i
3081 | pg_extension_name_index | pg_extension_name_index | i
3085 | pg_collation_oid_index | pg_collation_oid_index | i
3118 | pg_foreign_table | pg_foreign_table | r
3119 | pg_foreign_table_relid_index | pg_foreign_table_relid_index | i
3164 | pg_collation_name_enc_nsp_index | pg_collation_name_enc_nsp_index | i
3256 | pg_policy | pg_policy | r
3257 | pg_policy_oid_index | pg_policy_oid_index | i
3258 | pg_policy_polrelid_polname_index | pg_policy_polrelid_polname_index | i
3350 | pg_partitioned_table | pg_partitioned_table | r
3351 | pg_partitioned_table_partrelid_index | pg_partitioned_table_partrelid_index | i
3379 | pg_statistic_ext_relid_index | pg_statistic_ext_relid_index | i
3380 | pg_statistic_ext_oid_index | pg_statistic_ext_oid_index | i
3381 | pg_statistic_ext | pg_statistic_ext | r
3394 | pg_init_privs | pg_init_privs | r
3395 | pg_init_privs_o_c_o_index | pg_init_privs_o_c_o_index | i
3429 | pg_statistic_ext_data | pg_statistic_ext_data | r
3433 | pg_statistic_ext_data_stxoid_index | pg_statistic_ext_data_stxoid_index | i
3455 | pg_class_tblspc_relfilenode_index | pg_class_tblspc_relfilenode_index | i
3456 | pg_collation | pg_collation | r
3466 | pg_event_trigger | pg_event_trigger | r
3467 | pg_event_trigger_evtname_index | pg_event_trigger_evtname_index | i
3468 | pg_event_trigger_oid_index | pg_event_trigger_oid_index | i
3501 | pg_enum | pg_enum | r
3502 | pg_enum_oid_index | pg_enum_oid_index | i
3503 | pg_enum_typid_label_index | pg_enum_typid_label_index | i
3534 | pg_enum_typid_sortorder_index | pg_enum_typid_sortorder_index | i
3541 | pg_range | pg_range | r
3542 | pg_range_rngtypid_index | pg_range_rngtypid_index | i
3574 | pg_transform_oid_index | pg_transform_oid_index | i
3575 | pg_transform_type_lang_index | pg_transform_type_lang_index | i
3576 | pg_transform | pg_transform | r
3592 | pg_shseclabel | pg_shseclabel | r
3593 | pg_shseclabel_object_index | pg_shseclabel_object_index | i
3596 | pg_seclabel | pg_seclabel | r
3597 | pg_seclabel_object_index | pg_seclabel_object_index | i
3600 | pg_ts_dict | pg_ts_dict | r
3601 | pg_ts_parser | pg_ts_parser | r
3602 | pg_ts_config | pg_ts_config | r
3603 | pg_ts_config_map | pg_ts_config_map | r
3604 | pg_ts_dict_dictname_index | pg_ts_dict_dictname_index | i
3605 | pg_ts_dict_oid_index | pg_ts_dict_oid_index | i
3606 | pg_ts_parser_prsname_index | pg_ts_parser_prsname_index | i
3607 | pg_ts_parser_oid_index | pg_ts_parser_oid_index | i
3608 | pg_ts_config_cfgname_index | pg_ts_config_cfgname_index | i
3609 | pg_ts_config_map_index | pg_ts_config_map_index | i
3712 | pg_ts_config_oid_index | pg_ts_config_oid_index | i
3764 | pg_ts_template | pg_ts_template | r
3766 | pg_ts_template_tmplname_index | pg_ts_template_tmplname_index | i
3767 | pg_ts_template_oid_index | pg_ts_template_oid_index | i
3997 | pg_statistic_ext_name_index | pg_statistic_ext_name_index | i
5002 | pg_sequence_seqrelid_index | pg_sequence_seqrelid_index | i
6000 | pg_replication_origin | pg_replication_origin | r
6001 | pg_replication_origin_roiident_index | pg_replication_origin_roiident_index | i
6002 | pg_replication_origin_roname_index | pg_replication_origin_roname_index | i
6100 | pg_subscription | pg_subscription | r
6102 | pg_subscription_rel | pg_subscription_rel | r
6104 | pg_publication | pg_publication | r
6106 | pg_publication_rel | pg_publication_rel | r
6110 | pg_publication_oid_index | pg_publication_oid_index | i
6111 | pg_publication_pubname_index | pg_publication_pubname_index | i
6112 | pg_publication_rel_oid_index | pg_publication_rel_oid_index | i
6113 | pg_publication_rel_prrelid_prpubid_index | pg_publication_rel_prrelid_prpubid_index | i
6114 | pg_subscription_oid_index | pg_subscription_oid_index | i
6115 | pg_subscription_subname_index | pg_subscription_subname_index | i
6117 | pg_subscription_rel_srrelid_srsubid_index | pg_subscription_rel_srrelid_srsubid_index | i
12217 | pg_roles | pg_roles | v
12222 | pg_shadow | pg_shadow | v
12227 | pg_group | pg_group | v
12231 | pg_user | pg_user | v
12235 | pg_policies | pg_policies | v
12240 | pg_rules | pg_rules | v
12245 | pg_views | pg_views | v
12250 | pg_tables | pg_tables | v
12255 | pg_matviews | pg_matviews | v
12260 | pg_indexes | pg_indexes | v
12265 | pg_sequences | pg_sequences | v
12270 | pg_stats | pg_stats | v
12275 | pg_stats_ext | pg_stats_ext | v
12280 | pg_stats_ext_exprs | pg_stats_ext_exprs | v
12285 | pg_publication_tables | pg_publication_tables | v
12290 | pg_locks | pg_locks | v
12294 | pg_cursors | pg_cursors | v
12298 | pg_available_extensions | pg_available_extensions | v
12302 | pg_available_extension_versions | pg_available_extension_versions | v
12307 | pg_prepared_xacts | pg_prepared_xacts | v
12312 | pg_prepared_statements | pg_prepared_statements | v
12316 | pg_seclabels | pg_seclabels | v
12321 | pg_settings | pg_settings | v
12327 | pg_file_settings | pg_file_settings | v
12331 | pg_hba_file_rules | pg_hba_file_rules | v
12335 | pg_timezone_abbrevs | pg_timezone_abbrevs | v
12339 | pg_timezone_names | pg_timezone_names | v
12343 | pg_config | pg_config | v
12347 | pg_shmem_allocations | pg_shmem_allocations | v
12351 | pg_backend_memory_contexts | pg_backend_memory_contexts | v
12355 | pg_stat_all_tables | pg_stat_all_tables | v
12360 | pg_stat_xact_all_tables | pg_stat_xact_all_tables | v
12365 | pg_stat_sys_tables | pg_stat_sys_tables | v
12370 | pg_stat_xact_sys_tables | pg_stat_xact_sys_tables | v
12374 | pg_stat_user_tables | pg_stat_user_tables | v
12379 | pg_stat_xact_user_tables | pg_stat_xact_user_tables | v
12383 | pg_statio_all_tables | pg_statio_all_tables | v
12388 | pg_statio_sys_tables | pg_statio_sys_tables | v
12392 | pg_statio_user_tables | pg_statio_user_tables | v
12396 | pg_stat_all_indexes | pg_stat_all_indexes | v
12401 | pg_stat_sys_indexes | pg_stat_sys_indexes | v
12405 | pg_stat_user_indexes | pg_stat_user_indexes | v
12409 | pg_statio_all_indexes | pg_statio_all_indexes | v
12414 | pg_statio_sys_indexes | pg_statio_sys_indexes | v
12418 | pg_statio_user_indexes | pg_statio_user_indexes | v
12422 | pg_statio_all_sequences | pg_statio_all_sequences | v
12427 | pg_statio_sys_sequences | pg_statio_sys_sequences | v
12431 | pg_statio_user_sequences | pg_statio_user_sequences | v
12435 | pg_stat_activity | pg_stat_activity | v
12440 | pg_stat_replication | pg_stat_replication | v
12445 | pg_stat_slru | pg_stat_slru | v
12449 | pg_stat_wal_receiver | pg_stat_wal_receiver | v
12453 | pg_stat_subscription | pg_stat_subscription | v
12458 | pg_stat_ssl | pg_stat_ssl | v
12462 | pg_stat_gssapi | pg_stat_gssapi | v
12466 | pg_replication_slots | pg_replication_slots | v
12471 | pg_stat_replication_slots | pg_stat_replication_slots | v
12475 | pg_stat_database | pg_stat_database | v
12480 | pg_stat_database_conflicts | pg_stat_database_conflicts | v
12484 | pg_stat_user_functions | pg_stat_user_functions | v
12489 | pg_stat_xact_user_functions | pg_stat_xact_user_functions | v
12494 | pg_stat_archiver | pg_stat_archiver | v
12498 | pg_stat_bgwriter | pg_stat_bgwriter | v
12502 | pg_stat_wal | pg_stat_wal | v
12506 | pg_stat_progress_analyze | pg_stat_progress_analyze | v
12511 | pg_stat_progress_vacuum | pg_stat_progress_vacuum | v
12516 | pg_stat_progress_cluster | pg_stat_progress_cluster | v
12521 | pg_stat_progress_create_index | pg_stat_progress_create_index | v
12526 | pg_stat_progress_basebackup | pg_stat_progress_basebackup | v
12531 | pg_stat_progress_copy | pg_stat_progress_copy | v
12536 | pg_user_mappings | pg_user_mappings | v
12541 | pg_replication_origin_status | pg_replication_origin_status | v
(251 rows)
62行的纯粹的表对象:
mydb2=# select oid, oid::regclass as _name, relname, relkind from pg_class where relname ~ 'pg_' and oid::regclass::varchar !~ '\.' and relkind = 'r' order by oid;
oid | _name | relname | relkind
------+-------------------------+-------------------------+---------
826 | pg_default_acl | pg_default_acl | r
1213 | pg_tablespace | pg_tablespace | r
1214 | pg_shdepend | pg_shdepend | r
1247 | pg_type | pg_type | r
1249 | pg_attribute | pg_attribute | r
1255 | pg_proc | pg_proc | r
1259 | pg_class | pg_class | r
1260 | pg_authid | pg_authid | r
1261 | pg_auth_members | pg_auth_members | r
1262 | pg_database | pg_database | r
1417 | pg_foreign_server | pg_foreign_server | r
1418 | pg_user_mapping | pg_user_mapping | r
2224 | pg_sequence | pg_sequence | r
2328 | pg_foreign_data_wrapper | pg_foreign_data_wrapper | r
2396 | pg_shdescription | pg_shdescription | r
2600 | pg_aggregate | pg_aggregate | r
2601 | pg_am | pg_am | r
2602 | pg_amop | pg_amop | r
2603 | pg_amproc | pg_amproc | r
2604 | pg_attrdef | pg_attrdef | r
2605 | pg_cast | pg_cast | r
2606 | pg_constraint | pg_constraint | r
2607 | pg_conversion | pg_conversion | r
2608 | pg_depend | pg_depend | r
2609 | pg_description | pg_description | r
2610 | pg_index | pg_index | r
2611 | pg_inherits | pg_inherits | r
2612 | pg_language | pg_language | r
2613 | pg_largeobject | pg_largeobject | r
2615 | pg_namespace | pg_namespace | r
2616 | pg_opclass | pg_opclass | r
2617 | pg_operator | pg_operator | r
2618 | pg_rewrite | pg_rewrite | r
2619 | pg_statistic | pg_statistic | r
2620 | pg_trigger | pg_trigger | r
2753 | pg_opfamily | pg_opfamily | r
2964 | pg_db_role_setting | pg_db_role_setting | r
2995 | pg_largeobject_metadata | pg_largeobject_metadata | r
3079 | pg_extension | pg_extension | r
3118 | pg_foreign_table | pg_foreign_table | r
3256 | pg_policy | pg_policy | r
3350 | pg_partitioned_table | pg_partitioned_table | r
3381 | pg_statistic_ext | pg_statistic_ext | r
3394 | pg_init_privs | pg_init_privs | r
3429 | pg_statistic_ext_data | pg_statistic_ext_data | r
3456 | pg_collation | pg_collation | r
3466 | pg_event_trigger | pg_event_trigger | r
3501 | pg_enum | pg_enum | r
3541 | pg_range | pg_range | r
3576 | pg_transform | pg_transform | r
3592 | pg_shseclabel | pg_shseclabel | r
3596 | pg_seclabel | pg_seclabel | r
3600 | pg_ts_dict | pg_ts_dict | r
3601 | pg_ts_parser | pg_ts_parser | r
3602 | pg_ts_config | pg_ts_config | r
3603 | pg_ts_config_map | pg_ts_config_map | r
3764 | pg_ts_template | pg_ts_template | r
6000 | pg_replication_origin | pg_replication_origin | r
6100 | pg_subscription | pg_subscription | r
6102 | pg_subscription_rel | pg_subscription_rel | r
6104 | pg_publication | pg_publication | r
6106 | pg_publication_rel | pg_publication_rel | r
(62 rows)
3.总结
我们可以通过oid2name得到系统大体的database, tablespace的oid值。针对单个oid的值，如果知道它是什么样的对象，oid2name也能方便的得到该对象的基本信息。
系统表的oid基本上是不会变的，建库的时候就已经确定下来了。
通过oid各种别名可以实时的进行转换，将oid值直接转化成相应类型的对象名。在大概11种oid的别名类型转换。可以直接在SQL当中进行转化，而不需要去对应的pg_*表里头查询对应的名字。
而关于全局表空间里头到底存了哪些东西，可以参考以前文章：深入PostgreSQL中的pg_global表空间。
