Here's a patch implementing explicitly starting subtransactions mentioned in
http://archives.postgresql.org/pgsql-hackers/2010-12/msg01991.php. It's
an incremental patch on top of the spi-in-subxacts patch sent eariler.
Git branch for this patch:
https://github.com/wulczer/postgres/tree/explicit-subxacts.
The idea has been proposed in
http://archives.postgresql.org/pgsql-hackers/2010-11/msg00122.php
This patch provides a subtransaction context manager, in the vein of
http://www.python.org/dev/peps/pep-0343/.
When inside an explicit subtransaction, SPI calls do not start another
one, so you pay the subxact start overhead only once, and you get atomic
behaviour.
For instance this:
with plpy.subxact():
plpy.execute("insert into t values (1)")
plpy.execute("insert into t values (2)")
plpy.execute("ooops")
will not insert any rows into t. Just so you realise it, it *will* raise
the exception from the last execute, if you want to continue execution
you need to put your with block in a try/catch. If the code starts a
subtransaction but fails to close it, PL/Python will forcibly roll it
back to leave the backend in a clean state.
The patch lacks user-facing documentation, I'll add that later if it
gets accepted. For more usage examples refer to the unit tests that the
patch adds.
Cheers,
Jan