Thread: BUG #5500: SPI_execute_plan_with_paramlist SPI_ERROR_TRANSACTION with savepoint
BUG #5500: SPI_execute_plan_with_paramlist SPI_ERROR_TRANSACTION with savepoint
From
"Robert Walker"
Date:
The following bug has been logged online: Bug reference: 5500 Logged by: Robert Walker Email address: robwalker01@speedymail.org PostgreSQL version: 9.0 beta 2 Operating system: Windows XP 32-bit SP2 Description: SPI_execute_plan_with_paramlist SPI_ERROR_TRANSACTION with savepoint Details: SPI_execute_plan_with_paramlist seems to be newly introduced into 9.0 beta that is causing an error in pl/pgsql when attempting to create a savepoint. This did not happen when using version 8.4 (which I assume was not using SPI_execute_plan_with_paramlist), so I believe this is a regression. The specific format of the error is: ERROR: SPI_execute_plan_with_paramlist failed executing query "SAVEPOINT savepoint_name": SPI_ERROR_TRANSACTION SQL state: XX000 Context: PL/pgSQL function "add_foo" line 241 at SQL statement Before the savepoint is only a couple of statements: one insert statement and another insert statement using "returning *" into a variable. The function parameters use %TYPE in order to help ensure the parameters match the same type as the table column it is meant to deal with. The function's return value is a rowtype of the results from the insert/returning right before the savepoint. If I comment out the few lines regarding savepoints, the function works (but without error trapping of course), so I know it's not a syntax error or some other type of error. PostgreSQL for some reason wants to use SPI_execute_plan_with_paramlist and this causes the error.
Re: BUG #5500: SPI_execute_plan_with_paramlist SPI_ERROR_TRANSACTION with savepoint
From
Tom Lane
Date:
"Robert Walker" <robwalker01@speedymail.org> writes: > SPI_execute_plan_with_paramlist seems to be newly introduced into 9.0 beta > that is causing an error in pl/pgsql when attempting to create a savepoint. > This did not happen when using version 8.4 (which I assume was not using > SPI_execute_plan_with_paramlist), so I believe this is a regression. Um, it's never been the case that you could execute a SAVEPOINT statement inside plpgsql. I'd be interested to see the case that you claim worked in 8.4. regards, tom lane
Re: BUG #5500: SPI_execute_plan_with_paramlist SPI_ERROR_TRANSACTION with savepoint
From
Alvaro Herrera
Date:
Excerpts from Robert Walker's message of vie jun 11 14:49:03 -0400 2010: > SPI_execute_plan_with_paramlist seems to be newly introduced into 9.0 beta > that is causing an error in pl/pgsql when attempting to create a savepoint. > This did not happen when using version 8.4 (which I assume was not using > SPI_execute_plan_with_paramlist), so I believe this is a regression. > > The specific format of the error is: > > ERROR: SPI_execute_plan_with_paramlist failed executing query "SAVEPOINT > savepoint_name": SPI_ERROR_TRANSACTION If it fails to fail on 8.4, that's a bug -- it is *supposed* not to work. If you want to use savepoints in plpgsql you need to use EXCEPTION blocks. Can you provide a working example in 8.4? -- Ãlvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support