Commit turns into rollback?

Ühel kenal päeval, R, 2006-03-17 kell 10:12, kirjutas Peter Eisentraut:

What sense is this supposed to make?

=> begin;
BEGIN
=> blah;
ERROR: ...
=> commit;
ROLLBACK
^^^^^^^^

Return status tells you what actually happened.

Even if this is justifiable, this behavior is not documented (in any obvious
place).

What would you suggest as an obvious place ?

-------------
Hannu

Am Freitag, 17. März 2006 11:25 schrieb Hannu Krosing:

=> commit;
ROLLBACK
^^^^^^^^

Return status tells you what actually happened.

If I send a COMMIT, I want a commit to happen or an error.

Even if this is justifiable, this behavior is not documented (in any
obvious place).

What would you suggest as an obvious place ?

The COMMIT reference page would be a start.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Ühel kenal päeval, R, 2006-03-17 kell 11:27, kirjutas Peter Eisentraut:

Am Freitag, 17. März 2006 11:25 schrieb Hannu Krosing:

=> commit;
ROLLBACK
^^^^^^^^

Return status tells you what actually happened.

If I send a COMMIT, I want a commit to happen or an error.

You already got several errors.

Any suggestion how to get out of the ERROR state ?

=> begin;
BEGIN
=> blah;
ERROR: ...
=> commit;
ERROR: ...
=> select 1;
ERROR: current transaction is aborted, commands ignored until end of
transaction block

Or would you like a mode where you need explicitly send a ROLLBACK to
get out ?

Even if this is justifiable, this behavior is not documented (in any
obvious place).

What would you suggest as an obvious place ?

The COMMIT reference page would be a start.

Good point :)

------------
Hannu

Am Freitag, 17. März 2006 13:03 schrieb Hannu Krosing:

If I send a COMMIT, I want a commit to happen or an error.

You already got several errors.

Right, but a defensively programmed application, I want to get all the errors
all the time at every possible place.

Or would you like a mode where you need explicitly send a ROLLBACK to
get out ?

I faintly recall that this used to be the behavior.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Ühel kenal päeval, R, 2006-03-17 kell 14:10, kirjutas Peter Eisentraut:

Am Freitag, 17. März 2006 13:03 schrieb Hannu Krosing:

If I send a COMMIT, I want a commit to happen or an error.

You already got several errors.

Right, but a defensively programmed application, I want to get all the errors
all the time at every possible place.

Or would you like a mode where you need explicitly send a ROLLBACK to
get out ?

I faintly recall that this used to be the behavior.

in 7.4 it was worse

ahf=# begin;
BEGIN
ahf=# select 1/0;
ERROR: division by zero
ahf=# commit;
COMMIT

it still did a rollback, but reported a COMMIT

-----------
Hannu

Peter Eisentraut <peter_e@gmx.net> writes:

Am Freitag, 17. März 2006 13:03 schrieb Hannu Krosing:

Or would you like a mode where you need explicitly send a ROLLBACK to
get out ?

I faintly recall that this used to be the behavior.

No, it never was like that, and I don't think we can change it without
breaking an awful lot of stuff --- particularly psql scripts, which
would basically be unable to do anything at all with such a definition.
A script can't change its behavior depending on whether there was an
error earlier.

It would also move us further away from the SQL standard. The spec says
that COMMIT ends the transaction, full stop, not "ends it only if you're
not in an error state". Of course the spec hasn't got a notion of a
transaction error state at all, but my point is that making COMMIT leave
you in the broken transaction is not an improvement compliance-wise.

I am surprised that the COMMIT reference page makes no mention of the
point though. That needs to be fixed.

regards, tom lane

Am Freitag, 17. M�rz 2006 16:07 schrieb Tom Lane:

It would also move us further away from the SQL standard. The spec says
that COMMIT ends the transaction, full stop, not "ends it only if you're
not in an error state". Of course the spec hasn't got a notion of a
transaction error state at all, but my point is that making COMMIT leave
you in the broken transaction is not an improvement compliance-wise.

The standard does address the issue of transactions that cannot be committed
because of an error. In 16.6. <commit statement> GR 6 it basically says that
if the transaction cannot be completed (here: because of a constraint
violation), then an exception condition should be raised. That is, the
transaction is over but you get an error. I think that behavior would be
better. For example, Java programs will get an exception and know something
is wrong. Right now, I don't even know whether it is possible in all
programming interfaces to get at the command tag and infer failure to commit
from there.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut <peter_e@gmx.net> writes:

Am Freitag, 17. M�rz 2006 16:07 schrieb Tom Lane:

It would also move us further away from the SQL standard. The spec says
that COMMIT ends the transaction, full stop, not "ends it only if you're
not in an error state". Of course the spec hasn't got a notion of a
transaction error state at all, but my point is that making COMMIT leave
you in the broken transaction is not an improvement compliance-wise.

The standard does address the issue of transactions that cannot be committed
because of an error. In 16.6. <commit statement> GR 6 it basically says that
if the transaction cannot be completed (here: because of a constraint
violation), then an exception condition should be raised. That is, the
transaction is over but you get an error. I think that behavior would be
better.

So it's not the fact that it rolls back that bugs you, it's the way that
the action is reported? We could talk about changing that maybe --- it
wouldn't break existing scripts AFAICS. It might break applications
though.

regards, tom lane

On 3/17/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:

The standard does address the issue of transactions that cannot be committed
because of an error. In 16.6. <commit statement> GR 6 it basically says that
if the transaction cannot be completed (here: because of a constraint
violation), then an exception condition should be raised. That is, the
transaction is over but you get an error. I think that behavior would be
better.

So it's not the fact that it rolls back that bugs you, it's the way that
the action is reported? We could talk about changing that maybe --- it
wouldn't break existing scripts AFAICS. It might break applications
though.

Error means the actual command failed. _Doing_ something, successfully,
and still reporting error seems rather wrong.

IMHO only other behaviour than current one that is not broken
is requiring ROLLBACK for failed transactions. And that is no good
for backwards-compatibility reasons.

So -1 for changing anything.

--
marko