OID

A data type used for internal identifiers which serve as primary keys for system catalogue tables

An OID is a data type which PostgreSQL uses as a unique identifier (primary key) for database objects. The OID data type is implemented as an unsigned 32 bit integer.

Historical usage

Before PostgreSQL 7.2, OIDs were mandatory, while before PostgreSQL 8.1 , OIDs were included by default on rows in user tables. This provided the marginal advantage of providing a potentially unique identifier to distinguish otherwise identical table rows, however this uniqueness can not be guaranteed, as the OID counter might reach the upper limit of the data type's 32 bit capacity and wrap around.

In versions between PostgreSQL 8.1 and PostgreSQL 11, it is still possible to enable OIDs for user tables, either by specifying WITH OIDS in the table definition, or setting the configuration parameter default_with_oids.

If enabled, in psql following an INSERT, the most recently updated OID will be displayed and stored in the variableLASTOID. However unless the table was explictly created with OIDs, this value will usually be zero.

testdb=# CREATE TABLE oidtest (id int) WITH OIDS;
CREATE TABLE
testdb=# INSERT INTO oidtest VALUES(1);
INSERT 10992082 1
testdb=# SELECT oid, id FROM oidtest;
   oid    | id 
----------+----
 10992082 |  1
(1 row)

Removing OIDs from tables

Tables originating from older PostgreSQL versions with OIDs enabled by default can have these removed with ALTER TABLE ... SET WITHOUT OIDS, however note that in contrast to normal column removal, this will require a full table rewrite.

OIDs and system catalog tables

In PostgreSQL 11 and earlier, OIDs on system catalog tables were special "hidden" columns and needed to be selected explicitly, e.g. SELECT oid, * FROM pg_class.

OIDs from PostgreSQL 12

From PostgreSQL 12, it is no longer possible to enable OIDs for user tables. Moreover, they are now treated as normal columns on system tables and are always visible.

For more information, see commit 578b2297.

Assignment of OIDs

OIDs from 16384 onwards are automatically assigned by PostgreSQL (this value is defined by the constant FirstNormalObjectId in src/include/access/transam.h).

OIDs below 16384 are reserved for system use.

Currently, OIDs between 1-9999 are reserved for manual assignment to system catalogue entries; of these 8000-9999 are reserved for development purposes (according to commit 09568ec3, those over 9000 are reserved for use by forks).

OIDs between 10000-11999 are used for objects automatically generated by genbki.pl; OIDs between 12000 and 16383 will be used for objects created during the bootstrap process.

The script src/include/catalog/unused_oids can be used to show availabile OIDs and even provides a suggestion of which to use, e.g.:

$ src/include/catalog/unused_oids
4 - 9
111
388 - 389
560 - 583
786 - 789
811 - 816
1137
...
6122 - 8431
8434 - 8455
8457 - 8594
8597 - 9743
9745 - 9999
Patches should use a more-or-less consecutive range of OIDs.
Best practice is to start with a random choice in the range 8000-9999.
Suggested random unused OID: 9004 (740 consecutive OID(s) available starting here)

For more details see the documentation section OID Assignment.

OIDs and filenodes

When examining PostgreSQL's on-disk file structure, at first glance it may appear that the filenames associated with a table correspond to the table's OID in pg_class. However this is not necessarily always the case: the filename is identified by the filenode, which in normal cases starts off identical to the OID (note: per comment in src/backend/access/transam/xlog.c, there are corner cases following a crash situation where this might not be the case), but which can change, e.g. following a full table rewrite.

Examples

Some OIDs in their natural environment:

test=# \d pg_trigger
                 Table "pg_catalog.pg_trigger"
     Column     |     Type     | Collation | Nullable | Default 
----------------+--------------+-----------+----------+---------
 oid            | oid          |           | not null | 
 tgrelid        | oid          |           | not null | 
 tgname         | name         |           | not null | 
 tgfoid         | oid          |           | not null | 
 tgtype         | smallint     |           | not null | 
 tgenabled      | "char"       |           | not null | 
 tgisinternal   | boolean      |           | not null | 
 tgconstrrelid  | oid          |           | not null | 
 tgconstrindid  | oid          |           | not null | 
 tgconstraint   | oid          |           | not null | 
 tgdeferrable   | boolean      |           | not null | 
 tginitdeferred | boolean      |           | not null | 
 tgnargs        | smallint     |           | not null | 
 tgattr         | int2vector   |           | not null | 
 tgargs         | bytea        |           | not null | 
 tgqual         | pg_node_tree |           |          | 
 tgoldtable     | name         |           |          | 
 tgnewtable     | name         |           |          | 
Indexes:
    "pg_trigger_oid_index" UNIQUE, btree (oid)
    "pg_trigger_tgrelid_tgname_index" UNIQUE, btree (tgrelid, tgname)
    "pg_trigger_tgconstraint_index" btree (tgconstraint)

Note: the above example is taken from PostgreSQL 12; in earlier versions the first oid column would not be visible.

Categories

Data type, PostgreSQL internals

See also

filenode, oid2name, default_with_oids, OID hacks