Re: COMMIT leads to ROLLBACK

refer: [HACKERS] Commit turns into rollback?
On 3/17/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> 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.

Yes, we should change the message. A silent SUCCESS message leads
the onlooker (script/application) to believe that everything was okay.
As Peter mentioned in the above-mentioned thread:

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

As far as breaking-the-applications goes, I think we'll be
catching the broken applications rather than breaking any well wrtten
application (which detects a DML error as soon as it occurs, and
issues a rollback).

These are a few scenarios after entering an ABORTed transaction state:

1)
=# END;
ROLLBACK

2)
=# ROLLBACK;
ROLLBACK

3)
=# COMMIT;
ERROR: The transaction has been ended with a ROLLBACK.

So, in effect, all these statements do end the ABORTed transaction
with a ROLLBACK. Whereas situations (1) and (2) behave just as
expected, situation (3) needs to be implemented with a
appropriate/correct error message.

Regards,
Gurjeet.

On 5/21/06, Jaime Casanova <systemguards(at)gmail(dot)com> wrote:
> On 5/21/06, Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> wrote:
> > Hi All,
> >
> > While trying to implement a recent TODO item, I noticed this behaviour:
> >
> > test=# select * from t2;
> > ERROR: current transaction is aborted, commands ignored until end of
> > transaction block
> > test=# commit;
> > ROLLBACK
> >
> > Either:
> > 1) COMMIT in an aborted transaction should lead to an 'ERROR:
> > Cannot COMMIT an aborted transaction.'
> > 2) At least a 'WARNING: transaction is being rolled back to last
> > known consistent state.' should precede the success (ROLLBACK)
> > message.
> >
> > Regards,
> > Gurjeet.
> >
>
> there was a thread about that two months ago...
>
> here's Tom's response:
> http://archives.postgresql.org/pgsql-hackers/2006-03/msg00786.php