Rolled back but an error persists

Question

Semantics:

I am using PostGreSql 9.0.3 as my Dbms. Actually i was tried to accomplish one of the objectives assigned for me, which is to Raise Exception with some predefined message when some conditions failed in a IF - Statement inside my stored procedure. As a result of that exception, The Process should needs to be rolled back.

Syntax:

 For r3 in select itemname,stock from stock s,items it where s.itemno=it.itemno and it.itemno=$2[I].itemno and s.stockpointno=$1.stockpointno loop
    if xStock > r3.stock then
        RAISE EXCEPTION '%', r3.itemname || ' decreased down from the low stock level'; 
    end if;                           
 End Loop;

where r3 is a record and xStock is a Double Precision Variable.

Then At the end of the stored procedure i just included the following code in order to roll back the transactions happened.

Exception when raise_exception then rollback transaction;

The problem i am facing is when ever the manual exception getting raised, The following error bumps up.

DA00014:ERROR: XX000: SPI_execute_plan_with_paramlist failed executing query "rollback transaction": SPI_ERROR_TRANSACTION

Though the above error occured, transactions are not happened while i was checking in the tables. I don't know the exact reason why this particular error is raising when rolling back is in progress. Can anybody tell what may be the possible mistake which i was made in my code? And also suggest solutions to fix this issue.

You do not need to explicitly rollback transaction in case of error. Any thrown exception inside transaction will cause a rollback. — Ihor Romanchenko, Oct 12 '13 at 12:04
The same thing could be accomplished with a check condition and/or a trigger function. No loops, no cursors. — wildplasser, Oct 12 '13 at 12:15
@IgorRomanchenko Oh.. Then, Under which circumstances we have to use Roll backs manually? Until now i wrongly assumes that the roll backs are invoked manually by us. — Rajaprabhu Aravindasamy, Oct 12 '13 at 12:48
@wildplasser what do you meant by `accomplished with a check condition` and `No loops, no cursors.`? Actually i had checked condition by using if-statement inside my code. — Rajaprabhu Aravindasamy, Oct 12 '13 at 12:52
Ok i just realized that the error which i mentioned in my question was occurred due to the Explicit rollback call. But can anybody tell me the Technical reason for this error.? Because it would be very useful if i face the same error in future. And i can place a reason to my project manager in order to remove all that explicit call in the project. — Rajaprabhu Aravindasamy, Oct 12 '13 at 12:57
I don't know where the `xStock` variable comes from. If it is an expression from one or more tables, the whole purpose of the function in the OQ appears to be to enforce some constraint (or is it business logic?) . 2) A ROLLBACK rolls back the changes you made in your current transaction. It also closes all cursors. Your session is essentially reset to its initial state. So the cursor in your loop is no longer valid. — wildplasser, Oct 12 '13 at 13:16

score 1 · Accepted Answer · answered Oct 22 '13 at 11:20

While some database engines allow COMMIT or ROLLBACK inside a function or procedure, PostgreSQL does not. Any attempt to do that leads to an error:

ERROR: cannot begin/end transactions in PL/pgSQL

That includes code inside an exception block.

On the other hand, a RAISE EXCEPTION alone will abort the transaction with the function-supplied error message, so there's no need to trap your own exception. It would work as expected if you just removed your exception block.

As said by the plpgsql documentation in Trapping Errors:

By default, any error occurring in a PL/pgSQL function aborts execution of the function, and indeed of the surrounding transaction as well

You current code raises the exception, then traps it and fails in the exception block itself because of the forbidden ROLLBACK statement, which leads to the SQL engine aborting the transaction.

Rolled back but an error persists

1 Answers1