主页 / dba / 问题 / 104994
Accepted
arthur
Asked: 2015-06-25 03:44:28 +0800 CST

即使函数中止，也会在 UDF 中持久插入

  • 772

我有一个相当复杂的 UDF（在一堆表中移动并创建一堆新表），其中可能会发生多次中止。在每次操作之前，我想记录操作发生的时间和查询本身。UDF 如下所示：

log function_start

log sql1
execute sql1

log sql2
execute sql2

...

log sqlN
execute sqlN

log function_end

每条日志语句都意味着在下表中插入一条新记录：

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

如果中止，我想sql1, sql2, ... sqlN回滚，但inserto into backup_logs要坚持。问题：我怎样才能做到这一点？

postgresql insert

1 个回答

  1. Best Answer

    当发生异常时，一切都会回滚。您希望在当前事务上下文“外部”执行选定的代码，这通常称为自主事务。目前尚未实施（截至第 9.4 页）。Postgres TODO wiki 中有一项，但这是一件棘手的事情，请不要屏住呼吸。

    现在您可以使用附加模块dblink。它提供了在单独的连接中连接到另一个数据库（因此也是单独的事务）以在那里执行 SQL 的功能。已经完成的已经完成，无法回滚。通过另一次连接到同一个数据库，您可以有效地实现自主事务（有一些额外的开销）。它的速度相当快。

    概念验证

    准备工作

    您需要为每个数据库（运行函数的位置）安装一次 dblink：

    CREATE EXTENSION dblink;

    为了方便起见，我将连接信息封装在一个FOREIGN SERVER加号中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');

    连接字符串有多种选择。该示例适用于名为localhost 上的数据库的用户role_source连接。源和目标可以是相同的角色名称。我在这里使用密码（简单示例），但出于此目的我更喜欢无密码访问，因此我们不必保存 PW（并且它不会以备份等结束）：role_targettest127.0.0.1

    但是，根据文档：

    只有超级用户可以dblink_connect用来创建非密码验证的连接。如果非超级用户需要此功能，请dblink_connect_u改用。

    或者创建一个SECURITY DEFINER函数来封装相关部分。见下文。

    主功能

    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;

    确保为USER MAPPING运行此函数的角色设置一个 ，或者使其成为SECURITY DEFINER您要使用的角色所拥有的函数：

    • 3

相关问题