Object identifiers (OIDs) are used internally by
PostgreSQL as primary keys for various system
tables. Also, an OID system column is added to user-created tables
(unless WITHOUT OIDS is specified at table creation time).
Type oid represents an object identifier. There are also
several aliases for oid: regproc, regprocedure,
regoper, regoperator, regclass,
and regtype. Table 5-20 shows an overview.
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.
The oid type itself has few operations beyond comparison
(which is implemented as unsigned 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 type oid would use. The alias
types allow simplified lookup of OID values for objects: for example,
one may write 'mytable'::regclass to get the OID of table
mytable, rather than SELECT oid FROM pg_class WHERE
relname = 'mytable'. (In reality, a much more complicated SELECT would
be needed to deal with selecting the right OID when there are multiple
tables named mytable in different schemas.)
Table 5-20. Object Identifier Types
| Type 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 | type name | integer |
All of the OID alias types 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.
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 is more appropriate. For regoperator,
unary operators are identified by writing NONE for the unused
operand.
OIDs are 32-bit quantities and are assigned from a single cluster-wide
counter. In a large or long-lived database, it is possible for the
counter to wrap around. Hence, it is bad practice to assume that OIDs
are unique, unless you take steps to ensure that they are unique.
Recommended practice when using OIDs for row identification is to create
a unique constraint on the OID column of each table for which the OID will
be used. Never assume that OIDs are unique across tables; use the
combination of tableoid and row OID if you need a
database-wide identifier. (Future releases of
PostgreSQL are likely to use a separate
OID counter for each table, so that tableoid
must be included to arrive at a globally unique identifier.)
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 a long-lived
database it is possible for transaction IDs to wrap around. This
is not a fatal problem given appropriate maintenance procedures;
see the PostgreSQL 7.3 Administrator's Guide for details. However, it is
unwise to depend on uniqueness of transaction IDs over the long term
(more than one billion transactions).
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. This creates a hard limit
of 232 (4 billion) SQL commands
within a single transaction. In practice this limit is not a
problem --- note that the limit is on number of
SQL commands, not number of tuples processed.
A final identifier type used by the system is tid, or tuple
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 tuple within its table.
