Postgres Pro
Downloads
Home   >   mailing lists

Removing oids with pg_repack - Mailing list pgsql-general

From CG
Subject Removing oids with pg_repack
Date
Msg-id 1375662821.3508272.1700592084461@mail.yahoo.com
Whole thread Raw
Responses Re: Removing oids with pg_repack
Re: Removing oids with pg_repack
List pgsql-general
Tree view
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 -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) = wipe_oid_test
INFO: repacking table "public.wipe_oid_test"
LOG: (query) SELECT pg_try_advisory_lock($1, CAST(-2147483648 + $2::bigint AS integer))
LOG: (param:0) = 16185446
LOG: (param:1) = 2273648077
LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
LOG: (query) SET LOCAL statement_timeout = 100
LOG: (query) LOCK TABLE public.wipe_oid_test 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) = 2273648077
LOG: (query) SELECT indexrelid, repack.repack_indexdef(indexrelid, indrelid, $2, FALSE)  FROM pg_index WHERE indrelid = $1 AND indisvalid
LOG: (param:0) = 2273648077
LOG: (param:1) = (null)
LOG: (query) SELECT repack.conflicted_triggers($1)
LOG: (param:0) = 2273648077
LOG: (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_trigger
LOG: (query) SELECT repack.disable_autovacuum('repack.log_2273648077')
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 = 2273648077 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) = 15246
LOG: (param:1) = pg_repack
LOG: (query) DELETE FROM repack.log_2273648077
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) SET LOCAL statement_timeout = 100
LOG: (query) LOCK TABLE public.wipe_oid_test IN ACCESS SHARE MODE
LOG: (query) RESET statement_timeout
LOG: (query) CREATE TABLE repack.table_2273648077 WITH (oids = false) TABLESPACE pg_default AS SELECT k,v FROM ONLY public.wipe_oid_test WITH NO DATA
LOG: (query) INSERT INTO repack.table_2273648077 SELECT k,v FROM ONLY public.wipe_oid_test
LOG: (query) SELECT repack.disable_autovacuum('repack.table_2273648077')
LOG: (query) COMMIT
LOG: (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 $1
LOG: (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) = 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 public.wipe_oid_test 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_2273648077 ORDER BY id LIMIT $1
LOG: (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) = 0
LOG: (query) SELECT repack.repack_swap($1)
LOG: (param:0) = 2273648077
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 public.wipe_oid_test IN ACCESS EXCLUSIVE MODE
LOG: (query) RESET statement_timeout
LOG: (query) SELECT repack.repack_drop($1, $2)
LOG: (param:0) = 2273648077
LOG: (param:1) = 4
LOG: (query) COMMIT
LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
LOG: (query) ANALYZE public.wipe_oid_test
LOG: (query) COMMIT
LOG: (query) SELECT pg_advisory_unlock($1, CAST(-2147483648 + $2::bigint AS integer))
LOG: (param:0) = 16185446
LOG: (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




pgsql-general by date:

Previous
From: Joan Pujol
Date:
Subject: Re: Retrieve results in PostgreSQL stored procedure allowing query parallelism
Next
From: "David G. Johnston"
Date:
Subject: Re: Retrieve results in PostgreSQL stored procedure allowing query parallelism