2

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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
Kanchetianeel
  • 189
  • 2
  • 3
  • 15
  • 4
    This doesn't make much sense. Please *show the code you are running*, and any error messages. – Craig Ringer Jan 15 '14 at 06:06
  • 1
    Maybe you didn't turn off autocommit? –  Jan 15 '14 at 06:52
  • @a_horse_with_no_name: your comment probably was valid at the time, but from my understanding, turning off autocommit now is neither needed nor possible: http://stackoverflow.com/a/17936997 – ssc Sep 30 '16 at 16:05

3 Answers3

5

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;
smn_onrocks
  • 1,282
  • 1
  • 18
  • 33
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;
Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
-8

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
code save
  • 1,054
  • 1
  • 9
  • 15