Persistent inserts in a UDF even if the function aborts

Question

I have a rather complicated UDF (moves around a bunch of tables as well as creates a bunch of new ones) where multiple aborts may occur. Before every operation I would like to record the time when an operation takes place and the query itself. The UDF looks like the following then:

log function_start

log sql1
execute sql1

log sql2
execute sql2

...

log sqlN
execute sqlN

log function_end

Every log statement means inserting a new record into the following table:

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

In case of abort I'd like sql1, sql2, ... sqlN to be rollbacked, but the inserto into backup_logs to persist. Question: how can I achieve that?

Answer 1

When an exception occurs everything is rolled back. You want to execute selected code "outside" the current transaction context, which is commonly referred to as autonomous transactions. This is not currently implemented (as of pg 9.4). There is an item in the Postgres TODO wiki, but it's a tricky matter, don't hold your breath.

Update: Postgres 11 or later allows COMMIT in procedures. (But committing some changes and not others is still tricky.) See:

• When to use stored procedure / user-defined function?
• Does a procedure run in a transaction in PostgreSQL?

For now you can use the additional module dblink. It provides functions to connect to another DB in a separate connection (and thus also separate transaction) to execute SQL there. What's done is done and cannot be rolled back. By connecting to the same DB another time you effectively achieve autonomous transactions (with some extra overhead). It's decently fast.

Proof of concept

Preparations

You need to install dblink once per database (where you run the function):

CREATE EXTENSION dblink;

For convenience I encapsulate the connection info in a FOREIGN SERVER plus 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');

There are various options for the connection string. The example is for a user named role_source to connect as role_target in a DB test on localhost 127.0.0.1. Could be the same role name for source and target. I use a password here (simple example) but I much prefer password-less access for the purpose, so we don't have to save a PW (and it does not end up in backups etc.):

• Run batch file with psql command without password

However, per documentation:

Only superusers may use dblink_connect to create non-password-authenticated connections. If non-superusers need this capability, use dblink_connect_u instead.

Or create a SECURITY DEFINER function to encapsulate relevant parts. See below.

Main function

CREATE OR REPLACE FUNCTION f_work()
  RETURNS void
  LANGUAGE plpgsql VOLATILE 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$;

Be sure to have a USER MAPPING for the role you are running this function with, or make it a SECURITY DEFINER function owned by the role you want to work with:

• Permission for sequence in another schema
• Creating UUID extension as the non super privileged user