Re: pl/python SPI in subtransactions

On 11-01-27 04:33 PM, Jan Urbański wrote:
>
> 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.
>

If we've made this type of behaviour change for pl/perl and no one
complained then I don't see an issue with doing it for plpython (if
anyone does they should speak up)

>> 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 think you need to make the same change to PLy_spi_execute_plan.

Try

BEGIN;
create table foo(a int4 primary key);
DO $$
prep=plpy.prepare("savepoint save")
plpy.execute(prep)
r=plpy.execute("insert into foo values ('1')")
try :
r=plpy.execute("insert into foo values ('1')")
except:
prep=plpy.prepare("rollback to save")
plpy.execute(prep)
plpy.log("something went wrong")
$$ language plpythonu;