Re: [HACKERS] Statement-level rollback

From: Alvaro Herrera
> 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.

Thank you so much for reviving this thread!

> 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)

Why don't we also allow ALTER DATABASE SET for a database exclusively
for data migrated from another DBMS?

> 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.

Would it be dangerous to allow both enabling and disabling the
statement-level rollback only outside a transaction block? I thought
it was considered dangerous to change the setting inside a transaction
block.

> 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!)

How do the drivers know, from the server error response to connection
request, that transaction_rollback_scope is unsupported?

> 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.)

That sounds interesting.

* How can PLs like PL/pgSQL utilize this to continue upon an SQL
failure? They don't call StartTransactionCommand.

* How can psql make use of this feature for its ON_ERROR_ROLLBACK?

Regards
MauMau