Re: [HACKERS] Statement-level rollback

On 2017-Nov-06, Tsunakawa, Takayuki wrote:

> From: Simon Riggs
> > A backend-based solution is required for PL procedures and functions.
> >
> > We could put this as an option into PL/pgSQL, but it seems like it is
> > a function of the transaction manager rather than the driver.
>
> Exactly. Thanks.

I've been looking at re-implementing this feature recently, using
Tsunakawa's proposed UI of a GUC transaction_rollback_scope that can
take values "transaction" (default, current behavior) and "statement".
I didn't take other parts of his patch though; see below.

I think the main objectionable point is that of making servers behave in
a way that could lose data, if applications assume that transactions
behave in the way they do today. I propose that we solve this by
allowing this feature to be enabled only via one of:

* a PGOPTIONS connection-time option
* ALTER USER SET (transaction_rollback_scope)

but it can be *disabled* normally via SET. In other words, changing the
scope from transaction to statement in a running session is forbidden,
but changing it the other way around is allowed (if app is unsure
whether env is unsafe, it can set the scope to "transaction" to ensure
it's safe from that point onwards). Changing the scope in
postgresql.conf is forbidden, so a server is never unsafe as a whole.

Drivers such as JDBC can easily use this mode, for example a connection
option such as "AUTOSAVE=SERVER" can automatically add the
transaction_rollback_scope option. (Naturally, if the server does not
support transaction_rollback_scope and the user gave that option, this
causes an exception to be raised -- NOT to fallback to the standard
transaction behavior!)

Tsunakawa's implementation puts the feature in postgres.c's client loop.
I think a better way to implement this is to change xact.c to have a new
TBLOCK state which indicates when to start a new internal
subtransaction; StartTransactionCommand pushes a new element into the
transaction stack and puts it in the new state; a subsequent operation
actually starts the new subtransaction. (This design decision allows
things like SAVEPOINT to work correctly by having the
subtrasaction-for-savepoint appear *before* the internal subtransaction,
so a subsequent "SELECT 0/0" doesn't remove the user declared
savepoint.)

I have a PoC implementation that's slightly different: it adds more code
to a few xact.c low-level routines (StartTransactionCommand creates the
internal savepoint itself). It's not as nice because SAVEPOINT has to
close the internal subtransaction, then create the savepoint, then
create the internal subtransaction again. And it doesn't handle
RELEASE. But as a PoC it's quite nice. I measured the performance
overhead to be about 2% - 3% loss of the current mode, which seems
acceptable.

I would like to hear opinions on whether the protections I propose are
sufficient to appease the objections. In my opinion they are, and we
should press forward with this, which seems to be one of the frequently
requested features from people porting from other DBMSs.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services