Re: Rollback on Error

Tom Lane wrote:

> "Michael Paesold" <mpaesold(at)gmx(dot)at> writes:
...
> > If that is not the case, I don't understand why core seems to be
> > against a mode (GUC), where an implicit savepoint is generated before
each
> > statement so that "rollback of the last statement" would be possible.
>
> Because we learned our lesson with the ill-fated autocommit GUC
> variable. You can't have fundamental transactional semantics depending
> on the phase of the moon, but from the point of view of application
> code, anything that can be flipped as easily as a GUC variable is an
> unknown.
>
> If you've been following recent -hackers discussions you will also
> realize that a forced savepoint for every statement is untenable
> from a performance perspective anyway.

Well, I read the threads "Re: beta1 & beta2 & Windows & heavy load" and
"Cleaning up recovery from subtransaction start failure".

I see, that savepoint/subxacts are rather expensive, at least wrt
shared-memory (lock on xid).

On the other hand, the scenario of a psql option (read: I have given up the
idea of a backend implementation) to rollback only last statement on error
is quite different.

In the mentioned thread, several thousand rows where deleted, each firing a
trigger creating a subxact. But in usual interactive administrative
sessions, you would perhaps have 5 to 20 statements in a usual transaction.
At least according to my personal experience.

Am I right in asserting that psql could recognize if a session is
interactive or scripted (\i, psql < script.sql)? And it already has user
settable options, (\set, e.g. autocommit)...

So a proper patch implementing a corresponding mode in psql that wraps each
statement (of a multi statement transaction) in a subxact, if the user
enables it, would be accepted or even called welcome?

Just thinking about...

BEGIN;
SET TRANSACTION ISOLATION SERIALIZABLE;
SELECT * FROM a WHERE id = 5;
UPDATE a SET .... WHERE id = 5;

-- check everything is ok...
SELEC * FROM a WHERE id = 5;

--> ERROR: syntax error at or near "SELEC" at character 1

-- arghh... typo do it all over again...

Best Regards,
Michael Paesold