Re: Statement-level rollback

On 10/31/17 13:47, MauMau wrote:
> I'm very sorry I couldn't reply to your kind offer. I rebased the
> patch and will add it to CF 2017/11. I hope I will complete the patch
> in this CF.

I've been thinking about this a little bit. Many are worried about
repeating the mistakes of the autocommit feature, so it's worth
comparing that.

The problem with the autocommit setting, or at least the one I remember,
is that code is currently written expecting that

connect
exec SQL statement
disconnect

will succeed in executing and committing the SQL statement, unless an
error is reported.

If you turned the autocommit setting off, then this code would
effectively silently do nothing, and that is obviously quite bad. So
the autocommit setting would break a large proportion of all code out
there, and was thus not really usable, and hence it was removed.

The proposed statement-level rollback feature works in a slightly
different context. It does not change when or how a transaction or
transaction block begins and ends. It only changes what happens inside
explicit transaction blocks. Considering code like

START TRANSACTION;
SQL1;
SQL2;
SQL3;
COMMIT;

currently an error would cause all subsequent commands to fail. Under
statement-level rollback, a failed command would effectively be ignored
and the transaction would continue until COMMIT.

Therefore, a successful transaction block would always work the same way
under either setting.

The difference is how error recovery works. So this will necessarily be
tied to how the client code or other surrounding code is structured or
what the driver or framework is doing in the background to manage
transactions. It would also be bad if client code was not prepared for
this new behavior, reported the transaction as complete while some
commands in the middle were omitted.

Drivers can already achieve this behavior and do do that by issuing
savepoint commands internally. The point raised in this thread was that
that creates too much network overhead, so a backend-based solution
would be preferable. We haven't seen any numbers or other evidence to
quantify that claim, so maybe it's worth looking into that some more.

In principle, a backend-based solution that drivers just have to opt
into would save a lot of duplication. But the drivers that care or
require it according to their standards presumably already implement
this behavior in some other way, so it comes back to whether there is a
performance or other efficiency gain here.

Another argument was that other SQL implementations have this behavior.
This appears to be the case. But as far as I can tell, it is also tied
to their particular interfaces and the structure and flow control they
provide. So a client-side solution like psql already provides or
something in the various drivers would work just fine here.

So my summary for the moment is that a GUC or similar run-time setting
might be fine, with appropriate explanation and warnings. But it's not
clear whether it's worth it given the existing alternatives.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services