Transaction roll back not working in Postgresql

Question

I am working on PostgreSQL 9.1.4 .

I am inserting the data into 2 tables its working nicely.

I wish to apply transaction for my tables both table exist in same DB. If my 2nd table going fail on any moment that time my 1 st table should be rollback.

I tried the properties in "max_prepared_transactions" to a non zero value in /etc/postgres/postgres.conf. But Still Transaction roll back is not working.

Solution 1

in postgresql you cannot write commit or roll back explicitly within a function. I think you could have use a begin end block just write it simple

BEGIN;
   insert into tst_table values ('ABC');
   Begin
    insert into 2nd_table values ('ABC');
   EXCEPTION
    when your_exception then
    ROLL BACK;
   END;
END;

Solution 2

Probably you didn't started transaction.

Please, try

BEGIN;
  INSERT INTO first_table VALUES(10);

  -- second insert should fail
  INSERT INTO second_table VALUES(10/0);

ROLLBACK;