Início / dba / Perguntas / 333700
Accepted
user2458080
Asked: 2023-11-29 06:53:10 +0800 CST

Usando pg_repack para remover OIDs de tabelas no PostgreSQL

  • 772

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.

postgresql

1 respostas

  1. Best Answer

    Você deseja evitar um longo tempo de inatividade causado pela reescrita da tabela ALTER TABLE ... SET WITHOUT OIDS, correto?

    A solução segura seria executar ALTER TABLE ... SET WITHOUT OIDSe diminuir o tempo de inatividade. Seu caminho é mais perigoso, mas evita uma longa ACCESS EXCLUSIVEtrava na mesa.

    O que você está fazendo é equivalente ao que o PostgreSQL fez ALTER TABLE ... SET WITHOUT OIDSantes do commit 6d1e361852 , exceto que você esqueceu de bloquear ACCESS EXCLUSIVEa tabela, não verificou dependências oide não invalidou os planos e informações de metadados armazenados em cache que outras sessões têm para esta tabela.

    Para evitar esses problemas, faça o seguinte:

    • Primeiro, certifique-se de que nada depende da oidcoluna:

      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;

    • Em seguida, faça as alterações sugeridas, mas bloqueie a tabela e invalide todos os planos:

      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;

    • Finalmente, você pode usar pg_repack para reescrever a tabela sem precisar de um longo ACCESS EXCLUSIVEbloqueio.

    Como alternativa ao pg_repack, você pode atualizar a tabela em partes para que as linhas individuais sejam reescritas. Certifique-se de fazer VACUUMentre os lotes:

    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;

...

    Depois disso, a mesa deve ficar bem. Ainda assim, execute uma atualização de teste e teste bem.

    Algumas informações básicas sobre por que ALTER TABLE ... SET WITHOUT OIDSreescreve a tabela, ao contrário de ALTER TABLE ... DROP COLUMN ...:

    Antes da versão 8.4, o PostgreSQL não exigia reescrita de tabela para nenhuma das operações. Mas houve uma razão que ALTER TABLE ... SET WITHOUT OIDSfoi alterada para reescrever a tabela; veja as discussões neste relatório de bug e neste tópico subsequente . Os mesmos problemas mencionados não afetariam você após a pg_upgrade, mas pode haver outros problemas sutis se você tiver uma tabela que tenha fisicamente OIDs em suas tuplas onde o PostgreSQL não espera nenhum. Essa é a razão pela qual ALTER TABLE ... SET WITHOUT OIDSfoi alterado para reescrever a tabela.

    Sua cirurgia de catálogo deixará a tabela em um estado como ALTER TABLE ... SET WITHOUT OIDSno PostgreSQL 8.3 e versões anteriores. Agora, a pg_upgradedocumentação menciona que a atualização é suportada para 9.2 e posterior, e antes do commit e469f0aaf3 mencionou suporte para 8.4 e superior. Embora não haja nenhuma menção explícita na documentação histórica de que as tabelas cujos OIDs foram eliminados da sua maneira constituem um problema, este caso é obviamente inesperado, não foi testado e não é suportado. Você pode acabar tendo problemas interessantes no futuro. Eu certamente não arriscaria isso no meu banco de dados.

    • 2

relate perguntas