8.18. 指標型別

Object identifiers (OIDs) are used internally byPostgreSQLas primary keys for various system tables. OIDs are not added to user-created tables, unlessWITH OIDSis specified when the table is created, or thedefault_with_oidsconfiguration variable is enabled. Typeoidrepresents an object identifier. There are also several alias types foroid:regproc,regprocedure,regoper,regoperator,regclass,regtype,regrole,regnamespace,regconfig, andregdictionary.Table 8.24shows an overview.

Theoidtype 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.

Theoidtype 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.)

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 typeoidwould use. The alias types allow simplified lookup of OID values for objects. For example, to examine thepg_attributerows related to a tablemytable, 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');

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 namedmytablein different schemas. Theregclassinput 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 toregclassis handy for symbolic display of a numeric OID.

Table 8.24. Object Identifier Types

Name

References

Description

Value Example

oid

any

numeric object identifier

564182

regproc

pg_proc

function name

sum

regprocedure

pg_proc

function with argument types

sum(int4)

regoper

pg_operator

operator name

+

regoperator

pg_operator

operator with argument types

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

regclass

pg_class

relation name

pg_type

regtype

pg_type

data type name

integer

regrole

pg_authid

role name

smithee

regnamespace

pg_namespace

namespace name

pg_catalog

regconfig

pg_ts_config

text search configuration

english

regdictionary

pg_ts_dict

text search dictionary

simple

All of the OID alias types for objects 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. Theregprocandregoperalias types will only accept input names that are unique (not overloaded), so they are of limited use; for most usesregprocedureorregoperatorare more appropriate. Forregoperator, unary operators are identified by writingNONEfor the unused operand.

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 expressionnextval('my_seq'::regclass),PostgreSQLunderstands that the default expression depends on the sequencemy_seq; the system will not let the sequence be dropped without first removing the default expression.regroleis the only exception for the property. Constants of this type are not allowed in such expressions.

Note

The OID alias types do not completely follow transaction isolation rules. The planner also treats them as simple constants, which may result in sub-optimal planning.

Another identifier type used by the system isxid, or transaction (abbreviatedxact) identifier. This is the data type of the system columnsxminandxmax. Transaction identifiers are 32-bit quantities.

A third identifier type used by the system iscid, or command identifier. This is the data type of the system columnscminandcmax. Command identifiers are also 32-bit quantities.

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

(The system columns are further explained inSection 5.4.)