Re: [PATCH] Fix documentation about PL/Python exception handling

On Fri, Nov 19, 2010 at 05:29, Greg Smith <greg@2ndquadrant.com> wrote:
> The open question for this one then is the right way for exceptions to act.
>  Marti, since you've looked at this a bit already, any thoughts on what that
> should look like?  You initially commented that what happens was surprising,
> but I'm not clear on what you expected to happen instead.

The documented behavior would make a lot more sense.

For instance, currently, when you call plpy.error(), the exception
that's thrown can be caught, but even if you handle it, the function
still ends up failing. Similarly you CANNOT handle SQL errors -- at
all! All errors are fatal.

CREATE FUNCTION foo() RETURNS text
       AS
'try:
       plpy.execute("SELECT syntax error")
except:
       plpy.notice("exception caught but cannot ignore")
       return "retval"
'
       LANGUAGE plpythonu;

WARNING:  PL/Python: plpy.SPIError: unrecognized error in PLy_spi_execute_query
CONTEXT:  PL/Python function "foo"
NOTICE:  exception caught but cannot ignore
CONTEXT:  PL/Python function "foo"
ERROR:  column "syntax" does not exist
LINE 1: SELECT syntax error
               ^
QUERY:  SELECT syntax error
CONTEXT:  PL/Python function "foo"

Notice how the return value in the above is ignored, although the
NOTICE is still output. Any new exceptions raised in the exception
handler -- such as coding errors -- are also silently ignored.

Another surprise is that plpy.Error and plpy.Fatal are just empty
exception subclasses -- they behave nothing like
plpy.error()/plpy.fatal(). Since they're normal Python exceptions,
they can be caught and silenced. But an unhandled plpy.Fatal still
results in an ERROR level message, go figure.

I'm surprised that PL/Python got merged into Postgres at all without
proper error handling.

Regards,
Marti