Thread: BUG #1971: Start Transaction is giving Errors !!!
The following bug has been logged online: Bug reference: 1971 Logged by: Yogaraj. C Email address: c_yog@rediffmail.com PostgreSQL version: 7.4.8 Operating system: Linux - Red Hat 9 Description: Start Transaction is giving Errors !!! Details: Hi, When I was executing the procedure (with Transaction), postgres will producing the following error. ERROR: SPI_prepare() failed on "START TRANSACTION" CONTEXT: PL/pgSQL function "sp_example1" line 4 at SQL statement If anyone know the solution for this, pleas help me. Yogaraj.C
On Mon, Oct 17, 2005 at 12:50:29PM +0100, Yogaraj. C wrote: > When I was executing the procedure (with Transaction), postgres will > producing the following error. > > ERROR: SPI_prepare() failed on "START TRANSACTION" > CONTEXT: PL/pgSQL function "sp_example1" line 4 at SQL statement http://www.postgresql.org/docs/7.4/interactive/plpgsql-structure.html "Functions and trigger procedures are always executed within a transaction established by an outer query --- they cannot start or commit transactions...." > If anyone know the solution for this, pleas help me. What problem are you trying to solve? PostgreSQL 8.0 introduced savepoints, which you can use in PL/pgSQL via an EXCEPTION clause: http://www.postgresql.org/docs/8.0/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING Another possibility might be to use dblink to make another connection to the database from within the function and execute transactions over that connection. -- Michael Fuhr
Yogaraj. C wrote: > When I was executing the procedure (with Transaction), postgres will > producing the following error. > > ERROR: SPI_prepare() failed on "START TRANSACTION" > CONTEXT: PL/pgSQL function "sp_example1" line 4 at SQL statement > > If anyone know the solution for this, pleas help me. NOTABUG. You are not allowed to use transaction commands using SPI, which in particular means PL/pgSQL. Use the EXCEPTION clause if you need a savepoint-like feature. Note that you can't close the transaction that started the function. -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34 "El sudor es la mejor cura para un pensamiento enfermo" (Bardia)