SQL、Postgres OID，它们是什么以及它们为什么有用？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5625585/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL, Postgres OIDs, What are they and why are they useful?
提问by fabrizioM
I am looking at some PostgreSQL table creation and I stumbled upon this:
我正在查看一些 PostgreSQL 表创建，我偶然发现了这一点：
CREATE TABLE (
...
) WITH ( OIDS = FALSE );
I read the documentation provided by postgres and I know the concept of object identifier from OOP but still I do not grasp,
我阅读了 postgres 提供的文档，我知道 OOP 中对象标识符的概念，但我仍然不明白，
- why such identifier would be useful in a database?
- to make queries shorter?
- when should it be used?
- 为什么这样的标识符在数据库中会有用？
- 使查询更短？
- 什么时候应该使用？
回答by Frank Farmer
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.
OID 基本上为包含在系统列（而不是用户空间列）中的每一行提供了一个内置的、全局唯一的 ID。这对于没有主键、有重复行等的表很方便。例如，如果你有一个包含两个相同行的表，并且你想删除两个中最旧的，你可以使用oid 列。
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:
根据我的经验，该功能在大多数 postgres 支持的应用程序中通常未使用（可能部分是因为它们是非标准的），并且它们的使用基本上已被弃用：
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.
在 PostgreSQL 8.1 default_with_oids 默认关闭；在以前的 PostgreSQL 版本中，它默认是打开的。
在用户表中使用 OID 被视为已弃用，因此大多数安装应禁用此变量。需要特定表的 OID 的应用程序应在创建表时指定 WITH OIDS。可以启用此变量以与不遵循此行为的旧应用程序兼容。
回答by rogerdpack
OID's are still in use for Postgres withlarge objects(though some people would argue large objects are not generally useful anyway). They are also used extensively by system tables. They are used for instance by TOASTwhich stores larger than 8KB BYTEA's (etc.) off to a separate storage area (transparently) which is used by default by all tables. Their direct use associated with "normal" user tables is basically deprecated.
OID 仍然用于具有大对象的Postgres （尽管有些人会争辩说大对象通常没有用处）。它们也被系统表广泛使用。例如，它们被TOAST使用，它将大于 8KB 的 BYTEA（等）存储到一个单独的存储区域（透明），默认情况下所有表都使用该区域。它们与“普通”用户表相关的直接使用基本上已被弃用。
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 类型目前被实现为一个无符号的四字节整数。因此，它不足以在大型数据库中，甚至在大型单个表中提供数据库范围的唯一性。因此，不鼓励使用用户创建的表的 OID 列作为主键。OID 最好仅用于引用系统表。
Apparently the OID sequence "does" wrap if it exceeds 4B 6. So in essence it's a global counter that can wrap. If it does wrap, some slowdown may start occurring when it's used and "searched" for unique values, etc.
显然，如果 OID 序列超过 4B 6 ，则它“确实”换行。所以本质上它是一个可以换行的全局计数器。如果它确实包装了，则在使用它并“搜索”唯一值等时可能会开始出现一些减速。
See also https://wiki.postgresql.org/wiki/FAQ#What_is_an_OID.3F
回答by Basil Bourque
OIDs being phased out
正在逐步淘汰 OID
The core team responsible for Postgres is gradually phasing out OIDs.
负责 Postgres 的核心团队正在逐步淘汰 OID。
Postgres 12 removes special behavior of OID columns
Postgres 12 移除了 OID 列的特殊行为
The use of OID as an optional system column on your tables is now removed from Postgres 12. You can no longer use:
现在从 Postgres 12 中删除了将 OID 用作表上可选系统列的做法。您不能再使用：
CREATE TABLE … WITH OIDScommanddefault_with_oids (boolean)compatibility setting
CREATE TABLE … WITH OIDS命令default_with_oids (boolean)兼容性设置
The data type OIDremains in Postgres 12. You can explicitly create a column of the type OID.
数据类型OID保留在 Postgres 12 中。您可以显式创建类型为 的列OID。
After migrating to Postgres 12, any optionally-defined system columnoidwill no longer be invisible by default. Performing a SELECT *will now include this column. Note that this extra “surprise” column may break na?vely written SQL code.
后迁移到的Postgres 12，任何任选地定义系统柱oid将不再通过默认不可见的。执行 aSELECT *现在将包括此列。请注意，这个额外的“惊喜”列可能会破坏简单编写的 SQL 代码。
回答by Rodrigo Boratto
To remove all OIDs from your database tables, you can use this Linux script:
要从数据库表中删除所有 OID，您可以使用以下 Linux 脚本：
First, login as PostgreSQL superuser:
首先，以 PostgreSQL 超级用户身份登录：
sudo su postgres
Now run this script, changing YOUR_DATABASE_NAME with you database name:
现在运行此脚本，使用您的数据库名称更改 YOUR_DATABASE_NAME：
for tbl in `psql -qAt -c "select schemaname || '.' || tablename from pg_tables WHERE schemaname <> 'pg_catalog' AND schemaname <> 'information_schema';" YOUR_DATABASE_NAME` ; do psql -c "alter table $tbl SET WITHOUT OIDS" YOUR_DATABASE_NAME ; done
I used this script to remove all my OIDs, since Npgsql 3.0 doesn't work with this, and it isn't important to PostgreSQL anymore.
我使用这个脚本删除了我所有的 OID，因为 Npgsql 3.0 不能使用它，而且它对 PostgreSQL 不再重要了。
