OID-related hacks

Show next OID

Scenario: you want to which OID will be used next:

Solution: Execute SELECT next_oid FROM pg_control_checkpoint() to display the approximate value of the next OID (as of the most recent checkpoint):

postgres=# SELECT next_oid FROM pg_control_checkpoint();
 next_oid
----------
    95549
(1 row)

For versions prior to PostgreSQL 9.6, use pg_controldata to extract the NextOID field:

postgres $ pg_controldata /path/to/pgdata | grep OID
Latest checkpoint's NextOID:          2254511

Show tables with OIDs

Scenario: You have a legacy database and want to check which tables, if any, still have OIDs enabled.

Solution: execute the following query

testdb=> SELECT n.nspname, c.relname, c.relhasoids
           FROM pg_class c
           JOIN pg_namespace n ON (c.relnamespace=n.oid)
          WHERE n.nspname !~ '^pg_'
            AND c.relkind='r'
            AND c.relhasoids IS TRUE;
 nspname |         relname         | relhasoids
---------+-------------------------+------------
 appdb   | entry                   | t
 appdb   | form_session            | t
 appdb   | action                  | t
 appdb   | language                | t
 appdb   | markup_language_variant | t
(5 rows)

Remove OIDs from all tables which still have them

The following anonymous code block looks up all tables with OIDs and dynamically executes ALTER TABLE ... SET WITHOUT OIDS:

DO LANGUAGE plpgsql $$
DECLARE
  rec_table RECORD;
BEGIN
  FOR rec_table IN
    SELECT c.relname
      FROM pg_namespace n
INNER JOIN pg_class c
        ON n.oid=c.relnamespace
     WHERE n.nspname !~ '^pg_'
       AND c.relkind='r'
       AND c.relhasoids IS TRUE
  ORDER BY 1
   LOOP
     RAISE NOTICE 'Removing OIDs from table %', rec_table.relname;
     EXECUTE FORMAT ('ALTER TABLE %I SET WITHOUT OIDS', rec_table.relname);
   END LOOP;
END
$$;

Get the OID of the current database

As there's no regdatabase modifier, the only way to do this is to combine a query identifying the current session with a system catalogue table which can correlate that identification with a particular database, e.g.:

SELECT oid FROM pg_database WHERE datname = current_database()

or:

SELECT datid FROM pg_stat_activity WHERE pid = pg_backend_pid()