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