Re: plpythonu: how to catch plpy.execute() exceptions

Hi,

On 29 October 2010 17:02, Dragos Valentin Moinescu
<dragos(dot)moinescu(at)gmail(dot)com> wrote:
> The thing is that I cannot catch the exception raised by plpy.execute().

I came to this plpython function template. It handles errors properly
and works faster then usual function because of the code caching.

CREATE OR REPLACE FUNCTION some_plpython_function()
RETURNS integer
LANGUAGE plpythonu
AS $function$
""" Exaple of function's core cache and error handling """

sdNamespace = 'some_plpython_function'

if sdNamespace not in SD:

def main():
""" The function is assumed to be cached in SD and reused """

result = None

# Do whatever you need here

return result

# Cache body in SD
SD[sdNamespace] = main

try:
return SD[sdNamespace]()
except Exception, e:
import traceback
plpy.info(traceback.format_exc())

$function$;

>
> I tried this with the following code:
>
> CREATE OR REPLACE FUNCTION __syncpg_execute() RETURNS boolean
> AS $$
>        try:
>                plpy.execute("SELECT * FROM not_existing_table", 5)
>        except Exception, exc:
>                plpy.notice("Exception %s" % str(exc) )
>        return True
> $$ LANGUAGE plpythonu;
>
> The result is:
> # select __syncpg_execute();
> WARNING:  PL/Python: plpy.SPIError: unrecognized error in PLy_spi_execute_query
> CONTEXT:  PL/Python function "__syncpg_execute"
> NOTICE:  Exception error return without exception set
> CONTEXT:  PL/Python function "__syncpg_execute"
> ERROR:  relation "not_existing_table" does not exist
> LINE 1: SELECT * FROM not_existing_table
>                                      ^
> QUERY:  SELECT * FROM not_existing_table
> CONTEXT:  PL/Python function "__syncpg_execute"
>
> I am using Postgresql9.0 with python 2.4.3
>
> Thank you
>
> --
> Best regards,
> Dragos Moinescu
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

--
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray(dot)ru(at)gmail(dot)com / Skype: gray-hemp