2

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.

Improve this question
0

1 Answer 1

Reset to default
2

You want to avoid long down time caused by the table rewrite of ALTER TABLE ... SET WITHOUT OIDS, correct?

The safe solution would be to run ALTER TABLE ... SET WITHOUT OIDS and take the down time. Your way is more dangerous, but it avoids a long ACCESS EXCLUSIVE lock on the table.

What you are doing is equivalent to what PostgreSQL did for ALTER TABLE ... SET WITHOUT OIDS before commit 6d1e361852, except that you forgot to take an ACCESS EXCLUSIVE lock on the table, that you didn't check for dependencies on oid and that you don't invalidate the plans and cached metadata information that other sessions have for this table.

To avoid these problems, do it as follows:

  • First, make sure that nothing depends on the oid column:

    BEGIN;

-- would cause an error if there were dependencies on "oid"
ALTER TABLE some_schema.a_very_large_table_with_oids SET WITHOUT OIDS;
-- hit Ctrl+C to interrupt the statement

ROLLBACK;

  • Then, perform the changes you suggest, but lock the table and invalidate all plans:

    BEGIN;

-- perform a no-op change that locks the table and invalidates plans
ALTER TABLE some_schema.a_very_large_table_with_oids
   ALTER id SET NOT NULL;

UPDATE pg_class SET relhasoids = false
WHERE oid = 'some_schema.a_very_large_table_with_oids'::regclass;

DELETE FROM pg_attribute
WHERE attrelid = 'some_schema.a_very_large_table_with_oids'::regclass
  AND attname = 'oid';

COMMIT;

  • Finally, you can use pg_repack to rewrite the table without taking a long ACCESS EXCLUSIVE lock.

As an alternative to pg_repack, you could update the table in chunks so that the individual rows get rewritten. Make sure to VACUUM between the batches:

UPDATE some_schema.a_very_large_table_with_oids
SET id = id
WHERE id BETWEEN 1 AND 1000000;

VACUUM some_schema.a_very_large_table_with_oids;

UPDATE some_schema.a_very_large_table_with_oids
SET id = id
WHERE id BETWEEN 1000001 AND 2000000;

VACUUM some_schema.a_very_large_table_with_oids;

...

After that, the table should be fine. Still, perform a test upgrade and test well.

Some background information about why ALTER TABLE ... SET WITHOUT OIDS rewrites the table, unlike ALTER TABLE ... DROP COLUMN ...:

Before version 8.4, PostgreSQL did not require a table rewrite for either operation. But there was a reason that ALTER TABLE ... SET WITHOUT OIDS was changed to rewrite the table; see the discussions in this bug report and this ensuing thread. The very problems mentioned there wouldn't affect you after a pg_upgrade, but there may be other subtle problems if you have a table that physically has OIDs in its tuples where PostgreSQL doesn't expect any. That is the reason why ALTER TABLE ... SET WITHOUT OIDS was changed to rewrite the table.

Your catalog surgery will leave the table in a state like ALTER TABLE ... SET WITHOUT OIDS in PostgreSQL 8.3 and earlier. Now the pg_upgrade documentation mentions that upgrade is supported for 9.2 and later, and before commit e469f0aaf3 it mentioned support for 8.4 and above. While there is no explicit mention in the historical documentation that tables whose OIDs were dropped in your fashion constitute a problem, this case is obviously unexpected, not tested at all and not supported. You could end up having interesting problems in the future. I would certainly not risk that for my database.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.