OID介紹
在PostgreSQL中，對象標識符Object identifiers (OIDs) 用來在整個數(shù)據(jù)集簇中唯一的標識一個數(shù)據(jù)庫對象，這個對象可以是數(shù)據(jù)庫、表、索引、視圖、元組、類型等等。
同時OID也是系統(tǒng)內(nèi)部的一個數(shù)據(jù)類型，用4個字節(jié)的無符號整數(shù)表示。
OID的分配由系統(tǒng)中的一個全局OID計數(shù)器來實現(xiàn)，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通常被用于系統(tǒng)表的主鍵，進行系統(tǒng)表直接的鏈接。
- 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字節(jié)無符號整形存儲，并且不能保證數(shù)據(jù)庫級的全局唯一性，甚至是數(shù)據(jù)量的大的表中元組的唯一性。
驗證OID的數(shù)據(jù)集簇級別的全局唯一性
查看pg_class中表的OID，發(fā)現(xiàn)每個庫的相同系統(tǒng)表的OID一樣，這是由于他們都是從模板庫template1拷貝而來才導(dǎo)致一樣。
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是全數(shù)據(jù)集簇唯一的，而不只是一個database內(nèi)部唯一的。
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對應(yīng)的對象是什么？
安裝目錄bin下面提供了一個oid2name命令用于查看某個OID代表的數(shù)據(jù)庫對象，也可以查看某個數(shù)據(jù)庫對象下面的所有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實際上是如何存儲的？
無論是系統(tǒng)表的OID還是用戶表創(chuàng)建時指定了WITH OIDS，OID在實際存儲上并不是放在Tuple的數(shù)據(jù)部分，而是隱藏在了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既是一個數(shù)據(jù)類型也是一個列藏列，用于數(shù)據(jù)庫內(nèi)部對象的默認主鍵，作為系統(tǒng)表之間的關(guān)聯(lián)使用。
- OID是整個數(shù)據(jù)庫實例級別的全局唯一，用4字節(jié)無符號整形存儲，無法保證全局唯一性，不建議用戶表使用。
- OID存儲在HeapTupleHeader上，不是在數(shù)據(jù)部分。因此屬于隱藏列。
OID與物理存儲的數(shù)據(jù)目錄關(guān)系，請看：http://www.itdecent.cn/p/cd8c5b988e52
