Tuesday, May 29, 2012

Autonomous Transaction in PostgreSQL 9.1

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