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.