Pessoal estou com um problema aqui na empresa, nosso banco de dados é Postgres e temos implementado trigger e function para gerar log de alteração dos registros temos apenas uma tabela que guarda em formato XML tudo que é alterado em todas as tabela do banco, acontece que como a quantidade de dados é imensa durante uma integração de dados entre filiais, essa tabela de log se torna extremamente lenta deixando o banco até 8 vezes mais lento, o que pode ser feito para melhorar o desempenho ??
Segue abaixo a função que gera o log.
[code]-- Function: geralog()
– DROP FUNCTION geralog();
CREATE OR REPLACE FUNCTION geralog()
RETURNS trigger AS
$BODY$
DECLARE
recnew RECORD;
recold RECORD;
auxcursor refcursor;
auxrecord RECORD;
ischave BOOLEAN;
colunaaux TEXT;
valor1 TEXT;
valor2 TEXT;
alteracoes TEXT;
tipooperacao TEXT;
chaveaux TEXT;
loginjalis TEXT;
estacaojalis INTEGER;
datahorajalis TIMESTAMP WITHOUT TIME ZONE;
filialorigemjalis INTEGER;
filialdestinojalis INTEGER;
ignorada varchar;
BEGIN
Raise info ‘--------------------------------------------Iniciando%’, clock_timestamp();
–DESCOMENTE a linha abaixo para DESATIVAR TODAS AS TRIGGERS DE LOG
–RETURN new;
–evita gerar log de tabelas contidas em tabelaignoradalog - by jr
Raise info ‘evita gerar log de tabelas contidas em tabelaignoradalog%’, clock_timestamp();
SELECT til_tabela into ignorada from tabelaignoradalog where til_tabela = ‘’ || TG_TABLE_NAME || ‘’;
IF FOUND THEN
RETURN new;
END IF;
–busca as colunas da tabela
Raise info ‘busca as colunas da tabela % %’, TG_TABLE_NAME, clock_timestamp();
OPEN auxCursor
FOR
SELECT a.attname AS coluna
,a.attnum IN (SELECT UNNEST(c.conkey)) AS chave
FROM pg_catalog.pg_class t
LEFT JOIN pg_catalog.pg_constraint c on t.oid = c.conrelid
JOIN pg_attribute a ON t.oid = a.attrelid AND a.attnum > 0 AND a.atttypid > 0
WHERE t.oid::REGCLASS = CAST(TG_TABLE_NAME AS REGCLASS);
–busca as informacoes de conexao do usuario
Raise info ‘busca as informacoes de conexao do usuario%’, clock_timestamp();
SELECT usc_login
,usc_estacao
,usc_datahora
,usc_filial
INTO loginjalis
,estacaojalis
,datahorajalis
,filialorigemjalis
FROM usuarioconectado
WHERE usc_oid = pg_backend_pid();
Raise info ‘inicia loop para verificar os campos alterados%’, clock_timestamp();
–loop para verificar os campos alterados
–gera um xml com os valores alterados
–estrutura xml: “ABCXXXYYY…”
alteracoes = ‘’;
chaveaux = ‘’;
filialdestinojalis = NULL;
tipooperacao = substr(TG_OP, 1, 1);
LOOP
FETCH auxcursor INTO auxrecord;
IF auxrecord IS NULL THEN
EXIT;
END IF;
colunaaux = auxrecord.coluna;
ischave = auxrecord.chave;
–Raise info ‘operacao UPDATE%’, clock_timestamp();
–operacao UPDATE
IF TG_OP = ‘UPDATE’ THEN
recnew = cast(NEW as RECORD);
recold = cast(OLD as RECORD);
EXECUTE 'SELECT ($1).' || colunaaux || '::text'
INTO valor1
USING recnew;
EXECUTE 'SELECT ($1).' || colunaaux || '::text'
INTO valor2
USING recold;
–Raise info ‘rmazena somente os campos alterados%’, clock_timestamp();
–armazena somente os campos alterados
IF valor1 != valor2 THEN
alteracoes = alteracoes || ‘’ || colunaaux || ‘’ || COALESCE(valor1, ‘NULL’) || ‘’ || valor2 || ‘’;
END IF;
–Raise info ‘armazena a filial de destino (filial do comando)%’, clock_timestamp();
–armazena a filial de destino (filial do comando)
IF colunaaux = ‘fil_codigo’ THEN
filialdestinojalis = recnew.fil_codigo;
END IF;
–Raise info ‘operacao INSERT%’, clock_timestamp();
–operacao INSERT
ELSIF TG_OP = ‘INSERT’ THEN
recNew = cast(NEW as RECORD);
EXECUTE 'SELECT ($1).' || colunaaux || '::text'
INTO valor1
USING recnew;
–Raise info ‘armazena todos os campos%’, clock_timestamp();
–armazena todos os campos
alteracoes = alteracoes || ‘’ || colunaaux || ‘’ || COALESCE(valor1, ‘NULL’) || ‘’;
–Raise info ‘armazena a filial de destino (filial do comando)%’, clock_timestamp();
–armazena a filial de destino (filial do comando)
IF colunaaux = ‘fil_codigo’ THEN
filialdestinojalis = recnew.fil_codigo;
END IF;
–Raise info ‘operacao DELETE%’, clock_timestamp();
–operacao DELETE
ELSE
recold = cast(OLD as RECORD);
EXECUTE 'SELECT ($1).' || colunaaux || '::text'
INTO valor1
USING recold;
–Raise info ‘armazena todos os campos%’, clock_timestamp();
–armazena todos os campos
alteracoes = alteracoes || ‘’ || colunaaux || ‘’ || COALESCE(valor1, ‘NULL’) || ‘’;
–Raise info ‘armazena a filial de destino (filial do comando)%’, clock_timestamp();
–armazena a filial de destino (filial do comando)
IF colunaaux = ‘fil_codigo’ THEN
filialdestinojalis = recold.fil_codigo;
END IF;
END IF;
–Raise info ‘armazena as chaves da tabela%’, clock_timestamp();
–armazena as chaves da tabela
IF ischave THEN
IF length(chaveaux) > 0 THEN
chaveaux = chaveaux || ‘,’;
END IF;
chaveaux = chaveaux || colunaaux || '=' || valor1;
END IF;
END LOOP;
Raise info ‘Fim do loop’;
Raise info ‘alterações %’,alteracoes;
Raise info ‘caso nao tenha sido encontrado usuario na tabela usuarioconectado deve-se usar a role da conexao%’, clock_timestamp();
–caso nao tenha sido encontrado usuario na tabela usuarioconectado deve-se usar a role da conexao
IF loginjalis IS NULL THEN
Raise info ‘entro para pesquisar pg_stat_get_activity’;
BEGIN
SELECT b.rolname
INTO loginjalis
FROM pg_stat_get_activity(NULL) a
,pg_authid b
WHERE a.procpid = pg_backend_pid()
AND a.usesysid = b.oid;
EXCEPTION WHEN OTHERS THEN
Raise info ‘nova pesquisa entro para pesquisar pg_stat_get_activity’;
SELECT b.rolname
INTO loginjalis
FROM pg_stat_get_activity(NULL) a
,pg_authid b
WHERE a.pid = pg_backend_pid()
AND a.usesysid = b.oid;
END;
END IF;
Raise info ‘Finalizou consulta ao pg_stat_get_activity’;
IF length(alteracoes) > 0 THEN
alteracoes = ‘’ || alteracoes || ‘’;
Raise info ‘insert armazena o log%’,clock_timestamp();
–armazena o log
EXECUTE ’ INSERT INTO lograstreabilidade ’
|| ’ (ras_filialorigem ’
|| ’ ,ras_filialdestino ’
|| ’ ,ras_tabela ’
|| ’ ,ras_operacao ’
|| ’ ,ras_tipooperacao ’
|| ’ ,ras_datahora ’
|| ’ ,ras_loginusuario ’
|| ’ ,ras_chave) ’
|| ’ VALUES ( ’
|| quote_nullable(filialorigemjalis) || ‘,’ --filial de origem
|| quote_nullable(filialdestinojalis) || ‘,’ --filial de destino
|| ‘’’’ || TG_TABLE_NAME || ‘’’,’ --nome da tabela
|| quote_literal(alteracoes) || ‘,’ --campos alterados
|| ‘’’’ || tipooperacao || ‘’’,’ --tipo de operacao (I=insert, U=update, D=delete)
|| ‘’’’ || COALESCE(datahorajalis, CURRENT_TIMESTAMP) || ‘’’,’ --data/hora da alteracao
|| ‘’’’ || COALESCE(loginjalis, ‘NULL’) || ‘’’,’ --usuario da alteracao (ou a role, caso nao esteja logado no jalis)
|| quote_literal(chaveaux) --chave da tabela
|| ') ';
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION geralog()
OWNER TO thread;
[/code]