Re: pl/python SPI in subtransactions

On 26/01/11 04:51, Steve Singer wrote:
> On 10-12-23 08:45 AM, Jan Urbański wrote:
> I see you've merged the changes from the refactoring branch down but
> haven't yet posted an updated patch. This review is based on
> 2f2b4a33bf344058620a5c684d1f2459e505c727

Thanks for the review, I'm attaching an updated patch against master.

> The patch updates the excepted results of the regression tests so they
> no longer expect the 'unrecognized error' warnings. No new unit test
> are added to verify that behavior changes will continue to function as
> intended (though they could be)

It's in fact just a correctness change, so I didn't include any new unit
tests.

> No documentation updates are included. The current documentation is
> silent on the behaviour of plpython when SPI calls generate errors so
> this change doesn't invalidate any documentation but it would be nice if
> we described what effect SQL invoked through SPI from the functions have
> on the transaction. Maybe a "Trapping Errors" section?

Good point, the fact that you can now actually catch SPI errors should
be documented, I'll try to add a paragraph about that.

> A concern I have is that some users might find this surprising. For
> plpgsql the exception handling will rollback SQL from before the
> exception and I suspect the other PL's are the same. It would be good
> if a few more people chimed in on if they see this as a good idea.

It's not true for other PLs, but see below.

> Another concern is that we are probably breaking some peoples code.
>
> Consider the following:
>
> BEGIN;
> create table foo(a int4 primary key);
> DO $$
> r=plpy.execute("insert into foo values ('1')")
> try :
> r=plpy.execute("insert into foo values ('1')")
> except:
> plpy.log("something went wrong")
> $$ language plpython2u;
> select * FROM foo;
> a
> ---
> 1
> (1 row)
>
>
> This code is going to behave different with the patch.

Right, without the patch you can never catch errors originating from
plpy.execute, so any error terminates the whole function, and so rolls
back the statement. FWIW PL/Perl works the same:

begin;
create table foo(i int primary key);
DO $$
spi_exec_query("insert into foo values ('1')");
eval { spi_exec_query("insert into foo values ('1')"); };
elog(LOG, $@) if $@;
$$ language plperl;
select * from foo;

You will see a row in foo. AFAICS PL/Tcl also does it, but I don't have
it complied it to try. It does consitute a behaviour change, but we
didn't get any complains when the same thing happened for Perl.

> I am finding the treatment of savepoints very strange.
> If as a function author I'm able to recover from errors then I'd expect
> (or maybe want) to be able to manage them through savepoints

Ooops, you found a bug there. In the attached patch you get the same
error (SPI_ERROR_TRANSACTION) as in master. Also added a unit test for that.

> I've only glanced at your transaction manager patch, from what I can
> tell it will give me another way of managing the inner transaction but
> I'm not sure it will make the savepoint calls work(will it?). I also
> wonder how useful in practice this patch will be if the other patch
> doesn't also get applied (function others will be stuck with an all or
> nothing as their options for error handling)

As long as you don't catch SPIError, nothing changes. And error in a SPI
call will propagate to the top of the Python stack and your function
will be terminated, its effects being rolled back. The function will
only work differently if you have bare except: clauses (that are
deprecated in 2.x and forbidden in 3.x IIRC) or catch Exception.

For example:

begin;
create table foo(i int primary key);
DO $$
plpy.execute("insert into foo values ('1')")
plpy.execute("insert into foo values ('1')")
$$ language plpython2u;

No row will be inserted and the whole transaction will be rolled back.

As for explicit subxact management, that's something that would be
unique to PL/Python, and my other patch implements it like this:

begin;
create table foo(i int primary key);
DO $$
try:
with plpy.subxact():
plpy.execute("insert into foo values ('1')")
plpy.execute("insert into foo values ('1')")
except plpy.SPIError:
plpy.log("no rows inserted")
$$ language plpython2u;

Thanks again for the review and for spotting that bug!

Cheers,
Jan