Thread: Commit turns into rollback?
What sense is this supposed to make? => begin; BEGIN => blah; ERROR: ... => commit; ROLLBACK ^^^^^^^^ Even if this is justifiable, this behavior is not documented (in any obvious place). -- Peter Eisentraut http://developer.postgresql.org/~petere/
Ü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