Обсуждение: ROLLBACK, SAVEPOINT, COMMIT throwing error in transaction block.

Hi List,=20

Below function throwing error when i execute it on PostgreSQL 9.0.=20

ERROR: SPI_execute_plan_with_paramlist failed executing query "SAVEPOINT my=
_savepoint": SPI_ERROR_TRANSACTION

CONTEXT: PL/pgSQL function "rlbk" line 5 at SQL statement

=20

I searched and found that it is reported to pgsql-bugs. Can  you please let=
 me know whether this bug has fixed or not.=20

=20

CREATE OR REPLACE FUNCTION rlbk(integer) RETURNS integer AS
$BODY$

DECLARE

bal_amt ALIAS FOR $1;

BEGIN

UPDATE accounts SET balance =3D balance - bal_amt WHERE name =3D 'Axis';=20

SAVEPOINT my_savepoint;=20

UPDATE accounts SET balance =3D balance + bal_amt WHERE name =3D 'Bob';=20

ROLLBACK TO my_savepoint;=20

UPDATE accounts SET balance =3D balance + bal_amt WHERE name =3D 'Wally';=
=20

COMMIT;

return bal_amt;

end;

$BODY$
LANGUAGE 'plpgsql';

=20
--
Thanks & Regards
Dhaval Jaiswal |=20
The information transmitted is intended only for the person or entity to wh=
ich it is addressed and may contain confidential and/or privileged material=
.=20
Any review, re-transmission, dissemination or other use of or taking of any=
 action in reliance upon,this information by persons or entities other than=
 the intended recipient is prohibited.=20
If you received this in error, please contact the sender and delete the mat=
erial from your computer.=20
Microland takes all reasonable steps to ensure that its electronic communic=
ations are free from viruses.=20
However, given Internet accessibility, the Company cannot accept liability =
for any virus introduced by this e-mail or any attachment and you are advis=
ed to use up-to-date virus checking software.=20

"Jaiswal Dhaval Sudhirkumar" <JaiswalDS@microland.com> writes:
> ERROR: SPI_execute_plan_with_paramlist failed executing query "SAVEPOINT my_savepoint": SPI_ERROR_TRANSACTION

This is not a bug.  It's an extremely well-documented limitation that
you can't use those commands in a plpgsql function.

You can get the effect of a savepoint using a BEGIN/EXCEPTION block.

            regards, tom lane