Estou preparando um banco de dados PostgreSQL 9.5 legado para atualização.
Preciso remover OIDs de tabelas sem bloqueá-las por longos períodos de tempo. Desenvolvi uma estratégia que parece funcionar, mas gostaria que os especialistas avaliassem, já que estou planejando fazer coisas nas tabelas do sistema que geralmente são desaprovadas.
Antes de executar o pg_repack eu realizo estas modificações:
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
Até agora tudo bem. Posso inserir atualizações e excluir linhas, mas a estrutura da tabela no disco permanece inalterada. Vou precisar consertar isso.
Então, depois que essas modificações forem feitas, eu reempacotei a tabela com vanilla pg_repack. Isso copia os dados para novas tabelas no disco, sem oids.
Antes de começar a realizar essas operações on-line nos dados de produção, eu queria alguns especialistas nesse processo, já que se trata de algo de missão crítica.
Nas tabelas muito grandes, ficaremos neste estado de limbo por um longo período de tempo, onde pg_class e pg_attribute terão essas modificações forçadas enquanto pg_repack faz sua mágica.
Há algo com que se preocupar se inserir/atualizar/excluir parece estar funcionando?
Minhas inserções/atualizações/exclusões nas tabelas enquanto o pg_repack está em execução parecem funcionar bem.
Também tentei isso em tabelas que possuem tabelas de brinde anexadas. À primeira vista tudo parece estar em ordem.
Esta é uma operação destrutiva e não estou usando o canonical ALTER TABLE ... SET WITHOUT OIDS. Quero ter certeza de que não estou me preparando para um problema no futuro se houver algum detalhe que perdi (como durante a restauração de backup ou replicação).
Por favor, avise.
ATUALIZAR:
A excelente resposta de Laurenz me deu duas coisas adicionais nas quais não pensei: a verificação de dependência e a invalidação de cache/plano. Trabalhá-los no plano será importante. Executar pequenos vácuos para limpar as colunas obsoletas é uma boa posição de reserva, mas com base no que vejo acontecendo com o pg_repack, acho que isso resultará na mesma coisa... Os logs do pg_repack expõem todo o SQL envolvido na cópia do tabela e trocando definições:
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
Não parece que os OIDs estejam sobrevivendo à cópia como Laurenz supôs. Para mim, seria preferível usar pg_repack, pois ele pode ser executado sem supervisão. Isso não seria tão bom quanto VACUUMs incrementais para reescrever a tabela no disco sem OIDs? Ainda posso estar faltando um detalhe.