Postgresql 中文操作指南

8.19. Object Identifier Types #

PostgreSQL 在内部将对象标识符 (OID) 用作各种系统表的主键。类型 oid 表示一个对象标识符。 oid 也有多个别名类型，每个都命名为 reg_something_ 。 Table 8.26 显示了概览。

Object identifiers (OIDs) are used internally by PostgreSQL as primary keys for various system tables. Type oid represents an object identifier. There are also several alias types for oid, each named reg_something_. Table 8.26 shows an overview.

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 类型本身除比较之外几乎没有其他操作。但是，它可以转换为整数，然后使用标准整数运算符进行操作。（这样做时，要小心可能存在的有符号与无符号之间的混淆。）

The oid type itself has few operations beyond comparison. It can be cast to integer, however, and then manipulated using the standard integer operators. (Beware of possible signed-versus-unsigned confusion if you do this.)

OID 别名类型没有自己的操作，除了专门的输入和输出例程。这些例程能够接受和显示系统对象的符号名称，而不是类型 oid 将使用的原始数值。别名类型允许简化对对象 OID 值的查找。例如，要检查与表 mytable 相关的 pg_attribute 行，可以写：

The OID alias types have no operations of their own except for specialized input and output routines. These routines are able to accept and display symbolic names for system objects, rather than the raw numeric value that type oid would use. The alias types allow simplified lookup of OID values for objects. For example, to examine the pg_attribute rows related to a table mytable, one could write:

SELECT * FROM pg_attribute WHERE attrelid = 'mytable'::regclass;

而不是：

rather than:

SELECT * FROM pg_attribute
  WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'mytable');

虽然这本身看起来并不好，但它仍然过于简单。如果在不同的模式中有多个名为 mytable 的表，则需要更复杂的子选择来选择正确的 OID。regclass 输入转换器根据模式路径设置处理表查找，因此它会自动执行“正确的事情”。同样，将表的 OID 转换为 regclass 便于符号显示数字 OID。

While that doesn’t look all that bad by itself, it’s still oversimplified. A far more complicated sub-select would be needed to select the right OID if there are multiple tables named mytable in different schemas. The regclass input converter handles the table lookup according to the schema path setting, and so it does the “right thing” automatically. Similarly, casting a table’s OID to regclass is handy for symbolic display of a numeric OID.

Table 8.26. Object Identifier Types

Name

References

Description

Value Example

oid

any

numeric object identifier

564182

regclass

pg_class

relation name

pg_type

regcollation

pg_collation

collation name

"POSIX"

regconfig

pg_ts_config

text search configuration

english

regdictionary

pg_ts_dict

text search dictionary

simple

regnamespace

pg_namespace

namespace name

pg_catalog

regoper

pg_operator

operator name

regoperator

pg_operator

operator with argument types

*(integer,integer) or -(NONE,integer)

regproc

pg_proc

function name

sum

regprocedure

pg_proc

function with argument types

sum(int4)

regrole

pg_authid

role name

smithee

regtype

pg_type

data type name

integer

对于按名称空间分组的对象的所有 OID 别名类型，都接受模式限定名，并且如果在当前搜索路径中找不到对象而不进行限定，则在输出上显示模式限定名。例如，myschema.mytable 是 regclass 可接受的输入（如果存在这样的表）。该值可能会作为 myschema.mytable 输出，也可能只是 mytable，具体取决于当前搜索路径。regproc 和 regoper 别名类型只会接受唯一的输入名称（不重载），因此它们用处有限；对于大多数用途，regprocedure 或 regoperator 更合适。对于 regoperator，一元运算符的识别方式是，为未使用的运算符编写 NONE。

All of the OID alias types for objects that are grouped by namespace accept schema-qualified names, and will display schema-qualified names on output if the object would not be found in the current search path without being qualified. For example, myschema.mytable is acceptable input for regclass (if there is such a table). That value might be output as myschema.mytable, or just mytable, depending on the current search path. The regproc and regoper alias types will only accept input names that are unique (not overloaded), so they are of limited use; for most uses regprocedure or regoperator are more appropriate. For regoperator, unary operators are identified by writing NONE for the unused operand.

这些类型的输入函数允许代币之间有空格，并将折叠大写字母为小写字母，双引号内的除外；这样做是为了使语法规则类似于在 SQL 中编写对象名称的方式。相反，输出函数会根据需要使用双引号，以使输出成为有效的 SQL 标识符。例如，名为 Foo（大写 F）且采用两个整型参数的函数的 OID 可以输入为 ' "Foo" ( int, integer ) '::regprocedure。输出看起来像 "Foo"(integer,integer)。函数名称和参数类型名称也可以进行模式限定。

The input functions for these types allow whitespace between tokens, and will fold upper-case letters to lower case, except within double quotes; this is done to make the syntax rules similar to the way object names are written in SQL. Conversely, the output functions will use double quotes if needed to make the output be a valid SQL identifier. For example, the OID of a function named Foo (with upper case F) taking two integer arguments could be entered as ' "Foo" ( int, integer ) '::regprocedure. The output would look like "Foo"(integer,integer). Both the function name and the argument type names could be schema-qualified, too.

许多内置 PostgreSQL 函数都接受表或其他类型数据库对象的 OID，为了方便，声明为采用 regclass（或适当的 OID 别名类型）。这意味着您不必手动查找对象的 OID，而只需将其名称作为字符串输入即可。例如，nextval(regclass) 函数采用序列关系的 OID，因此您可以像这样调用它：

Many built-in PostgreSQL functions accept the OID of a table, or another kind of database object, and for convenience are declared as taking regclass (or the appropriate OID alias type). This means you do not have to look up the object’s OID by hand, but can just enter its name as a string literal. For example, the nextval(regclass) function takes a sequence relation’s OID, so you could call it like this:

nextval('foo')              operates on sequence foo
nextval('FOO')              same as above
nextval('"Foo"')            operates on sequence Foo
nextval('myschema.foo')     operates on myschema.foo
nextval('"myschema".foo')   same as above
nextval('foo')              searches search path for foo

Note

将此类函数的自变量写为朴素的文本文字时，它将成为 regclass 类型（或相应的类型）的常量。由于这只是一种 OID，因此它将跟踪最初标识的对象，而不受后续重命名、模式重新分配等的约束。此“早期绑定”行为通常对于列默认值和视图中的对象引用是理想的。但有时你可能需要“延迟绑定”，其中对象引用将在运行时进行解析。为了获得延迟绑定行为，强制将常量存储为 text 常量，而不是 regclass：

When you write the argument of such a function as an unadorned literal string, it becomes a constant of type regclass (or the appropriate type). Since this is really just an OID, it will track the originally identified object despite later renaming, schema reassignment, etc. This “early binding” behavior is usually desirable for object references in column defaults and views. But sometimes you might want “late binding” where the object reference is resolved at run time. To get late-binding behavior, force the constant to be stored as a text constant instead of regclass:

nextval('foo'::text)      foo is looked up at runtime

to_regclass() 函数及其同类函数还可用于执行运行时查找。参见 Table 9.72。

The to_regclass() function and its siblings can also be used to perform run-time lookups. See Table 9.72.

使用 regclass 的另一个实际示例是查找 information_schema 视图中列出的表的 OID，而这些视图不直接提供此类 OID。例如，您可能希望调用需要表 OID 的 pg_relation_size() 函数。考虑上述规则，正确的执行方法是

Another practical example of use of regclass is to look up the OID of a table listed in the information_schema views, which don’t supply such OIDs directly. One might for example wish to call the pg_relation_size() function, which requires the table OID. Taking the above rules into account, the correct way to do that is

SELECT table_schema, table_name,
       pg_relation_size((quote_ident(table_schema) || '.' ||
                         quote_ident(table_name))::regclass)
FROM information_schema.tables
WHERE ...

quote_ident() 函数将处理在需要时对标识符加上双引号。看似更容易的

The quote_ident() function will take care of double-quoting the identifiers where needed. The seemingly easier

SELECT pg_relation_size(table_name)
FROM information_schema.tables
WHERE ...

是 not recommended，因为它对于搜索路径之外的表或需要引用符号的名称会失败。

is not recommended, because it will fail for tables that are outside your search path or have names that require quoting.

大多数 OID 别名类型的附加属性是依赖关系的创建。如果这些类型之一的常量出现在存储表达式（比如列默认表达式或视图）中，它就会创建一个对所引用对象的依赖关系。比如，如果一个列有默认表达式 nextval('my_seq'::regclass)，PostgreSQL 会理解默认表达式取决于序列 my_seq，这样系统就不会让序列被删除而不先删除默认表达式。nextval('my_seq'::text) 的替代创建了一个依赖关系。（regrole 是该属性的例外。这种类型的常量不在存储表达式中允许使用。）

An additional property of most of the OID alias types is the creation of dependencies. If a constant of one of these types appears in a stored expression (such as a column default expression or view), it creates a dependency on the referenced object. For example, if a column has a default expression nextval('my_seq'::regclass), PostgreSQL understands that the default expression depends on the sequence my_seq, so the system will not let the sequence be dropped without first removing the default expression. The alternative of nextval('my_seq'::text) does not create a dependency. (regrole is an exception to this property. Constants of this type are not allowed in stored expressions.)

系统使用的另一个标识符类型是 xid，即事务（缩写为 xact）标识符。这是系统列 xmin 和 xmax 的数据类型。事务标识符是 32 位量。在某些情况下，使用 64 位变体 xid8。与 xid 值不同，xid8 值严格单调增加，并且不能在数据库集群的生命周期内重复使用。有关详细信息，请参阅 Section 74.1。

Another identifier type used by the system is xid, or transaction (abbreviated xact) identifier. This is the data type of the system columns xmin and xmax. Transaction identifiers are 32-bit quantities. In some contexts, a 64-bit variant xid8 is used. Unlike xid values, xid8 values increase strictly monotonically and cannot be reused in the lifetime of a database cluster. See Section 74.1 for more details.

系统使用的第三个标识符类型是 cid，或命令标识符。这是系统列 cmin 和 cmax 的数据类型。命令标识符也是 32 位量。

A third identifier type used by the system is cid, or command identifier. This is the data type of the system columns cmin and cmax. Command identifiers are also 32-bit quantities.

系统使用的最后一个标识符类型是 tid，或者元组标识符（行标识符）。这是系统列 ctid 的数据类型。一个元组 ID 是一对（块号，块内的元组索引），它标识了表内行的物理位置。

A final identifier type used by the system is tid, or tuple identifier (row identifier). This is the data type of the system column ctid. A tuple ID is a pair (block number, tuple index within block) that identifies the physical location of the row within its table.

（系统列在 Section 5.5 中得到了进一步解释。）

(The system columns are further explained in Section 5.5.)