ProHoster > Blog > administração > Antipadrões do PostgreSQL: alterar dados ignorando um gatilho

Antipadrões do PostgreSQL: alterar dados ignorando um gatilho

Mais cedo ou mais tarde, muitos se deparam com a necessidade de consertar massivamente algo nos registros da tabela. eu já diga-me como fazer melhor, e como - é melhor não fazer isso. Hoje falarei sobre o segundo aspecto da atualização em massa - sobre gatilhos.

Por exemplo, em uma mesa em que você precisa consertar algo, um gatilho maligno trava ON UPDATE, transferindo todas as alterações para alguns agregados. E você precisa atualizar tudo (inicializar um novo campo, por exemplo) com muito cuidado para que esses agregados não sejam afetados.

Vamos apenas desabilitar os gatilhos!

BEGIN;
  ALTER TABLE ... DISABLE TRIGGER ...;
  UPDATE ...; -- тут долго-долго
  ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;

Na verdade, isso é tudo - tudo está pendurado.

Porque ALTER TABLE impõe Acesso Exclusivo- um bloqueio sob o qual ninguém está correndo em paralelo, mesmo um simples SELECT, não poderá ler nada da tabela. Ou seja, até que essa transação termine, todos que quiserem "apenas ler" vão esperar. E nós lembramos disso UPDATE temos um longo...

Vamos desligá-lo rapidamente e, em seguida, ligá-lo rapidamente!

BEGIN;
  ALTER TABLE ... DISABLE TRIGGER ...;
COMMIT;

UPDATE ...;

BEGIN;
  ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;

Aqui a situação já é melhor, o tempo de espera é bem menor. Mas apenas dois problemas estragam toda a beleza:

  • ALTER TABLE ele próprio espera por todas as outras operações na mesa, incluindo as longas SELECT
  • Enquanto o gatilho estiver desligado, "voar por" qualquer mudança na mesa, nem mesmo a nossa. E não entrará nos agregados, embora devesse. Dificuldade!

Gerenciando variáveis ​​de sessão

Então, na versão anterior, nos deparamos com um ponto fundamental - precisamos de alguma forma ensinar o gatilho a distinguir “nossas” mudanças na tabela de “não nossas”. “Ours” são ignorados como estão, mas em “not ours” eles são acionados. Para isso você pode usar variáveis ​​de sessão.

session_replication_role

Nós lemos manual:

O mecanismo de gatilho também é afetado pela variável de configuração session_replication_role. Habilitado sem instruções adicionais (padrão), os gatilhos serão acionados quando a função de replicação for "origem" (padrão) ou "local". Gatilhos habilitados especificando ENABLE REPLICA, só funcionará se modo de sessão atual - "réplica" e gatilhos habilitados especificando ENABLE ALWAYS, funcionará independentemente do modo de replicação atual.

Vou enfatizar especialmente que a configuração não se aplica a todos de uma vez, pois ALTER TABLE, mas apenas para nossa conexão especial separada. No total, para que nenhum gatilho de aplicativo funcione:

SET session_replication_role = replica; -- выключили триггеры
UPDATE ...;
SET session_replication_role = DEFAULT; -- вернули в исходное состояние

Condição dentro do gatilho

Mas a opção acima funciona para todos os gatilhos de uma vez (ou você precisa "alternar" os gatilhos com antecedência que não deseja desativar). E se precisarmos "desligar" um gatilho específico?

Isso vai nos ajudar variável de sessão "usuário":

Os nomes dos parâmetros de extensão são escritos da seguinte forma: o nome da extensão seguido por um ponto e, em seguida, o próprio nome do parâmetro, semelhante aos nomes de objetos completos em SQL. Por exemplo: plpgsql.variable_conflict.
Como as opções fora do sistema podem ser definidas em processos que não carregam o módulo de extensão apropriado, o PostgreSQL aceita valores para quaisquer nomes com dois componentes.

Primeiro, finalizamos o gatilho, algo assim:

BEGIN
    -- процессу конвертации можно делать все
    IF current_setting('mycfg.my_table_convert_process') = 'TRUE' THEN
        IF TG_OP IN ('INSERT', 'UPDATE') THEN
            RETURN NEW;
        ELSE
            RETURN OLD;
        END IF;
    END IF;
...

A propósito, isso pode ser feito "com fins lucrativos", sem bloqueio, por meio de CREATE OR REPLACE para a função de gatilho. E então, na conexão especial, ativamos "nossa" variável:


SET mycfg.my_table_convert_process = 'TRUE';
UPDATE ...;
SET mycfg.my_table_convert_process = ''; -- вернули в исходное состояние

Você conhece outras formas? Compartilhe nos comentários.

Fonte: habr.com

Adicionar um comentário