PostgreSQL 数据类型介绍（五）OID的理解

系统表, 系统表之间基本上都是以oid关联. 例如pg_attrdef.adrelid 关联 pg_class.oid

• 先介绍下oid的使用：
  以系统表 pg_class为例，查看下postgres里各个对象（表、序列、索引 等）的oid

pg_class 存储的都是这些对象的信息

postgres=# \d pg_class // 列出pg_class表的所有字段。         Table "pg_catalog.pg_class"       Column        |   Type    | Modifiers ---------------------+-----------+----------- relname             | name      | not null relnamespace        | oid       | not null reltype             | oid       | not null reloftype           | oid       | not null relowner            | oid       | not null relam               | oid       | not null relfilenode         | oid       | not null reltablespace       | oid       | not null relpages            | integer   | not null reltuples           | real      | not null relallvisible       | integer   | not null reltoastrelid       | oid       | not null relhasindex         | boolean   | not null relisshared         | boolean   | not null relpersistence      | "char"    | not null relkind             | "char"    | not null relnatts            | smallint  | not null relchecks           | smallint  | not null relhasoids          | boolean   | not null relhaspkey          | boolean   | not null relhasrules         | boolean   | not null relhastriggers      | boolean   | not null relhassubclass      | boolean   | not null relrowsecurity      | boolean   | not null relforcerowsecurity | boolean   | not null relispopulated      | boolean   | not null relreplident        | "char"    | not null relfrozenxid        | xid       | not null relminmxid          | xid       | not null relacl              | aclitem[] |  reloptions          | text[]    | Indexes:    "pg_class_oid_index" UNIQUE, btree (oid)    "pg_class_relname_nsp_index" UNIQUE, btree (relname, relnamespace)    "pg_class_tblspc_relfilenode_index" btree (reltablespace, relfilenode)//有没有觉得奇怪？ 明明没有oid这个字段，但是你执行下面语句却有结果，这是为什么呢 ？postgres=# select oid from pg_class limit 5 ;  oid  -------  2671  2672 16399 16405 16407(5 rows)同时在 \d pg_class 命令下还有这个描述 ** "pg_class_oid_index" UNIQUE, btree (oid)**oid 上有 btree索引，并且有唯一约束 pg_class_oid_index 。也证明了存在oid

那oid在哪儿？到底为什么会出现这种情况 ？

来看看postgres官网对 oid的介绍：

1.Object identifiers (OIDs) are used internally by PostgreSQL as primary keys for various system tables. 这里表明了 oid 是内部使用，并作为系统表的主键。

2.OIDs are not added to user-created tables, unless WITH OIDS is specified when the table is created, or the default_with_oids configuration variable is enabled. oid不会添加到 用户自己创建的表里，除非明确指定 WITH OIDS 或者 default_with_oids 打开。

3.Type oid represents an object identifier. There are also several alias types for oid: regproc, regprocedure, regoper, regoperator, regclass, regtype, regrole, regnamespace, regconfig, and regdictionary. Table 8-24 shows an overview. oid代表着object identifier (对象标识号). oid 还有一些别名： regproc, regprocedure, regoper, regoperator, regclass, regtype, regrole, regnamespace, regconfig, and regdictionary

4.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. So, using a user-created table’s OID column as a primary key is discouraged. OIDs are best used only for references to system tables. oid类型是unsigned four-byte integer，因此还不是足够大，不建议用作用户表的主键，最佳用处是作为系统表的主键。

根据stackoverflow的高票用户的回答：
OIDs basically give you a built-in, globally unique id for every row, contained in a system column (as opposed to a user-space column). That’s handy for tables where you don’t have a primary key, have duplicate rows, etc. For example, if you have a table with two identical rows, and you want to delete the oldest of the two, you could do that using the oid column.
In my experience, the feature is generally unused in most postgres-backed applications (probably in part because they’re non-standard), and their use is essentially deprecated:
In PostgreSQL 8.1 default_with_oids is off by default; in prior versions of PostgreSQL, it was on by default.
The use of OIDs in user tables is considered deprecated, so most installations should leave this variable disabled. Applications that require OIDs for a particular table should specify WITH OIDS when creating the table. This variable can be enabled for compatibility with old applications that do not follow this behavior.

大意是你要是有个表没有用主键，这时候可以把oid充当为主键使用，当然这是没办法的办法。

总结： oid是给内部表做标识用的，不推荐使用。 建议将 default_with_oids 设置为off。 建表的时候，如果想使用主键，请自行建立。oid本身大小固定的，万一 行数超过了oid 的最大限制数（4 byte int），那就无法插入新行了。