Sql, Postgres Oids, What Are They and Why Are They Useful

SQL, Postgres OIDs, What are they and why are they useful?

OIDs basically give you a built-in id for every row, contained in a system column (as opposed to a user-space column). That's handy for tables where you don't have a primary key, have duplicate rows, etc. For example, if you have a table with two identical rows, and you want to delete the oldest of the two, you could do that using the oid column.

OIDs are implemented using 4-byte unsigned integers. They are not unique–OID counter will wrap around at 2³²-1. OID are also used to identify data types (see /usr/include/postgresql/server/catalog/pg_type_d.h).

In my experience, the feature is generally unused in most postgres-backed applications (probably in part because they're non-standard), and their use is essentially deprecated:

In PostgreSQL 8.1 default_with_oids is
off by default; in prior versions of
PostgreSQL, it was on by default.

The use of OIDs in user tables is
considered deprecated, so most
installations should leave this
variable disabled. Applications that
require OIDs for a particular table
should specify WITH OIDS when creating
the table. This variable can be
enabled for compatibility with old
applications that do not follow this
behavior.

Are the SQL, Postgres OIDs are dyanamic or static

PostgreSQL OID is static, but it will not survive dumping and restoring the database, so you should NOT rely on it.

Actually, in PostgreSQL 8.1 and later, configuration parameter default_with_oids is false by default, which means that unless you use CREATE TABLE using WITH OIDS option, OIDs will NOT be present in your table.

So, long story short - OIDs are thing of the past. Do not use it, do not rely on it, forget about it.

OID type in regular postgres function

There is nothing wrong with using oid like any other data type.

The only thing that you should keep in mind is that the OIDs that PostgreSQL uses internally to identify objects like tables are subject to change during an upgrade or restore from a dump. So you should not use oids to permanently store references to system objects.

The same applies to the ref* types, which are OIDs under the hood.

Does PostgreSQL OIDs change over time?

It is not a good idea to use the oid as permanent reference to a table.

While the oid never changes during the life time of a database, it will be different after a restore from a pg_dump.

Also a major upgrade, be it with dump/restore or pg_upgrade, will change the oids.

Enable OIDs to Postgres Table after it is created

In this case you have your answer right there in the manual for PostgreSQL 8.3:

Note that there is no variant of ALTER TABLE that allows OIDs to be
restored to a table once they have been removed.

So the answer is no.

SET WITH OIDS was implemented shortly thereafter with version 8.4.

But I can't think of a good reason to do that. OIDs in a user table, that was a broken concept to begin with. Good riddance.

How to determine the OID of a Postgres table?

The postgres catalog table pg_class is what you should look at. There should be one row per table, with the table name in the column relname, and the oid in the hidden column oid.

You may also be interested in the pg_attribute catalog table, which includes one row per table column.

See: http://www.postgresql.org/docs/current/static/catalog-pg-class.html and http://www.postgresql.org/docs/current/static/catalog-pg-attribute.html

PostgreSQL function-OID: 'my OID' where 'my' is the currently executing user-defined-function

Using the OID of a function does not necessarily prevent a naming conflict. The same function could be run multiple times in the same session.

If you are in need of a unique name, use a SEQUENCE. Run once in your database:

CREATE SEQUENCE tt_seq;

Then, in your plpgsql function or DO statement:

DO
$$
DECLARE
    _tbl text := 'tt' || nextval('tt_seq');
BEGIN
    EXECUTE 'CREATE TEMP TABLE ' || _tbl || '(id int)';
END
$$

Drawback is that you have to use dynamic SQL for dynamic identifiers. Plain SQL commands do not accept parameters for identifiers.

---

Related Topics