Using pg_repack to remove OIDs from tables in PostgreSQL

I’m prepping a legacy PostgreSQL 9.5 database for upgrade.

I need to remove OIDs from tables without locking the tables for long periods of time. I have developed a strategy that seems to work, but I would like the experts to weigh in since I’m planning on doing things to the system tables that are generally frowned upon.

Prior to running pg_repack I perform these modifications:

mydata=# update pg_class set relhasoids = false where oid = 'some_schema.a_very_large_table_with_oids'::regclass;
UPDATE 1
mydata=# delete from pg_attribute where attrelid = 'some_schema.a_very_large_table_with_oids'::regclass and attname = 'oid';
DELETE 1
mydata=# d+ some_schema.a_very_large_table_with_oids;
        Table "some_schema.a_very_large_table_with_oids"
 Column | Type | Modifiers | Storage  | Stats target | Description 
--------+------+-----------+----------+--------------+-------------
 k      | text | not null  | extended |              | 
 v      | text |           | extended |              | 
Indexes:
    "a_very_large_table_with_oids_pkey" PRIMARY KEY, btree (k)

mydata=# select oid,* from some_schema.a_very_large_table_with_oids;
ERROR:  column "oid" does not exist

So far so good. I can insert update and delete rows, but the table structure on disk is unchanged. I’ll need to fix that.

So after those modifications are made, I repack the table with vanilla pg_repack. That copies the data to fresh new tables on-disk, sans oids.

Before I start performing these operations on-line on the production data I wanted some expert eyes on this process since this is mission critical stuff.

On the very large tables we will be in this limbo state for an extended period of time where pg_class and pg_attribute will have those forced modifications while pg_repack works its magic.

Is there anything to be concerned about if insert/update/delete seems to be working?

My insert/update/deletes on the tables while pg_repack is running seem to work fine.

I also tried this on tables that have toast tables attached. Upon first glance everything seems to be in order.

This is a destructive operation, and I’m not using the canonical ALTER TABLE ... SET WITHOUT OIDS. I want to be sure I’m not setting myself up for a problem down the line if there is some detail that I’ve missed (like during backup-restore or replication).

Please advise.

UPDATE:

Laurenz’s excellent answer has given me two additional things I didn’t think of– namely the dependency check and the cache/plan invalidation. Working those into the plan will be important. Performing short vacuums to clear out the stale columns is a good fallback position, but based on what I see happening with pg_repack, I think that will accomplish the same thing… The logs from pg_repack expose all of the SQL involved in copying over the table and swapping definitions:

mydata=# begin;
BEGIN
mydata=# ALTER TABLE perm.permission_cache SET WITHOUT OIDS;
^CCancel request sent
ERROR:  canceling statement due to user request
mydata=# rollback;
ROLLBACK
mydata=# d+ perm.permission_cache
                                                       Table "perm.permission_cache"
    Column    |           Type           |                        Modifiers                        | Storage  | Stats target | Description 
--------------+--------------------------+---------------------------------------------------------+----------+--------------+-------------
 id           | integer                  | not null default nextval('perm.cache_id_seq'::regclass) | plain    |              | 
 company_uuid | uniqueidentifier         | not null                                                | plain    |              | 
 user_uuid    | uniqueidentifier         | not null                                                | plain    |              | 
 value        | boolean                  | not null                                                | plain    |              | 
 cache_date   | timestamp with time zone | not null default now()                                  | plain    |              | 
 token_name   | character varying(255)   |                                                         | extended |              | 
Indexes:
    "cache_id_pkey" PRIMARY KEY, btree (id)
    "permission_cache_user_token_idx" UNIQUE, btree (user_uuid, token_name)
Foreign-key constraints:
    "company_uuid_fkey" FOREIGN KEY (company_uuid) REFERENCES company_table(company_uuid) ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED
    "user_uuid_fkey" FOREIGN KEY (user_uuid) REFERENCES user_table(user_uuid) ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED
Has OIDs: yes

mydata=# e
BEGIN
ALTER TABLE
UPDATE 1
DELETE 1
COMMIT
mydata=# q

$ pg_repack -h 127.0.0.1 -d mydata -Upostgres -t 'perm.permission_cache' -e
LOG: (query) SET search_path TO pg_catalog, pg_temp, public
LOG: (query) SET search_path TO pg_catalog, pg_temp, public
LOG: (query) select repack.version(), repack.version_sql()
LOG: (query) SET statement_timeout = 0
LOG: (query) SET search_path = pg_catalog, pg_temp, public
LOG: (query) SET client_min_messages = warning
LOG: (query) SELECT t.*, coalesce(v.tablespace, t.tablespace_orig) as tablespace_dest FROM repack.tables t,  (VALUES (quote_ident($1::text))) as v (tablespace) WHERE (relid = $2::regclass) ORDER BY t.relname, t.schemaname
LOG:    (param:0) = (null)
LOG:    (param:1) = perm.permission_cache
INFO: repacking table "perm.permission_cache"
LOG: (query) SELECT pg_try_advisory_lock($1, CAST(-2147483648 + $2::bigint AS integer))
LOG:    (param:0) = 16185446
LOG:    (param:1) = 1046889774
LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
LOG: (query) SET LOCAL statement_timeout = 100
LOG: (query) LOCK TABLE perm.permission_cache IN ACCESS EXCLUSIVE MODE
LOG: (query) RESET statement_timeout
LOG: (query) SELECT pg_get_indexdef(indexrelid) FROM pg_index WHERE indrelid = $1 AND NOT indisvalid
LOG:    (param:0) = 1046889774
LOG: (query) SELECT indexrelid, repack.repack_indexdef(indexrelid, indrelid, $2, FALSE)  FROM pg_index WHERE indrelid = $1 AND indisvalid
LOG:    (param:0) = 1046889774
LOG:    (param:1) = (null)
LOG: (query) SELECT repack.conflicted_triggers($1)
LOG:    (param:0) = 1046889774
LOG: (query) CREATE TYPE repack.pk_1046889774 AS (id integer)
LOG: (query) CREATE TABLE repack.log_1046889774 (id bigserial PRIMARY KEY, pk repack.pk_1046889774, row perm.permission_cache)
LOG: (query) CREATE TRIGGER repack_trigger AFTER INSERT OR DELETE OR UPDATE ON perm.permission_cache FOR EACH ROW EXECUTE PROCEDURE repack.repack_trigger('INSERT INTO repack.log_1046889774(pk, row) VALUES( CASE WHEN $1 IS NULL THEN NULL ELSE (ROW($1.id)::repack.pk_1046889774) END, $2)')
LOG: (query) ALTER TABLE perm.permission_cache ENABLE ALWAYS TRIGGER repack_trigger
LOG: (query) SELECT repack.disable_autovacuum('repack.log_1046889774')
LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
LOG: (query) SELECT pg_backend_pid()
LOG: (query) SELECT pid FROM pg_locks WHERE locktype = 'relation' AND granted = false AND relation = 1046889774 AND mode = 'AccessExclusiveLock' AND pid <> pg_backend_pid()
LOG: (query) COMMIT
LOG: (query) BEGIN ISOLATION LEVEL SERIALIZABLE
LOG: (query) SELECT set_config('work_mem', current_setting('maintenance_work_mem'), true)
LOG: (query) SELECT coalesce(array_agg(l.virtualtransaction), '{}')   FROM pg_locks AS l   LEFT JOIN pg_stat_activity AS a     ON l.pid = a.pid   LEFT JOIN pg_database AS d     ON a.datid = d.oid   WHERE l.locktype = 'virtualxid'   AND l.pid NOT IN (pg_backend_pid(), $1)   AND (l.virtualxid, l.virtualtransaction) <> ('1/1', '-1/0')   AND (a.application_name IS NULL OR a.application_name <> $2)  AND a.query !~* E'^\s*vacuum\s+'   AND a.query !~ E'^autovacuum: '   AND ((d.datname IS NULL OR d.datname = current_database()) OR l.database = 0)
LOG:    (param:0) = 11918
LOG:    (param:1) = pg_repack
LOG: (query) DELETE FROM repack.log_1046889774
LOG: (query) SELECT pid FROM pg_locks WHERE locktype = 'relation' AND granted = false AND relation = 1046889774 AND mode = 'AccessExclusiveLock' AND pid <> pg_backend_pid()
LOG: (query) SET LOCAL statement_timeout = 100
LOG: (query) LOCK TABLE perm.permission_cache IN ACCESS SHARE MODE
LOG: (query) RESET statement_timeout
LOG: (query) CREATE TABLE repack.table_1046889774 WITH (oids = false) TABLESPACE pg_default AS SELECT id,company_uuid,user_uuid,NULL::integer AS "........pg.dropped.4........",value,cache_date,token_name FROM ONLY perm.permission_cache WITH NO DATA
LOG: (query) INSERT INTO repack.table_1046889774 SELECT id,company_uuid,user_uuid,NULL::integer AS "........pg.dropped.4........",value,cache_date,token_name FROM ONLY perm.permission_cache
LOG: (query) ALTER TABLE repack.table_1046889774 DROP COLUMN "........pg.dropped.4........"
LOG: (query) SELECT repack.disable_autovacuum('repack.table_1046889774')
LOG: (query) COMMIT
LOG: (query) CREATE UNIQUE INDEX index_1046889779 ON repack.table_1046889774 USING btree (id)
LOG: (query) CREATE UNIQUE INDEX index_1050932923 ON repack.table_1046889774 USING btree (user_uuid, token_name)
LOG: (query) SELECT repack.repack_apply($1, $2, $3, $4, $5, $6)
LOG:    (param:0) = SELECT * FROM repack.log_1046889774 ORDER BY id LIMIT $1
LOG:    (param:1) = INSERT INTO repack.table_1046889774 VALUES ($1.*)
LOG:    (param:2) = DELETE FROM repack.table_1046889774 WHERE (id) = ($1.id)
LOG:    (param:3) = UPDATE repack.table_1046889774 SET (id, company_uuid, user_uuid, value, cache_date, token_name) = ($2.id, $2.company_uuid, $2.user_uuid, $2.value, $2.cache_date, $2.token_name) WHERE (id) = ($1.id)
LOG:    (param:4) = DELETE FROM repack.log_1046889774 WHERE id IN (
LOG:    (param:5) = 1000
LOG: (query) SELECT repack.repack_apply($1, $2, $3, $4, $5, $6)
LOG:    (param:0) = SELECT * FROM repack.log_1046889774 ORDER BY id LIMIT $1
LOG:    (param:1) = INSERT INTO repack.table_1046889774 VALUES ($1.*)
LOG:    (param:2) = DELETE FROM repack.table_1046889774 WHERE (id) = ($1.id)
LOG:    (param:3) = UPDATE repack.table_1046889774 SET (id, company_uuid, user_uuid, value, cache_date, token_name) = ($2.id, $2.company_uuid, $2.user_uuid, $2.value, $2.cache_date, $2.token_name) WHERE (id) = ($1.id)
LOG:    (param:4) = DELETE FROM repack.log_1046889774 WHERE id IN (
LOG:    (param:5) = 1000
LOG: (query) SELECT pid FROM pg_locks WHERE locktype = 'virtualxid' AND pid <> pg_backend_pid() AND virtualtransaction = ANY($1)
LOG:    (param:0) = {}
LOG: (query) SAVEPOINT repack_sp1
LOG: (query) SET LOCAL statement_timeout = 100
LOG: (query) LOCK TABLE perm.permission_cache IN ACCESS EXCLUSIVE MODE
LOG: (query) RESET statement_timeout
LOG: (query) SELECT repack.repack_apply($1, $2, $3, $4, $5, $6)
LOG:    (param:0) = SELECT * FROM repack.log_1046889774 ORDER BY id LIMIT $1
LOG:    (param:1) = INSERT INTO repack.table_1046889774 VALUES ($1.*)
LOG:    (param:2) = DELETE FROM repack.table_1046889774 WHERE (id) = ($1.id)
LOG:    (param:3) = UPDATE repack.table_1046889774 SET (id, company_uuid, user_uuid, value, cache_date, token_name) = ($2.id, $2.company_uuid, $2.user_uuid, $2.value, $2.cache_date, $2.token_name) WHERE (id) = ($1.id)
LOG:    (param:4) = DELETE FROM repack.log_1046889774 WHERE id IN (
LOG:    (param:5) = 0
LOG: (query) SELECT repack.repack_swap($1)
LOG:    (param:0) = 1046889774
LOG: (query) COMMIT
LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
LOG: (query) SAVEPOINT repack_sp1
LOG: (query) SET LOCAL statement_timeout = 100
LOG: (query) LOCK TABLE perm.permission_cache IN ACCESS EXCLUSIVE MODE
LOG: (query) RESET statement_timeout
LOG: (query) SELECT repack.repack_drop($1, $2)
LOG:    (param:0) = 1046889774
LOG:    (param:1) = 4
LOG: (query) COMMIT
LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
LOG: (query) ANALYZE perm.permission_cache
LOG: (query) COMMIT
LOG: (query) SELECT pg_advisory_unlock($1, CAST(-2147483648 + $2::bigint AS integer))
LOG:    (param:0) = 16185446
LOG:    (param:1) = 1046889774

It doesn’t look like OIDs are surviving the copy like Laurenz surmised. For me, using pg_repack would be preferred since it can run unattended. Wouldn’t this be just as good as incremental VACUUMs for rewriting the table on-disk without OIDs? I could still be missing a detail.