Transactions are an important feature in any database and PostgreSQL has them. We want to be “ACID Compliant” and ensure that all data is read and written in a consistent state. All changes in the transaction either commit or rollback together. You never change just one table, without also changing all the other related tables together in an atomic block. Transactions are great!

Yeah. But … what about when you actually want to change just one table and roll back all the rest? Or you have to ensure that one change is made whether or not the rest of the transaction is committed? Say you need to write an audit record of an attempted change or data access by a user. Or say you are trying to update a batch job control table? What do you do then?

As a database developer, you want these sub-transactions or autonomous transactions to be processed and committed regardless of what happens to the larger surrounding transaction block. The application could open 2 connections or make 2 transactions in a row, but if the 2nd transaction or connection failed you might not get your audit record written, and you would be paying a performance penalty.

If you’re coming from an Oracle background, you may be familiar with PRAGMA AUTONOMOUS TRANSACTION – which makes a procedure execute in an autonomous transaction so any changes in the procedure are committed separately from the calling pl/SQL code. PostgreSQL does not directly support an autonomous transaction, but there are multiple ways to accomplish this same goal.

Installing pg_background

The pg_background Postgres extension originally written by Robert Haas starts a separate worker to process a SQL statement, which is a nice way to achieve an autonomous transaction. pg_background is available through the Postgres by BigSQL distribution so you can install it using the PGC command line tool:

#always make sure you have the latest list of extensions
./pgc UPDATE
./pgc install background1_pg96

Then create the extension in your database as normal:

-- create the extension needed for autonomous transactions
CREATE EXTENSION IF NOT EXISTS pg_background;

Sample usage

Here’s an example using pg_background for the job control status example. As you build app logic or batch job processing logic in your PL/pgSQL you will inevitably hit the need to log and update the status of a job process. Started, 1,000 rows processed, finished, etc. And whether or not a larger transaction or long running process finishes or rolls back, you want those log records to persist so you can debug why your batch job did fail, roll back, etc. It’s much harder to troubleshoot when your log records are rolled back any time there is a problem! So … use the autonomous transaction approach for writing a status record.

First, sample tables:

DROP TABLE IF EXISTS BATCH_JOB_STATUS;

CREATE TABLE BATCH_JOB_STATUS (
  JOB_NAME varchar primary key,
  STATUS_IND char(1),
  UPDT_TIME TIMESTAMPTZ
);

Now, we want to make a PL/pgSQL function that can be called from regular procedure code but always commits, regardless of what happens in the surrounding transaction. Here’s a simple approach that builds up the SQL as a dynamic string and then executes it using pg_background, waiting for the result:

-- simple function to update status in background
CREATE or REPLACE FUNCTION batch_job_set_status (
  P_JOB_NAME             IN BATCH_JOB_STATUS.JOB_NAME%TYPE,
  P_STATUS_IND            IN BATCH_JOB_STATUS.STATUS_IND%TYPE
) 
  RETURNS VOID
AS $FUNCBODY$
DECLARE
  v_query text;
BEGIN

  v_query = 'INSERT INTO BATCH_JOB_STATUS'
          || ' (JOB_NAME, STATUS_IND, UPDT_TIME)'
          || ' VALUES ('
          || quote_nullable(P_JOB_NAME) || ', '
          || quote_nullable(P_STATUS_IND) || ', '
          || 'NOW())'
          || ' ON CONFLICT (JOB_NAME) DO UPDATE'
          || ' SET STATUS_IND = ' || quote_nullable(P_STATUS_IND)
          || ' , UPDT_TIME = NOW()'
          ;

  -- execute function as autonomous transaction "in background"
  -- and wait for the result
  PERFORM * 
  FROM pg_background_result(pg_background_launch(v_query)) 
  AS (result TEXT);

END; 
$FUNCBODY$ LANGUAGE plpgsql;

This shows the key usage sequence for pg_background – use pg_background_launch() to execute a SQL statement in the background. In this case we wait for the result by wrapping the call with pg_background_result(). If this was truly a long running background and we wanted to check for the status and result, we could store the ID returned from launch() for a later call the result().

In this case, we built the full upsert statement as a dynamic SQL string. If the logic to be executed in the background was more complex, you would want to make an “inner” PL/pgSQL function and just dynamically build up the SQL string to call the autonomous function.

Demonstration

In the naive approach, if we try to directly update the status inside a transaction that rolls back, we lose the update we just made:

`

postgres=# BEGIN;
BEGIN
postgres=# SELECT * FROM BATCH_JOB_STATUS WHERE JOB_NAME = 'my job';
 job_name | status_ind |           updt_time           
----------+------------+-------------------------------
 my job   | R          | 2017-05-08 15:59:23.774083-04
(1 row)

postgres=# UPDATE BATCH_JOB_STATUS SET STATUS_IND = 'C' WHERE JOB_NAME = 'my job';
UPDATE 1
postgres=# SELECT * FROM BATCH_JOB_STATUS WHERE JOB_NAME = 'my job';
 job_name | status_ind |           updt_time           
----------+------------+-------------------------------
 my job   | C          | 2017-05-08 15:59:23.774083-04
(1 row)

postgres=# ROLLBACK;
ROLLBACK
postgres=# SELECT * FROM BATCH_JOB_STATUS WHERE JOB_NAME = 'my job';
 job_name | status_ind |           updt_time           
----------+------------+-------------------------------
 my job   | R          | 2017-05-08 15:59:23.774083-04
(1 row)

`

But using the background execution we are able to run an autonomous transaction and keep the update, even though the surrounding transaction rolls back.

`

postgres=# BEGIN;
BEGIN
postgres=# SELECT * FROM batch_job_set_status('my job', 'C');
 batch_job_set_status 
----------------------
 
(1 row)

postgres=# SELECT * FROM BATCH_JOB_STATUS WHERE JOB_NAME = 'my job';
 job_name | status_ind |           updt_time           
----------+------------+-------------------------------
 my job   | C          | 2017-05-08 22:28:01.344463-04
(1 row)

postgres=# ROLLBACK;
ROLLBACK
postgres=# SELECT * FROM BATCH_JOB_STATUS WHERE JOB_NAME = 'my job';
 job_name | status_ind |           updt_time           
----------+------------+-------------------------------
 my job   | C          | 2017-05-08 22:28:01.344463-04
(1 row)

<

p>`

DBLink option

Prior to parallel workers, the most common approach was to use DBLink, which had several drawbacks. DBLink created a new connection to the same database server which was sometimes a performance impediment, especially if you wound up executing a lot of single row inserts or updates throughout a transaction – such as in an audit trigger or debugging scenario. And you have to manage the connection information (including password) in the executing code.

The idea of integrating some version of background SQL execution has been circulating for a couple of versions. Robert Haas wrote the original code for pg_background as part of the parallelism features in 9.6. Peter Eisentraut wrote a more general background sessions patch that will hopefully be able to be included for 11.0. Until then, just follow the steps above to install via PGC and use the extension.