OID介紹

在PostgreSQL中隐解，對(duì)象標(biāo)識(shí)符Object identifiers (OIDs) 用來在整個(gè)數(shù)據(jù)集簇中唯一的標(biāo)識(shí)一個(gè)數(shù)據(jù)庫對(duì)象饿悬，這個(gè)對(duì)象可以是數(shù)據(jù)庫捐顷、表、索引狸膏、視圖沟饥、元組、類型等等湾戳。
同時(shí)OID也是系統(tǒng)內(nèi)部的一個(gè)數(shù)據(jù)類型贤旷，用4個(gè)字節(jié)的無符號(hào)整數(shù)表示。

OID的分配由系統(tǒng)中的一個(gè)全局OID計(jì)數(shù)器來實(shí)現(xiàn)砾脑，OID分配時(shí)會(huì)采用互斥鎖加以鎖定以避免多個(gè)要求分配OID的請(qǐng)求獲得相同的OID幼驶。

官方介紹 https://www.postgresql.org/docs/12/datatype-oid.html#DATATYPE-OID-TABLE

1. Object identifiers (OIDs) are used internally by PostgreSQL as primary keys for various system tables. OID通常被用于系統(tǒng)表的主鍵，進(jìn)行系統(tǒng)表直接的鏈接韧衣。
2. 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é)無符號(hào)整形存儲(chǔ)盅藻，并且不能保證數(shù)據(jù)庫級(jí)的全局唯一性，甚至是數(shù)據(jù)量的大的表中元組的唯一性畅铭。

驗(yàn)證OID的數(shù)據(jù)集簇級(jí)別的全局唯一性

查看pg_class中表的OID氏淑，發(fā)現(xiàn)每個(gè)庫的相同系統(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

我們?cè)诓煌瑤煜群蠼ū砑俨校碠ID其實(shí)不同。因此說OID是全數(shù)據(jù)集簇唯一的炉擅，而不只是一個(gè)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對(duì)應(yīng)的對(duì)象是什么？

安裝目錄bin下面提供了一個(gè)oid2name命令用于查看某個(gè)OID代表的數(shù)據(jù)庫對(duì)象坑资，也可以查看某個(gè)數(shù)據(jù)庫對(duì)象下面的所有OID及其對(duì)象耗帕。

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實(shí)際上是如何存儲(chǔ)的穆端？

無論是系統(tǒng)表的OID還是用戶表創(chuàng)建時(shí)指定了WITH OIDS袱贮，OID在實(shí)際存儲(chǔ)上并不是放在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柒莉。

綜上所述

1. OID既是一個(gè)數(shù)據(jù)類型也是一個(gè)列藏列，用于數(shù)據(jù)庫內(nèi)部對(duì)象的默認(rèn)主鍵沽翔，作為系統(tǒng)表之間的關(guān)聯(lián)使用兢孝。
2. OID是整個(gè)數(shù)據(jù)庫實(shí)例級(jí)別的全局唯一窿凤，用4字節(jié)無符號(hào)整形存儲(chǔ)，無法保證全局唯一性跨蟹，不建議用戶表使用雳殊。
3. OID存儲(chǔ)在HeapTupleHeader上，不是在數(shù)據(jù)部分窗轩。因此屬于隱藏列夯秃。

OID與物理存儲(chǔ)的數(shù)據(jù)目錄關(guān)系，請(qǐng)看：http://www.reibang.com/p/cd8c5b988e52