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
