Error handling in plperl and pltcl

Tom Lane wrote:

What I think we ought to do is change both PL languages so that every
SPI call is executed as a subtransaction. If the call elogs, we can
clean up by aborting the subtransaction, and then we can report the
error message as a Perl or Tcl error condition, which the function
author can trap if he chooses. If he doesn't choose to, then the
language interpreter will return an error condition to plperl.c or
pltcl.c, and we can re-throw the error.

This will slow down the PL SPI call operations in both languages, but
AFAICS it's the only way to provide error handling semantics that aren't
too broken for words.

The same observations apply to plpython, of course, but I'm not
volunteering to fix that language because I'm not at all familiar with
it. Perhaps someone who is can make the needed changes there.

Comments?

My approach with PL/Java is a bit different. While each SPI call is
using a try/catch they are not using a subtransaction. The catch will
however set a flag that will ensure two things:

1. No more calls can be made from PL/Java to the postgres backend.
2. Once PL/Java returns, the error will be re-thrown.

This allows PL/Java to catch the error, clean up (within the Java
domain), and return, nothing more.

The solution is IMO safe and could be used for all PL languages. It
introduces no overhead with subtransactions, and the developer writing
functions are provided a clean up mechanism where resources not related
to SPI can be handled (files closed, etc.).

Something that would be great for the future is if the errors could
divided into recoverable and unrecoverable.

Regards,
Thomas Hallgren

Tom Lane wrote:

plperl's error handling is not completely broken, but it's close :-(
Consider for example the following sequence on a machine with a
relatively old Perl installation:

You just picked an easy way to trigger this. As you rightly observe,
there are others.

We can deal with this in a localized fashion for plperl's elog()
subroutine, by PG_CATCH'ing the longjmp and converting it into a Perl
croak() call.

[...]

What I think we ought to do is change both PL languages so that every
SPI call is executed as a subtransaction. If the call elogs, we can
clean up by aborting the subtransaction, and then we can report the
error message as a Perl or Tcl error condition, which the function
author can trap if he chooses. If he doesn't choose to, then the
language interpreter will return an error condition to plperl.c or
pltcl.c, and we can re-throw the error.

We can do both of these, no?

This will slow down the PL SPI call operations in both languages, but
AFAICS it's the only way to provide error handling semantics that aren't
too broken for words.

Can you estimate the extent of the slowdown?

cheers

andrew

Thomas Hallgren <thhal@mailblocks.com> writes:

My approach with PL/Java is a bit different. While each SPI call is
using a try/catch they are not using a subtransaction. The catch will
however set a flag that will ensure two things:

1. No more calls can be made from PL/Java to the postgres backend.
2. Once PL/Java returns, the error will be re-thrown.

That's what pltcl has always done, and IMHO it pretty well sucks :-(
it's neither intuitive nor useful.

regards, tom lane

Andrew Dunstan <andrew@dunslane.net> writes:

Tom Lane wrote:

This will slow down the PL SPI call operations in both languages, but
AFAICS it's the only way to provide error handling semantics that aren't
too broken for words.

Can you estimate the extent of the slowdown?

Without actually doing the work, the closest comparison I can make is
between plpgsql functions with and without exception blocks. I tried

create or replace function foo(int) returns int as '
declare x int;
begin
select into x unique1 from tenk1 where unique2 = $1;
return x;
end' language plpgsql;

create or replace function foo(int) returns int as '
declare x int;
begin
begin
select into x unique1 from tenk1 where unique2 = $1;
exception
when others then null;
end;
return x;
end' language plpgsql;

and used
explain analyze select foo(unique2) from tenk1;
to execute each one 10000 times without too much overhead.
I get about 6900 vs 12800 msec, so for a simple pre-planned query
it's not quite a 50% overhead. This is probably about the worst
case you'd see in practice --- unlike plpgsql, plperl and pltcl
functions wouldn't be calling the SQL engine to do simple arithmetic,
so they're not going to have SPI calls that do much less work than
this example does.

regards, tom lane

Tom Lane wrote:

Thomas Hallgren <thhal@mailblocks.com> writes:

My approach with PL/Java is a bit different. While each SPI call is
using a try/catch they are not using a subtransaction. The catch will
however set a flag that will ensure two things:

1. No more calls can be made from PL/Java to the postgres backend.
2. Once PL/Java returns, the error will be re-thrown.

That's what pltcl has always done, and IMHO it pretty well sucks :-(
it's neither intuitive nor useful.

Given that most SPI actions that you do doesn't elog (most of them are
typically read-only), it's far more useful than imposing the overhead of
a subtransaction on all calls. That IMHO, would really suck :-(

Ideally, the behavior should be managed so that if a subtransaction is
started intentionally, crash recovery would be possible and the function
should be able to continue after it has issued a rollback of that
subtransaction.

I'm suprised you say that this is not useful. I've found that in most
cases when you encounter an elog, this is the most intuitive behavior.
Either you don't do any cleanup, i.e. just return and let the elog be
re-thrown, or you close some files, free up some resources or whatever,
then you return. Not many functions would continue executing after an
elog, unless of course, you *intentionally* started a subtransaction.

I'll investigate what's entailed in handling SPI calls performed in a
subtransaction differently so that calls are blocked only until the
subtransaction is rolled back. Since I have my own JDBC driver, that
doesn't sound too hard. I guess PL/Perl and PL/Tcl has something similar
where they could track this.

Such handling, in combination with a "recoverable" status in the elog's
error structure, would create a really nice (end efficient) subsystem.

Regards,
Thomas Hallgren

Thomas Hallgren <thhal@mailblocks.com> writes:

Tom Lane wrote:

That's what pltcl has always done, and IMHO it pretty well sucks :-(
it's neither intuitive nor useful.

Given that most SPI actions that you do doesn't elog (most of them are
typically read-only), it's far more useful than imposing the overhead of
a subtransaction on all calls. That IMHO, would really suck :-(

I don't think we really have any alternative --- certainly not if you
want to continue to regard plperl as a trusted language. I haven't
bothered to develop a test case, but I'm sure it's possible to crash
the backend by exploiting the lack of reasonable error handling in
spi_exec_query.

There's an ancient saying "I can make this code arbitrarily fast ...
if it doesn't have to give the right answer". I think that applies
here. Fast and unsafe is not how the Postgres project customarily
designs things. I'd rather get the semantics right the first time
and then look to optimize later. (I'm sure we can do more to speed
up subtransaction entry/exit than we have so far.)

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

(I'm sure we can do more to speed up subtransaction entry/exit than we have
so far.)

Is there anything that can be done to short circuit the _first_ layer of
subtransaction? I'm thinking there will be many cases like this where there's
one implicit subtransaction that users don't even know is there. in particular

I'm thinking of psql introducing a subtransaction on every query to allow
recovery from typos and other errors. Drivers may do something similar to
allow the application to catch errors using language constructs like
exceptions and recover.

In many environments there will be one layer of subtransaction on every query.

--
greg

On Fri, 2004-11-19 at 16:58 -0500, Tom Lane wrote:

What I think we ought to do is change both PL languages so that every
SPI call is executed as a subtransaction. If the call elogs, we can
clean up by aborting the subtransaction, and then we can report the
error message as a Perl or Tcl error condition, which the function
author can trap if he chooses. If he doesn't choose to, then the
language interpreter will return an error condition to plperl.c or
pltcl.c, and we can re-throw the error.

I do this already in my plpy, save the subtransaction handling
"feature".
In plpy, all Postgres ERRORs are caught and transformed into Python
exceptions, then when the interpreter exits with a Python exception, it
is transformed back into a Postgres ERROR and raised. I even created a
class of Python exceptions for Postgres ERRORs(e.g. raise
Postgres.ERROR('msg', code=someErrCode, hint='foo')). (And more specific
classes as well, putting errcodes to good use.)

I plan(well, already working on it) to create Python interfaces to
Postgres transaction facilities so that the author can start, rollback,
and commit subxacts as needed for use/cleanup. Of course, I feel that
this is the best way to go AFA subxacts are concerned; leaving the
details to the author.

I have been playing with RollbackToSavepoint and ReleaseSavepoint, but
per Neil's comments on IRC and the fact that I have to annoyingly
construct a List containing the savepoint name. I get the feeling that I
am not meant to use them. If they are provided for possible use,
shouldn't they take a string instead of a List? (Is a List used here to
discourage use?)

--
Regards,
James William Pye

I wrote:

I get about 6900 vs 12800 msec, so for a simple pre-planned query
it's not quite a 50% overhead.

However, that was yesterday ;-). I did some profiling and found some
easy-to-knock-off hotspots. Today I'm measuring about 25% overhead
for a simple SELECT, which I think is entirely acceptable considering
the cleanliness of definition that we're buying.

I changed my test cases to be

create or replace function foo(int,int) returns int as '
declare x int;
begin
for i in 1 .. $1 loop
select into x unique1 from tenk1 where unique2 = $2;
end loop;
return x;
end' language plpgsql;

create or replace function foos(int,int) returns int as '
declare x int;
begin
for i in 1 .. $1 loop
begin
select into x unique1 from tenk1 where unique2 = $2;
exception
when others then null;
end;
end loop;
return x;
end' language plpgsql;

so as to minimize the extraneous overhead --- I think this is a harder
test (gives a higher number) than what I was doing yesterday.

regards, tom lane

James William Pye <flaw@rhid.com> writes:

I have been playing with RollbackToSavepoint and ReleaseSavepoint, but
per Neil's comments on IRC and the fact that I have to annoyingly
construct a List containing the savepoint name. I get the feeling that I
am not meant to use them.

You're right. You can *not* expose those as user-callable operations in
a PL language. Consider for example what will happen if the user tries
to roll back to a savepoint that was established outside your function
call, or tries to exit the function while still inside a local
savepoint. You have to enforce strict nesting of functions and
subtransactions; therefore it's a lot easier to present an API that
looks like an exception-block construct (per plpgsql), or that just
hides the whole deal in the SPI calling interface (as I'm proposing for
plperl/pltcl).

There's been some discussion of creating a "stored procedure" language
that would execute outside the database engine, but still on the server
side of the network connection. In that sort of context it would be
reasonable to let the user do SAVEPOINT/ROLLBACK (or any other SQL
command). But our existing PLs most definitely execute inside the
engine, and therefore they can't expose facilities that imply arbitrary
changes in the subtransaction state stack.

regards, tom lane

Tom Lane wrote:

There's an ancient saying "I can make this code arbitrarily fast ...
if it doesn't have to give the right answer". I think that applies
here. Fast and unsafe is not how the Postgres project customarily
designs things.

I'm missing something, that's clear. Because I can't see why the PL/Java
way of doing it is anything but both fast and 100% safe. I agree 100%
that unsafe is not an option.

I'm arguing that since my design is totally safe, intuitive, and cover
90% of the use-cases, it is the best one.

Regards,
Thomas Hallgren

PS.
The current design that prevents non-volatile functions from doing
things with side effects is not very safe ;-) I persist claiming that
there's a better (and safe) way to handle that.

Tom Lane wrote:

James William Pye <flaw@rhid.com> writes:

I have been playing with RollbackToSavepoint and ReleaseSavepoint, but
per Neil's comments on IRC and the fact that I have to annoyingly
construct a List containing the savepoint name. I get the feeling that I
am not meant to use them.

You're right. You can *not* expose those as user-callable operations in
a PL language. Consider for example what will happen if the user tries
to roll back to a savepoint that was established outside your function
call, or tries to exit the function while still inside a local
savepoint. You have to enforce strict nesting of functions and
subtransactions; therefore it's a lot easier to present an API that
looks like an exception-block construct (per plpgsql), or that just
hides the whole deal in the SPI calling interface (as I'm proposing for
plperl/pltcl).

There's been some discussion of creating a "stored procedure" language
that would execute outside the database engine, but still on the server
side of the network connection. In that sort of context it would be
reasonable to let the user do SAVEPOINT/ROLLBACK (or any other SQL
command). But our existing PLs most definitely execute inside the
engine, and therefore they can't expose facilities that imply arbitrary
changes in the subtransaction state stack.

I'm planning to add subtransactions too, but my approach will be to use
the savepoint functionality already present in the java.sql.Connection
interface. Perhaps the plpy implementation could do something similar.
This is what I'm planning to implement:

In Java, safepoints are identified by an interface rather then just by a
name. I will (invisibly) include both the name of the safepoint and the
call level in my implementation of that interface. I will also have a
nested "call context" where I manage safepoints created by the executing
function. All of this will be completely hidden from the function
developer. This will make it possible to enforce the following rules:

1. A Safepoint lifecycle must be confined to a function call.
2. Safepoints must be rolled back or released by the same function that
sets them.

Failure to comply with those rules will result in an exception (elog
ERROR) that will be propagated all the way up.

Would you consider this as safe?

Regards,
Thomas Hallgren

Thomas Hallgren wrote

I'm planning to add subtransactions too, but my approach will be to
use the savepoint functionality already present in the
java.sql.Connection interface. Perhaps the plpy implementation could
do something similar. This is what I'm planning to implement:

In Java, safepoints are identified by an interface rather then just by
a name. I will (invisibly) include both the name of the safepoint and
the call level in my implementation of that interface. I will also
have a nested "call context" where I manage safepoints created by the
executing function. All of this will be completely hidden from the
function developer. This will make it possible to enforce the
following rules:

1. A Safepoint lifecycle must be confined to a function call.
2. Safepoints must be rolled back or released by the same function
that sets them.

Failure to comply with those rules will result in an exception (elog
ERROR) that will be propagated all the way up.

Would you consider this as safe?

Regards,
Thomas Hallgren

s/safepoint/savepoint/g

On Sat, 2004-11-20 at 16:39 -0500, Tom Lane wrote:

You're right. You can *not* expose those as user-callable operations in
a PL language. Consider for example what will happen if the user tries
to roll back to a savepoint that was established outside your function
call, or tries to exit the function while still inside a local
savepoint. You have to enforce strict nesting of functions and
subtransactions; therefore it's a lot easier to present an API that
looks like an exception-block construct (per plpgsql), or that just
hides the whole deal in the SPI calling interface (as I'm proposing for
plperl/pltcl).

Hrm, what about a savepoint scoping facility that would be wrapped
around calls to [volatile?] functions to explicitly enforce these
regulations?

[...Poking around the archives a bit...]

[Or do I mean savepoint levels?]:
http://archives.postgresql.org/pgsql-hackers/2004-07/msg00505.php
http://archives.postgresql.org/pgsql-hackers/2004-09/msg00569.php

--
Regards,
James William Pye

On 11/19/2004 7:54 PM, Tom Lane wrote:

Thomas Hallgren <thhal@mailblocks.com> writes:

My approach with PL/Java is a bit different. While each SPI call is
using a try/catch they are not using a subtransaction. The catch will
however set a flag that will ensure two things:

1. No more calls can be made from PL/Java to the postgres backend.
2. Once PL/Java returns, the error will be re-thrown.

That's what pltcl has always done, and IMHO it pretty well sucks :-(
it's neither intuitive nor useful.

At the time that code was written it simply acted as a stopgap to
prevent subsequent SPI calls after elog while still unwinding the Tcl
call stack properly to avoid resource leaking inside of Tcl.

I don't agree that the right cure is to execute each and every statement
itself as a subtransaction. What we ought to do is to define a wrapper
for the catch Tcl command, that creates a subtransaction and executes
the code within during that.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

Jan Wieck <JanWieck@Yahoo.com> writes:

I don't agree that the right cure is to execute each and every statement
itself as a subtransaction. What we ought to do is to define a wrapper
for the catch Tcl command, that creates a subtransaction and executes
the code within during that.

What I would like to do is provide a catch-like Tcl command that defines
a subtransaction, and then optimize the SPI commands so that they don't
create their own sub-subtransaction if they can see they are directly
within the subtransaction command. But when they aren't, they need to
define their own subtransactions so that the error semantics are
reasonable. I think what you're saying is that a catch command should
be exactly equivalent to a subtransaction, but I'm unconvinced --- a
catch might be used around some Tcl operations that don't touch the
database, in which case the subtransaction overhead would be a serious
waste.

The real point here is that omitting the per-command subtransaction
ought to be a hidden optimization, not something that intrudes to the
point of having unclean semantics when we can't do it.

regards, tom lane

On 11/29/2004 10:43 PM, Tom Lane wrote:

Jan Wieck <JanWieck@Yahoo.com> writes:

I don't agree that the right cure is to execute each and every statement
itself as a subtransaction. What we ought to do is to define a wrapper
for the catch Tcl command, that creates a subtransaction and executes
the code within during that.

What I would like to do is provide a catch-like Tcl command that defines
a subtransaction, and then optimize the SPI commands so that they don't
create their own sub-subtransaction if they can see they are directly
within the subtransaction command. But when they aren't, they need to
define their own subtransactions so that the error semantics are
reasonable. I think what you're saying is that a catch command should
be exactly equivalent to a subtransaction, but I'm unconvinced --- a
catch might be used around some Tcl operations that don't touch the
database, in which case the subtransaction overhead would be a serious
waste.

That is right. What the catch replacement command should do is to
establish some sort of "catch-level", run the script inside the catch
block. The first spi operation inside of that block causes a
subtransaction to be created and remembered in that catch-level. At the
end - i.e. when that block of commands finishes, the subtransaction is
committed or rolled back and nothing done if the command block didn't
hit any spi statement.

The real point here is that omitting the per-command subtransaction
ought to be a hidden optimization, not something that intrudes to the
point of having unclean semantics when we can't do it.

We could treat the entire function call as one subtransaction in the
first place. Then create more sub-subtransactions as catch blocks appear.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

Tom Lane wrote:

The real point here is that omitting the per-command subtransaction
ought to be a hidden optimization, not something that intrudes to the
point of having unclean semantics when we can't do it.

Sorry to be stupid here, but I didn't understand this when it was
disussed originally either. Why a subtransaction per command rather than
one per function? If I've got this right, this is so the PL can tidy up
behind itself and report/log an appropriate error?

--
Richard Huxton
Archonet Ltd

Richard Huxton wrote:

Tom Lane wrote:

The real point here is that omitting the per-command subtransaction
ought to be a hidden optimization, not something that intrudes to the
point of having unclean semantics when we can't do it.

Sorry to be stupid here, but I didn't understand this when it was
disussed originally either. Why a subtransaction per command rather
than one per function? If I've got this right, this is so the PL can
tidy up behind itself and report/log an appropriate error?

I don't understand this either. Why a subtransaction at all?

Don't get me wrong. I fully understand that a subtransaction would make
error recovery possible. What I try to say is that the kind of error
recovery that needs a subtransaction is fairly, or perhaps even very, rare.

We all agree that further calls to SPI must be prohibited if an error
occurs when no subtransaction is active. Such an error can only result
in one thing. The function must terminate and the error must be propagated.

The way most functions that I've seen is written, this is the most
common behavior anyway. It's very uncommon that you want to do further
database accesses after something has gone wrong. I admit that some
special cases indeed do exist but I cannot for my life understand why
those cases must incur a 25% overhead on everything else. Especially if
there is an alternate way of handling them without making any sacrifice
whatsoever on safety.

A function in PL/Java that calls to the backend and encounters an error
can be 1 of 2 types:
1. If no subtransaction is active, the function will be completely and
utterly blocked from doing further calls to the backend. When it
returns, the error will be re-thrown.
2. When a subtransaction is active, the function will be blocked the
same way as for #1 with one exception. A subtransaction rollback will go
through and it will remove the block.

So, in Java I have the choice of writing:

try
{
// do something
}
catch(SQLException e)
{
// Clean up (but no backend calls) and terminate
}

or I can write:

Savepoint sp = myConn->setSavepoint("foo");
try
{
// do something
sp.commit();
}
catch(SQLException e)
{
sp.rollback();

// Handle error and continue execution.
}

All cases are covered, there's no subtransaction overhead (unless you
really want it), the semantics are clean, and it's 100% safe. What's
wrong with this approach?

Regards,
Thomas Hallgren