Autonomous Transaction in PostgreSQL 9.1

Posted on by Raghav
Currently am working on Migrations from Oracle to PostgreSQL. Though am DBA, these days am learning a bit on Developer track too … 🙂
Let’s see a small feature of Oracle and a similar way in PostgreSQL.

Autonomous Transaction,what is it ?

An autonomous transaction is an independent transaction that is initiated by another transaction, and executes without interfering with the parent transaction. When an autonomous transaction is called, the originating transaction gets suspended. Control is returned when the autonomous transaction does a COMMIT or ROLLBACK.

Example in Oracle:

Create two tables and one procedure as shown below.

create table table_a(name varchar2(50));
create table table_b(name varchar2(50));

create or replace procedure insert_into_table_a is
begin
   insert into table_a values('Am in A');
   commit;
end;

Lets test it here.

SQL> begin
  2  insert into table_b values('Am in B');
  3  insert_into_table_a;
  4  rollback;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select * from table_a;

Am in A

SQL> select * from table_b;

Am in B

In my example above, line 3 has committed the line 2, where it has to rollback according to line 4. In my example am looking for a transaction blocks to behave independently, to achieve it in Oracle we need to include PRAGMA autonomous_transaction in the Procedure declaration to behave as independent transaction block. Lets Retake:

Truncate table table_a;
Truncate Table table_b;

create or replace procedure insert_into_table_a is pragma autonomous_transaction;
begin
   insert into table_a values('Am in A');
   commit;
end;

SQL> begin
  2  insert into table_b values('Am in B');
  3  INSERT_INTO_TABLE_A;
  4  rollback;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select * from table_a;

NAME
----------
Am in A

SQL> select * from table_b;

no rows selected

How to make work in PostgreSQL ?

Autonomous Transaction, are very well controlled in Oracle. Similar functionality is not there in PostgreSQL, however you can achieve with a hack using dblink. Below is the link, where hack has been provided:
http://archives.postgresql.org/pgsql-hackers/2008-01/msg00893.php

create extension dblink;

create or replace function insert_into_table_a() returns void as $$
begin
    perform dblink_connect('pragma','dbname=edb');
    perform dblink_exec('pragma','insert into table_a values (''Am in A'');');
    perform dblink_exec('pragma','commit;');
    perform dblink_disconnect('pragma');
end;
$$ language plpgsql;

edb=# begin;
BEGIN
edb=# insert into table_b VALUES ('am in B');
INSERT 0 1
edb=# select insert_into_table_a();
 insert_into_table_a
---------------------

(1 row)

edb=# select * from table_a;
  name
---------
 Am in A
(1 row)

edb=# select * from table_b;
  name
---------
 am in B
(1 row)

edb=# rollback;
ROLLBACK
edb=# select * from table_a;
  name
---------
 Am in A
(1 row)

edb=# select * from table_b;
 name
------
(0 rows)

Is it not simple, thanks to the hack provider.

–Raghav

20 Replies to “Autonomous Transaction in PostgreSQL 9.1”

  2. @Sharpe:Yes you are very true, without outer DB connection you cannot achieve this for sure. Since, its a workaround for those who are from Oracle Background and looking such Autonomous transaction kinda thing will help this hack.

  6. Bob or Raghavendra, could you better explain, how you imagine autonomous transaction using PL/Perl without creation of separate DB connection? AFAIK, in PostgreSQL, every stored procedure (does not matter if PL/pgSQL or PL/Perl or other PL/something) is packaged in one outer atomic transaction, unable to use e.g. COMMIT or begin autonomous transaction inside it. I don&#39t mean something like this http://archives.postgresql.org/pgsql-general/2006-09/msg01005.php, but more real autonomous transaction, without separate DB connection.

  7. I actually consider the lack of autonomous transactions mostly a feature in PostgreSQL. It makes things less complex. It&#39s all-or-nothing. If some stored procedure deep down throws an error, it is guaranteed the whole shebang will be rollbacked. As the user of a function, you don&#39t have to know the details of what the function might do or not do, you know it cannot affect anything if you later on if your code decides to do a rollback.In our system, I&#39ve only lacked this feature at one single place, and it&#39s to log invalid API requests, where the function does RAISE EXCEPTION, and thus cannot write the content of the invalid request to a table since it will be rollbacked.In this case I use the COPY TO command, to write the row to disk, which is then imported back to PostgreSQL using a cronjob using COPY FROM.

  9. Does dblink transfer state between the connections. If I set some kind of session setting like "set enable_seqscan=false" or whatever the setting is for date formatting does dblink send this over to the other connection? If the other connection changes them are they sent back? Are there session "variables" like "found" or sqlca sort of stuff that needs to be dealt with?

  10. Does dblink transfer state between the connections. If I set some kind of session setting like "set enable_seqscan=false" or whatever the setting is for date formatting does dblink send this over to the other connection? If the other connection changes them are they sent back? Are there session "variables" like "found" or sqlca sort of stuff that needs to be dealt with?

  11. @Sharpe:

    Yes you are very true, without outer DB connection you cannot achieve this for sure. Since, its a workaround for those who are from Oracle Background and looking such Autonomous transaction kinda thing will help this hack.

  12. Bob or Raghavendra, could you better explain, how you imagine autonomous transaction using PL/Perl without creation of separate DB connection? AFAIK, in PostgreSQL, every stored procedure (does not matter if PL/pgSQL or PL/Perl or other PL/something) is packaged in one outer atomic transaction, unable to use e.g. COMMIT or begin autonomous transaction inside it. I don't mean something like this http://archives.postgresql.org/pgsql-general/2006-09/msg01005.php, but more real autonomous transaction, without separate DB connection.

  13. I actually consider the lack of autonomous transactions mostly a feature in PostgreSQL. It makes things less complex. It's all-or-nothing. If some stored procedure deep down throws an error, it is guaranteed the whole shebang will be rollbacked. As the user of a function, you don't have to know the details of what the function might do or not do, you know it cannot affect anything if you later on if your code decides to do a rollback.

    In our system, I've only lacked this feature at one single place, and it's to log invalid API requests, where the function does RAISE EXCEPTION, and thus cannot write the content of the invalid request to a table since it will be rollbacked.
    In this case I use the COPY TO command, to write the row to disk, which is then imported back to PostgreSQL using a cronjob using COPY FROM.

  14. @Bob

    Very true, plperl is also a good candidate for Autonomous transaction. However, I dont know plperl much so tried dblink.

    @Anonymous

    There is a release from EnterpriseDB with support of Autonomous Transaction in PostgresPlus Advance Server(A oracle Compatiblity product).

    –Raghav

  15. @Bob

    Very true, plperl is also a good candidate for Autonomous transaction. However, I dont know plperl much so tried dblink.

    @Anonymous

    There is a release from EnterpriseDB with support of Autonomous Transaction in PostgresPlus Advance Server(A oracle Compatiblity product).

    –Raghav

  16. Nice article you can also do this by using plperl for example. You just open a new connection and start a transaction in it committing or rolling back. Not sure if dblink or plperl is better for this, probably more opinion than anything.

  18. Nice article you can also do this by using plperl for example. You just open a new connection and start a transaction in it committing or rolling back. Not sure if dblink or plperl is better for this, probably more opinion than anything.

Comments are closed.