Thread: Shouldn't "aborted transaction" be an ERROR? (was Re: [NOVICE] Optimising inside transactions)

John Taylor <postgres@jtresponse.co.uk> writes:
> On Wednesday 12 June 2002 16:36, Tom Lane wrote:
>> Queries after the failure aren't run at all; they're only passed through
>> the parser's grammar so it can look for a COMMIT or ROLLBACK command.
>> Normal processing resumes after ROLLBACK.  If you were paying attention
>> to the return codes you'd notice complaints like
>>
>> regression=# begin;
>> BEGIN
>> regression=# select 1/0;
>> ERROR:  floating point exception! The last floating point operation either exceeded legal ranges or was a divide by
zero
>> -- subsequent queries will be rejected like so:
>> regression=# select 1/0;
>> WARNING:  current transaction is aborted, queries ignored until end of transaction block
>> *ABORT STATE*

> Well, I'm using JDBC, and it isn't throwing any exceptions, so I
> assumed it was working :-/

This brings up a point that's bothered me in the past.  Why is the
"queries ignored" response treated as a NOTICE and not an ERROR?
A client that is not paying close attention to the command result code
(as JDBC is evidently not doing :-() might think that its command had
been executed.

It seems to me the right behavior is

regression=# select 1/0;
ERROR:  current transaction is aborted, queries ignored until end of transaction block
regression=#

I think the reason why it's been done with a NOTICE is that if we
elog(ERROR) on the first command of a query string, we'll not be able to
process a ROLLBACK appearing later in the same string --- but that
behavior does not seem nearly as helpful as throwing an error.

            regards, tom lane

I have just tested this on the latest code using the following

    Connection con = JDBC2Tests.openDB();
        try
        {

      // transaction mode
      con.setAutoCommit(false);
      Statement stmt = con.createStatement();
      stmt.execute("select 1/0");
            fail( "Should not execute this, as a SQLException s/b thrown" );
      con.commit();
        }
        catch ( Exception ex )
        {
        }
    try
    {
      con.commit();
      con.close();
    }catch ( Exception ex) {}
  }

and it executes as expected. It throws the SQLException and  does not
execute the fail statement

Thanks,

Dave

On Wed, 2002-06-12 at 12:12, Tom Lane wrote:
> John Taylor <postgres@jtresponse.co.uk> writes:
> > On Wednesday 12 June 2002 16:36, Tom Lane wrote:
> >> Queries after the failure aren't run at all; they're only passed through
> >> the parser's grammar so it can look for a COMMIT or ROLLBACK command.
> >> Normal processing resumes after ROLLBACK.  If you were paying attention
> >> to the return codes you'd notice complaints like
> >>
> >> regression=# begin;
> >> BEGIN
> >> regression=# select 1/0;
> >> ERROR:  floating point exception! The last floating point operation either exceeded legal ranges or was a divide
byzero 
> >> -- subsequent queries will be rejected like so:
> >> regression=# select 1/0;
> >> WARNING:  current transaction is aborted, queries ignored until end of transaction block
> >> *ABORT STATE*
>
> > Well, I'm using JDBC, and it isn't throwing any exceptions, so I
> > assumed it was working :-/
>
> This brings up a point that's bothered me in the past.  Why is the
> "queries ignored" response treated as a NOTICE and not an ERROR?
> A client that is not paying close attention to the command result code
> (as JDBC is evidently not doing :-() might think that its command had
> been executed.
>
> It seems to me the right behavior is
>
> regression=# select 1/0;
> ERROR:  current transaction is aborted, queries ignored until end of transaction block
> regression=#
>
> I think the reason why it's been done with a NOTICE is that if we
> elog(ERROR) on the first command of a query string, we'll not be able to
> process a ROLLBACK appearing later in the same string --- but that
> behavior does not seem nearly as helpful as throwing an error.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>