即使函式中止，UDF 中的持久插入也是如此

我有一個相當複雜的 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要堅持。問題：我怎樣才能做到這一點？

當發生異常時，一切都會回滾。您希望在目前事務上下文“外部”執行選定的程式碼，這通常稱為自主事務。目前尚未實施（截至 pg 9.4）。Postgres TODO wiki 中有一個項目，但這是一件棘手的事情，不要屏住呼吸。

現在您可以使用附加模組dblink。它提供了在單獨的連接（因此也是單獨的事務）中連接到另一個 DB 以在那裡執行 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_target``test``127.0.0.1

• 使用 psql 命令執行批處理文件，無需密碼

但是，根據文件：

只有超級使用者可以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您要使用的角色擁有的功能：

• 另一個模式中的序列權限
• 作為非超級特權使用者創建 UUID 擴展

引用自：https://dba.stackexchange.com/questions/104994