To use autonomous transactions, you need to enable required extension:

                CREATE EXTENSION IF NOT EXISTS pg_background;
            

In the following example, we'll use autonomous transactions to write log records for a job execution. Assume we have a table that stores job statuses:

                DROP TABLE IF EXISTS BATCH_JOB_STATUS;

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

Next, 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 autonomous transactions – 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.

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 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)