IN AUTONOMOUS TRANSACTION

Available in: PSQL

Added in: 2.5

Description: Code running in an autonomous transaction will be committed immediately upon successful completion, regardless of how the parent transaction finishes. This is useful if you want to make sure that certain actions will not be rolled back, even if an error is raised later.

Syntax:

IN AUTONOMOUS TRANSACTION DO <psql-statement>

Example:

create trigger tr_connect on connect
as
begin
  -- make sure log message is always preserved:
  in autonomous transaction do
    insert into log (msg) values ('User ' || current_user || ' connects.');
  if (current_user in (select username from blocked_users)) then
  begin
    -- again, log message must be preserved and event posted, so:
    in autonomous transaction do
    begin
      insert into log (msg) values ('User ' || current_user || ' refused.');
      post_event 'Connection attempt by blocked user.';
    end
    -- now we can safely except:
    exception ex_baduser;
  end
end

Notes:

Autonomous transactions have the same isolation level as their parent transaction.
Because the autonomous transaction is completely independent of its parent, care must be taken to avoid deadlocks.
If an exception occurs within the autonomous transaction, the work will be rolled back.