Antipadrões do PostgreSQL: alterando dados contornando o gatilho

Mais cedo ou mais tarde, muitos se deparam com a necessidade de corrigir algo em massa nas entradas de uma tabela. Eu já falei sobre como fazer isso melhor, e como — melhor não fazer. Hoje falarei sobre o segundo aspecto da atualização em massa — o acionamento de gatilhos.

Por exemplo, em uma tabela, onde você precisa corrigir algo, há um gatilho maligno ON UPDATE, que transfere todas as alterações para alguns agregados. E você precisa atualizar tudo (inicializar um novo campo, por exemplo) de forma cuidadosa para que esses agregados não sejam afetados.

Vamos simplesmente desativar os gatilhos!

BEGIN;
  ALTER TABLE ... DISABLE TRIGGER ...;
  UPDATE ...; -- aqui leva um bom tempo
  ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;

Na verdade, isso é tudo — tudo já está pendurado.

Porque ALTER TABLE impõe AccessExclusive- a bloqueio, sob o qual ninguém consegue executar em paralelo, nem mesmo uma simples SELECT, nada pode ser lido da tabela. Ou seja, enquanto essa transação não terminar, todos que quiserem apenas “ler” estarão esperando. E lembramos que ATUALIZAÇÃO temos um longo…

Então vamos desligar rapidamente, depois ligamos rapidamente!

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

UPDATE ...;

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

Aqui a situação já é melhor, o tempo de espera é significativamente menor. Mas há apenas dois problemas que arruínam toda a beleza:

• ALTER TABLE espera todas as outras operações na tabela, incluindo as longas SELECT
• Enquanto o gatilho estiver desligado, qualquer modificação na tabela, mesmo que não seja nossa, “passará” despercebida. E não irá para os agregados, apesar de que deveria. Que pena!

Gerenciamento de variáveis de sessão

Então, na variante anterior, encontramos um ponto fundamental — precisamos de algum modo ensinar o gatilho a diferenciar as alterações “nossas” na tabela das “não nossas”. Aceitar as “nossas” como estão, e acionar para as “não nossas”. Para isso, podemos utilizar variáveis de sessão.

session_replication_role

Lemos manual:

O mecanismo de acionamento dos gatilhos também é influenciado pela variável de configuração session_replication_role. Gatilhos habilitados sem indicações adicionais (por padrão) serão acionados quando o papel de replicação é “origin” (por padrão) ou “local”. Gatilhos habilitados com a indicação ENABLE REPLICA, serão acionados apenas se o modo atual da sessão é “replica”, e gatilhos habilitados com a indicação ENABLE ALWAYS, serão acionados independentemente do modo atual de replicação.

Ressalto que a configuração não se refere a todos de uma vez, como ALTER TABLE, mas apenas para a nossa conexão especial separada. Portanto, para evitar que quaisquer gatilhos de aplicativo sejam acionados:

SET session_replication_role = replica; -- desativamos os gatilhos
UPDATE ...;
SET session_replication_role = DEFAULT; -- retornamos ao estado original

Condição dentro do gatilho

Mas a opção acima funciona para todos os gatilhos ao mesmo tempo (ou é necessário 'alterar' previamente os gatilhos que não queremos desativar). E se precisarmos desativar um gatilho específico?

Para isso, podemos contar com variável de sessão 'usuário':

Os nomes dos parâmetros de extensões são escritos da seguinte forma: nome da extensão, ponto e, em seguida, o nome do parâmetro propriamente dito, semelhante aos nomes completos de objetos em SQL. Por exemplo: plpgsql.variable_conflict.
Como os parâmetros não sistêmicos podem ser definidos em processos que não carregam o módulo de extensão correspondente, o PostgreSQL aceita valores para qualquer nome com duas partes.

Primeiro, aprimoramos o gatilho, mais ou menos assim:

BEGIN
    -- O processo de conversão pode fazer tudo
    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 'ao vivo', sem bloqueios, através de CREATE OR REPLACE para a função de gatilho. E então, na conexão especial, ativamos a nossa variável:

SET mycfg.my_table_convert_process = 'TRUE';
UPDATE ...;
SET mycfg.my_table_convert_process = ''; -- retornamos ao estado original

Conhece outros métodos? Compartilhe nos comentários.

Fonte: habr.com