OID介绍
在PostgreSQL中，对象标识符Object identifiers (OIDs) 用来在整个数据集簇中唯一的标识一个数据库对象，这个对象可以是数据库、表、索引、视图、元组、类型等等。
同时OID也是系统内部的一个数据类型，用4个字节的无符号整数表示。
OID的分配由系统中的一个全局OID计数器来实现，OID分配时会采用互斥锁加以锁定以避免多个要求分配OID的请求获得相同的OID。
官方介绍 https://www.postgresql.org/docs/12/datatype-oid.html#DATATYPE-OID-TABLE
- Object identifiers (OIDs) are used internally by PostgreSQL as primary keys for various system tables. OID通常被用于系统表的主键，进行系统表直接的链接。
- The oid type is currently implemented as an unsigned four-byte integer. Therefore, it is not large enough to provide database-wide uniqueness in large databases, or even in large individual tables. OID是由4字节无符号整形存储，并且不能保证数据库级的全局唯一性，甚至是数据量的大的表中元组的唯一性。
验证OID的数据集簇级别的全局唯一性
查看pg_class中表的OID，发现每个库的相同系统表的OID一样，这是由于他们都是从模板库template1拷贝而来才导致一样。
postgres=# select oid,relname from pg_class order by oid;
oid | relname
-------+-----------------------------------------
112 | pg_foreign_data_wrapper_oid_index
113 | pg_foreign_server_oid_index
174 | pg_user_mapping_oid_index
175 | pg_user_mapping_user_server_index
548 | pg_foreign_data_wrapper_name_index
549 | pg_foreign_server_name_index
826 | pg_default_acl
827 | pg_default_acl_role_nsp_obj_index
828 | pg_default_acl_oid_index
1136 | pg_pltemplate
1137 | pg_pltemplate_name_index
1213 | pg_tablespace
1214 | pg_shdepend
1232 | pg_shdepend_depender_index
1233 | pg_shdepend_reference_index
1247 | pg_type
1249 | pg_attribute
1255 | pg_proc
1259 | pg_class
1260 | pg_authid
1261 | pg_auth_members
1262 | pg_database
1417 | pg_foreign_server
1418 | pg_user_mapping
2187 | pg_inherits_parent_index
2328 | pg_foreign_data_wrapper
2336 | pg_toast_2620
2337 | pg_toast_2620_index
2396 | pg_shdescription
2397 | pg_shdescription_o_c_index
2600 | pg_aggregate
2601 | pg_am
2602 | pg_amop
2603 | pg_amproc
2604 | pg_attrdef
我们在不同库先后建表，看OID其实不同。因此说OID是全数据集簇唯一的，而不只是一个database内部唯一的。
postgres=# \d
No relations found.
postgres=# create table test(a int);
CREATE TABLE
postgres=# select oid,relname from pg_class where relname='test';;
oid | relname
-------+---------
73735 | test
(1 row)
postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# \d
No relations found.
testdb=# create table test(a int);
CREATE TABLE
testdb=# select oid,relname from pg_class where relname='test';;
oid | relname
-------+---------
73738 | test
(1 row)
如何方便的知道OID对应的对象是什么？
安装目录bin下面提供了一个oid2name命令用于查看某个OID代表的数据库对象，也可以查看某个数据库对象下面的所有OID及其对象。
postgres@raspberrypi:bin $ oid2name --help
oid2name helps examining the file structure used by PostgreSQL.
Usage:
oid2name [OPTION]...
Options:
-d DBNAME database to connect to
-f FILENODE show info for table with given file node
-H HOSTNAME database server host or socket directory
-i show indexes and sequences too
-o OID show info for table with given OID
-p PORT database server port number
-q quiet (don't show headers)
-s show all tablespaces
-S show system objects too
-t TABLE show info for named table
-U NAME connect as specified database user
-V, --version output version information, then exit
-x extended (show additional columns)
-?, --help show this help, then exit
postgres@raspberrypi:bin $ oid2name
All databases:
Oid Database Name Tablespace
----------------------------------
12407 postgres pg_default
12406 template0 pg_default
1 template1 pg_default
16384 testdb pg_default
postgres@raspberrypi:bin $ oid2name -d postgres -S
From database "postgres":
Filenode Table Name
-----------------------------------
2600 pg_aggregate
2601 pg_am
2602 pg_amop
2603 pg_amproc
2604 pg_attrdef
1249 pg_attribute
1261 pg_auth_members
1260 pg_authid
2605 pg_cast
1259 pg_class
3456 pg_collation
2606 pg_constraint
2607 pg_conversion
1262 pg_database
2964 pg_db_role_setting
826 pg_default_acl
2608 pg_depend
2609 pg_description
3501 pg_enum
3466 pg_event_trigger
3079 pg_extension
2328 pg_foreign_data_wrapper
1417 pg_foreign_server
3118 pg_foreign_table
2610 pg_index
OID实际上是如何存储的？
无论是系统表的OID还是用户表创建时指定了WITH OIDS，OID在实际存储上并不是放在Tuple的数据部分，而是隐藏在了HeapTupleHeaderData里。
struct HeapTupleHeaderData
{
union
{
HeapTupleFields t_heap;
DatumTupleFields t_datum;
} t_choice;
ItemPointerData t_ctid; /* current TID of this or newer tuple (or a
* speculative insertion token) */
/* Fields below here must match MinimalTupleData! */
#define FIELDNO_HEAPTUPLEHEADERDATA_INFOMASK2 2
uint16 t_infomask2; /* number of attributes + various flags */
#define FIELDNO_HEAPTUPLEHEADERDATA_INFOMASK 3
uint16 t_infomask; /* various flag bits, see below */
#define FIELDNO_HEAPTUPLEHEADERDATA_HOFF 4
uint8 t_hoff; /* sizeof header incl. bitmap, padding */
/* ^ - 23 bytes - ^ */
#define FIELDNO_HEAPTUPLEHEADERDATA_BITS 5
bits8 t_bits[FLEXIBLE_ARRAY_MEMBER]; /* bitmap of NULLs */
/* MORE DATA FOLLOWS AT END OF STRUCT */
};
/*
* information stored in t_infomask:
*/
#define HEAP_HASNULL 0x0001 /* has null attribute(s) */
#define HEAP_HASVARWIDTH 0x0002 /* has variable-width attribute(s) */
#define HEAP_HASEXTERNAL 0x0004 /* has external stored attribute(s) */
#define HEAP_HASOID 0x0008 /* has an object-id field */
#define HEAP_XMAX_KEYSHR_LOCK 0x0010 /* xmax is a key-shared locker */
#define HEAP_COMBOCID 0x0020 /* t_cid is a combo cid */
#define HEAP_XMAX_EXCL_LOCK 0x0040 /* xmax is exclusive locker */
#define HEAP_XMAX_LOCK_ONLY 0x0080 /* xmax, if valid, is only a locker */
#define HeapTupleHeaderGetOid(tup) \
( \
((tup)->t_infomask & HEAP_HASOID) ? \
*((Oid *) ((char *)(tup) + (tup)->t_hoff - sizeof(Oid))) \
: \
InvalidOid \
)
通过t_infomask & HEAP_HASOID判断元组上是否包含OID，然后在通过HeapTupleHeaderGetOid取出OID的值。因此可以得之如果存在OID则被隐藏在t_hoff前面，如果不存在则该位置直接就是t_hoff。
综上所述
- OID既是一个数据类型也是一个列藏列，用于数据库内部对象的默认主键，作为系统表之间的关联使用。
- OID是整个数据库实例级别的全局唯一，用4字节无符号整形存储，无法保证全局唯一性，不建议用户表使用。
- OID存储在HeapTupleHeader上，不是在数据部分。因此属于隐藏列。
OID与物理存储的数据目录关系，请看：https://www.jianshu.com/p/cd8c5b988e52
网友评论