Início / dba / Perguntas / 104994
Accepted
arthur
Asked: 2015-06-25 03:44:28 +0800 CST

Inserções persistentes em um UDF mesmo se a função for abortada

  • 772

Eu tenho um UDF bastante complicado (move-se em torno de um monte de tabelas, bem como cria um monte de novas) onde podem ocorrer várias interrupções. Antes de cada operação, gostaria de registrar a hora em que uma operação ocorre e a própria consulta. A UDF se parece com o seguinte então:

log function_start

log sql1
execute sql1

log sql2
execute sql2

...

log sqlN
execute sqlN

log function_end

Cada declaração de log significa inserir um novo registro na seguinte tabela:

CREATE TABLE backup_logs
(
  id serial NOT NULL,
  t timestamp with time zone default now(),
  sql text,
  CONSTRAINT backup_logs_pkey PRIMARY KEY (id)
)

Em caso de aborto, gostaria sql1, sql2, ... sqlNde ser revertido, mas inserto into backup_logspara persistir. Pergunta: como posso conseguir isso?

postgresql insert

1 respostas

  1. Best Answer

    Quando ocorre uma exceção, tudo é revertido. Você deseja executar o código selecionado "fora" do contexto da transação atual, que é comumente referido como transações autônomas . Isso não está implementado atualmente (a partir da página 9.4). Há um item no wiki do Postgres TODO , mas é uma questão complicada, não prenda a respiração.

    Por enquanto você pode usar o módulo adicional dblink. Ele fornece funções para se conectar a outro banco de dados em uma conexão separada (e, portanto, também uma transação separada) para executar o SQL lá. O que está feito está feito e não pode ser revertido. Ao conectar-se ao mesmo banco de dados outra vez, você efetivamente obtém transações autônomas (com alguma sobrecarga extra). É decentemente rápido.

    Prova de conceito

    Preparações

    Você precisa instalar o dblink uma vez por banco de dados (onde você executa a função):

    CREATE EXTENSION dblink;

    Por conveniência, encapsulo as informações de conexão em um sinal de FOREIGN SERVERadição USER MAPPING:

    CREATE SERVER myserver FOREIGN DATA WRAPPER dblink_fdw
OPTIONS (hostaddr '127.0.0.1', dbname 'test');

CREATE USER MAPPING FOR role_source SERVER myserver
OPTIONS (user 'role_target', password 'secret');

    Existem várias opções para a string de conexão. O exemplo é para um usuário nomeado role_sourcese conectar como role_targetem um banco de dados testem localhost 127.0.0.1. Pode ser o mesmo nome de função para origem e destino. Eu uso uma senha aqui (exemplo simples), mas prefiro muito mais o acesso sem senha para esse fim, para que não precisemos salvar um PW (e não acabe em backups etc.):

    No entanto, por documentação :

    Somente superusuários podem usar dblink_connectpara criar conexões não autenticadas por senha. Se os não superusuários precisarem desse recurso, use -o dblink_connect_u.

    Ou crie uma SECURITY DEFINERfunção para encapsular partes relevantes. Veja abaixo.

    Função principal

    CREATE OR REPLACE FUNCTION f_work()
  RETURNS void AS
$func$
DECLARE
   sql1 text := 'SELECT 1';  -- dummy code
   sql2 text := 'SELECT 2';

BEGIN
   PERFORM dblink_connect('myserver');  -- name of foreign server
   PERFORM dblink_exec($f$INSERT INTO backup_logs(sql)
                          VALUES ('f_work() start')$f$);   -- log start

   PERFORM dblink_exec('INSERT INTO backup_logs(sql)
                        SELECT ' || quote_literal(sql1));  -- log sql1
   EXECUTE sql1;

   -- RAISE EXCEPTION 'foo'; -- unquote to test error case

   PERFORM dblink_exec('INSERT INTO backup_logs(sql)
                        SELECT ' || quote_literal(sql2));  -- log sql2
   EXECUTE sql2;


   PERFORM dblink_exec($f$INSERT INTO backup_logs(sql)
                          VALUES ('f_work() end')$f$);     -- log end
   PERFORM dblink_disconnect();
END
$func$  LANGUAGE plpgsql VOLATILE;

    Certifique-se de ter um USER MAPPINGpara a função com a qual está executando esta função ou torne-a uma SECURITY DEFINERfunção pertencente à função com a qual deseja trabalhar:

    • 3

relate perguntas