Re: problem with stored procedure ,transaction and jdbc

On Fri, 12 Aug 2005, Andres Ledesma wrote:

> have you considered to enclose both stored procedures in a transaction inside
> another stored procedure, and call just one from the java code, something
> like :
>
> CREATE FUNCTION stored_procedure_3()....
> BEGIN;
> BEGIN TRANSACTION;
> PERFORM ..     call strored procedure 1
> PERFORM ..     call strored procedure 2
> COMMIT;
> END;
> ' LANGUAGE plpgsql;
>

Postgresql does not allow transactions to be started/stopped inside of a
backend function.  It does support savepoints which do allow work to be
saved and rolled back to, but the function call is still wrapped in an
outer transaction.  For the example above leaving the BEGIN
TRANSACTION/COMMIT off may give you the behavior desired (because the
query itself is wrapped in a transaction).

Kris Jurka