I have a very large PostgreSQL 9.5 database that still has very large tables
with oids. I'm trying to get rid of the oids with as little downtime as
possible so I can prep the database for upgrade past PostgreSQL 11. I had a
wild idea to mod pg_repack to write a new table without oids. I think it almost
works.
To test out my idea I made a new table wipe_oid_test with oids. I filled it
with a few rows of data.
Next I make this modification:
CREATE OR REPLACE FUNCTION repack.get_storage_param(oid) RETURNS TEXT
AS$$SELECT string_agg(param, ', ')FROM ( -- table storage parameter
SELECT unnest(reloptions) as param FROM pg_class WHERE oid = $1 UNION
ALL -- TOAST table storage parameter SELECT ('toast.' ||
unnest(reloptions)) as param FROM ( SELECT reltoastrelid from
pg_class where oid = $1 ) as t, pg_class as c WHERE c.oid =
t.reltoastrelid UNION ALL /*-- table oid SELECT 'oids = ' ||
CASE WHEN relhasoids THEN 'true' ELSE 'false' END
FROM pg_class WHERE oid = $1*/
SELECT 'oids = false' --my modification
) as t$$LANGUAGE sql STABLE STRICT;
Then I run pg_repack:
$ pg_repack -d mydata -Upostgres -t wipe_oid_test -eLOG: (query) SET
search_path TO pg_catalog, pg_temp, publicLOG: (query) SET search_path TO
pg_catalog, pg_temp, publicLOG: (query) select repack.version(),
repack.version_sql()LOG: (query) SET statement_timeout = 0LOG: (query) SET
search_path = pg_catalog, pg_temp, publicLOG: (query) SET client_min_messages =
warningLOG: (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.schemanameLOG:
(param:0) = (null)LOG: (param:1) = wipe_oid_testINFO: repacking table
"public.wipe_oid_test"LOG: (query) SELECT pg_try_advisory_lock($1,
CAST(-2147483648 + $2::bigint AS integer))LOG: (param:0) = 16185446LOG:
(param:1) = 2273648077LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTEDLOG:
(query) SET LOCAL statement_timeout = 100LOG: (query) LOCK TABLE
public.wipe_oid_test IN ACCESS EXCLUSIVE MODELOG: (query) RESET
statement_timeoutLOG: (query) SELECT pg_get_indexdef(indexrelid) FROM pg_index
WHERE indrelid = $1 AND NOT indisvalidLOG: (param:0) = 2273648077LOG: (query)
SELECT indexrelid, repack.repack_indexdef(indexrelid, indrelid, $2, FALSE)
FROM pg_index WHERE indrelid = $1 AND indisvalidLOG: (param:0) =
2273648077LOG: (param:1) = (null)LOG: (query) SELECT
repack.conflicted_triggers($1)LOG: (param:0) = 2273648077LOG: (query) CREATE
TYPE repack.pk_2273648077 AS (k text)LOG: (query) CREATE TABLE
repack.log_2273648077 (id bigserial PRIMARY KEY, pk repack.pk_2273648077, row
public.wipe_oid_test)LOG: (query) CREATE TRIGGER repack_trigger AFTER INSERT OR
DELETE OR UPDATE ON public.wipe_oid_test FOR EACH ROW EXECUTE PROCEDURE
repack.repack_trigger('INSERT INTO repack.log_2273648077(pk, row) VALUES( CASE
WHEN $1 IS NULL THEN NULL ELSE (ROW($1.k)::repack.pk_2273648077) END, $2)')LOG:
(query) ALTER TABLE public.wipe_oid_test ENABLE ALWAYS TRIGGER
repack_triggerLOG: (query) SELECT
repack.disable_autovacuum('repack.log_2273648077')LOG: (query) BEGIN ISOLATION
LEVEL READ COMMITTEDLOG: (query) SELECT pg_backend_pid()LOG: (query) SELECT pid
FROM pg_locks WHERE locktype = 'relation' AND granted = false AND relation =
2273648077 AND mode = 'AccessExclusiveLock' AND pid <> pg_backend_pid()LOG:
(query) COMMITLOG: (query) BEGIN ISOLATION LEVEL SERIALIZABLELOG: (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) = 15246LOG: (param:1) = pg_repackLOG:
(query) DELETE FROM repack.log_2273648077LOG: (query) SELECT pid FROM pg_locks
WHERE locktype = 'relation' AND granted = false AND relation = 2273648077 AND
mode = 'AccessExclusiveLock' AND pid <> pg_backend_pid()LOG: (query) SET LOCAL
statement_timeout = 100LOG: (query) LOCK TABLE public.wipe_oid_test IN ACCESS
SHARE MODELOG: (query) RESET statement_timeoutLOG: (query) CREATE TABLE
repack.table_2273648077 WITH (oids = false) TABLESPACE pg_default AS SELECT k,v
FROM ONLY public.wipe_oid_test WITH NO DATALOG: (query) INSERT INTO
repack.table_2273648077 SELECT k,v FROM ONLY public.wipe_oid_testLOG: (query)
SELECT repack.disable_autovacuum('repack.table_2273648077')LOG: (query)
COMMITLOG: (query) CREATE UNIQUE INDEX index_2273648083 ON
repack.table_2273648077 USING btree (k)LOG: (query) SELECT
repack.repack_apply($1, $2, $3, $4, $5, $6)LOG: (param:0) = SELECT * FROM
repack.log_2273648077 ORDER BY id LIMIT $1LOG: (param:1) = INSERT INTO
repack.table_2273648077 VALUES ($1.*)LOG: (param:2) = DELETE FROM
repack.table_2273648077 WHERE (k) = ($1.k)LOG: (param:3) = UPDATE
repack.table_2273648077 SET (k, v) = ($2.k, $2.v) WHERE (k) = ($1.k)LOG:
(param:4) = DELETE FROM repack.log_2273648077 WHERE id IN (LOG: (param:5) =
1000LOG: (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_sp1LOG: (query) SET LOCAL statement_timeout = 100LOG:
(query) LOCK TABLE public.wipe_oid_test IN ACCESS EXCLUSIVE MODELOG: (query)
RESET statement_timeoutLOG: (query) SELECT repack.repack_apply($1, $2, $3, $4,
$5, $6)LOG: (param:0) = SELECT * FROM repack.log_2273648077 ORDER BY id LIMIT
$1LOG: (param:1) = INSERT INTO repack.table_2273648077 VALUES ($1.*)LOG:
(param:2) = DELETE FROM repack.table_2273648077 WHERE (k) = ($1.k)LOG:
(param:3) = UPDATE repack.table_2273648077 SET (k, v) = ($2.k, $2.v) WHERE (k)
= ($1.k)LOG: (param:4) = DELETE FROM repack.log_2273648077 WHERE id IN (LOG:
(param:5) = 0LOG: (query) SELECT repack.repack_swap($1)LOG: (param:0) =
2273648077LOG: (query) COMMITLOG: (query) BEGIN ISOLATION LEVEL READ
COMMITTEDLOG: (query) SAVEPOINT repack_sp1LOG: (query) SET LOCAL
statement_timeout = 100LOG: (query) LOCK TABLE public.wipe_oid_test IN ACCESS
EXCLUSIVE MODELOG: (query) RESET statement_timeoutLOG: (query) SELECT
repack.repack_drop($1, $2)LOG: (param:0) = 2273648077LOG: (param:1) = 4LOG:
(query) COMMITLOG: (query) BEGIN ISOLATION LEVEL READ COMMITTEDLOG: (query)
ANALYZE public.wipe_oid_testLOG: (query) COMMITLOG: (query) SELECT
pg_advisory_unlock($1, CAST(-2147483648 + $2::bigint AS integer))LOG:
(param:0) = 16185446LOG: (param:1) = 2273648077
But PostgreSQL still thinks that the table has oids:
mydata=# \d+ wipe_oid_test Table "public.wipe_oid_test"
Column | Type | Modifiers | Storage | Stats target | Description
--------+------+-----------+----------+--------------+------------- k |
text | not null | extended | | v | text | |
extended | | Indexes: "wipe_oid_test_pkey" PRIMARY KEY, btree
(k)Has OIDs: yes
mydata=# select oid,* from wipe_oid_test; oid | k | v -----+---+--- 0 | 1 | 2
0 | 3 | 4 0 | a | b 0 | c | d(4 rows)
I can modify pg_class and set relhasoids = false, but it isn't actually
eliminating the oid column. `\d+` will report not report that it has oids, but
the oid column is still present and returns the same result before updating
pg_class.
So I'm definitely missing something. I really need a point in the right
direction.... Please help! ;)
CG
