Início / dba / Perguntas / 332715
Accepted
Francis Ducharme
Asked: 2023-11-02 05:11:29 +0800 CST

Onde obter os logs de erros/informações sobre o que acontece ao confirmar várias instruções?

  • 772

Tive um problema com a falta de um índice em uma tabela e a única maneira que tive de reunir pistas foi desta forma:

BEGIN TRANSACTION;
-- OVER 100 DELETES
COMMIT;

No DBeaver, selecionei apenas a primeira linha e as exclusões subsequentes, executei isso. Em seguida, selecionei a linha de commit, executei-a e esperei até sentir que estava demorando muito.

Então o DBeaver me deu esta mensagem quando cancelei a transação:

SQL Error [57014]: ERROR: canceling statement due to user request
  Where: SQL statement "SELECT 1 FROM ONLY "schema"."table" x WHERE $1 OPERATOR(pg_catalog.=) "id_model" AND $2 OPERATOR(pg_catalog.=) "id_property1" AND $3 OPERATOR(pg_catalog.=) "id_property2" FOR KEY SHARE OF x"

id_modelé a cláusula WHERE para todos os DELETEs em questão. A consulta mencionada na mensagem definitivamente não é um código escrito por mim, em qualquer lugar de qualquer função/procedimento/gatilho que eu possa imaginar.

Tirei disso que um índice ausente nas tablecolunas id_property1era id_property2o problema, e aparentemente era.

Há algum log explicando o que está acontecendo "nos bastidores" quando o servidor está processando várias instruções enfileiradas na transação onde eu poderia ter encontrado essa instrução com mais facilidade?

Existem várias restrições de exclusão de chave estrangeira configuradas no modelo, então acho que quando os índices estão faltando, o servidor demora um pouco para encontrar a maneira correta de contornar.

postgresql

2 respostas

  1. Não posso comentar sobre o DBeaver. É um cliente genérico, não otimizado para Postgres. Tenho visto reclamações ocasionais sobre atritos aqui no dba.SE e SO.

    É responsabilidade do cliente mostrar mensagens ( ERROR, WARNING, NOTICE, ...) para cada comando. Não tenho certeza de como o DBeaver lida com isso. Talvez você precise executar cada um DELETEseparadamente (ainda permanecendo na mesma transação) para ver todas as mensagens.

    Você pode ajustar as configurações do Postgres client_min_messagese log_min_messagesajustar os níveis de mensagens que são enviadas para o cliente e para o log do banco de dados, respectivamente.

    Mas a SELECTdeclaração que você exibe não é uma “mensagem”. É um comando SQL simples. Você ou seu cliente devem tê-lo emitido. A sintaxe incomumente explícita ( OPERATOR(pg_catalog.=)em vez de apenas =) indica código gerado por máquina. Talvez fosse parte de uma mensagem maior mostrando o que a consulta cancelada estava esperando. Se um índice ausente faz com que uma instrução específica domine o tempo de execução, as chances de capturá-la em flagrante são boas ao interromper a execução. Ainda é coincidência.

    Além disso, embora um índice qualificado possa acelerar (muito) os comandos SQL, um índice "ausente" não é uma condição de erro (ou condição para qualquer nível de mensagem). Você não receberá uma mensagem do Postgres. A criação de índices fica a critério do usuário. Criar apenas os corretos é uma das principais tarefas do arquiteto/administrador de banco de dados. Existem inúmeras sutilezas nisso. As colunas alvo das restrições FK são indexadas por definição (é necessária uma restrição PK ou UNIQUE). As colunas de origem das restrições FK são as principais candidatas para um índice de árvore B simples. Ou, como diz o manual :

    Uma chave estrangeira deve fazer referência a colunas que sejam uma chave primária ou formem uma restrição exclusiva. Isso significa que as colunas referenciadas sempre possuem um índice (aquele subjacente à chave primária ou restrição exclusiva); portanto, verificar se uma linha de referência corresponde será eficiente. Como uma DELETElinha da tabela referenciada ou uma UPDATEcoluna referenciada exigirá uma varredura da tabela de referência em busca de linhas que correspondam ao valor antigo, geralmente é uma boa ideia indexar também as colunas de referência. Como isso nem sempre é necessário e há muitas opções disponíveis sobre como indexar, a declaração de uma restrição de chave estrangeira não cria automaticamente um índice nas colunas de referência.

    Monitore consultas lentas para obter indicações (não provas) das otimizações ausentes mais urgentes. Existem várias maneiras, principalmente: definir log_min_duration_statementou possivelmente log_min_duration_sampleamigos e, em seguida, estudar os logs do banco de dados gerados.

    E/ou use EXPLAIN (e todos os seus recursos) para diagnosticar planos de execução suspeitos.

    • 1
  2. Best Answer

    A estranha instrução que você vê foi executada a partir de um gatilho que implementa restrições de chave estrangeira no PostgreSQL.

    Existem duas maneiras de obter dados sobre essas declarações:

    1. Se você configurar shared_preload_libraries = 'pg_stat_statements'e pg_stat_statements.track = allreiniciar o PostgreSQL, pg_stat_statements também coletará estatísticas sobre essas instruções "ocultas". Você pode visualizar esses dados com a pg_stat_statementsvisualização fornecida pela extensão de mesmo nome.

    2. Se você definir shared_preload_libraries = 'auto_explain'e auto_explain.log_nested_statements = onreiniciar auto_explain.log_min_duration = '500ms'o PostgreSQL, obterá o plano de execução de todas as instruções que são executadas por mais de meio segundo registradas, incluindo essas instruções "ocultas".

    • 1

relate perguntas