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.
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
If enabled, in
psql following an
INSERT, the most recently updated
OID will be displayed and stored in the variable
LASTOID. However unless the table was explictly created with
OIDs, this value will usually be zero.
postgres=# CREATE TABLE oidtest (id int) WITH OIDS; CREATE TABLE
postgres=# INSERT INTO oidtest VALUES(1); INSERT 10992082 1
postgres=# 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 available 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
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.
Some OIDs in their natural environment:
postgres=# \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.