Thread: BUG #2836: SPI_execute_plan failed on pl/pgsql function that worked on 8.1

The following bug has been logged online:

Bug reference:      2836
Logged by:          Albert Cervera i Areny
Email address:      albert@sedifa.com
PostgreSQL version: 8.2
Operating system:   Debian GNU/Linux
Description:        SPI_execute_plan failed on pl/pgsql function that worked
on 8.1
Details:

I get the following error on a function that worked correctly on 8.1:

ERROR:  SPI_execute_plan failed executing query "UPDATE
pg_catalog.pg_settings SET setting= $1  WHERE name='search_path'":
Unrecognized SPI code 0
CONTEXT:  PL/pgSQL function "ventas_por_grupo_conjunta_tmp" line 23 at SQL
statement
SQL function "ventas_por_grupo_conjunta" statement 1

The function tries to reestablish the search_path with the following UPDATE
command:

$body$
SELECT setting INTO path FROM pg_catalog.pg_settings WHERE
name='search_path';

...here the rest of the function...

UPDATE pg_catalog.pg_settings SET setting=path WHERE name='search_path
$body$

I've been able to work around this by using:

EXECUTE 'UPDATE pg_catalog.pg_settings SET setting=''' || path || ''' WHERE
name=''search_path''';

instead.

Hope this helps in finding out the problem. I don't think the behaviour is
intended.

Please, contact if you want me to test any patches.

Thanks!

"Albert Cervera i Areny" <albert@sedifa.com> writes:
> I get the following error on a function that worked correctly on 8.1:

> ERROR:  SPI_execute_plan failed executing query "UPDATE
> pg_catalog.pg_settings SET setting= $1  WHERE name='search_path'":
> Unrecognized SPI code 0

Fixed, thanks for the report!

            regards, tom lane