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.

Answer 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;

Answer 2

I think it would be helpfull

  create proc DataInsertInTable
  as
  begin tran

  insert into Table1 values('Table1Data','XYZ')
  if(@@ERROR <>0)
  begin
        rollback tran;
        return 0
  end
  insert into Table2 values('Table2Data','ABC')
  if(@@ERROR <>0)
  begin
        rollback tran;
        return 0
  end
  commit Tran
  return 1

Answer 3

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;