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

Hi,

I'm running a transaction with about 1600 INSERTs.
Each INSERT involves a subselect.

I've noticed that if one of the INSERTs fails, the remaining INSERTs run in about
1/2 the time expected.

Is postgresql optimising the inserts, knowing that it will rollback at the end ?

If not, why do the queries run faster after the failure ?

Thanks
JohnT

John Taylor <postgres(at)jtresponse(dot)co(dot)uk> writes:
> I'm running a transaction with about 1600 INSERTs.
> Each INSERT involves a subselect.

> I've noticed that if one of the INSERTs fails, the remaining INSERTs run in about
> 1/2 the time expected.

> Is postgresql optimising the inserts, knowing that it will rollback at the end ?

> If not, why do the queries run faster after the failure ?

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*

I'd actually expect much more than a 2:1 speed differential, because the
grammar is not a significant part of the runtime AFAICT. Perhaps you
are including some large amount of communication overhead in that
comparison?

regards, tom lane

On Wednesday 12 June 2002 16:36, Tom Lane wrote:
> John Taylor <postgres(at)jtresponse(dot)co(dot)uk> writes:
> > I'm running a transaction with about 1600 INSERTs.
> > Each INSERT involves a subselect.
>
> > I've noticed that if one of the INSERTs fails, the remaining INSERTs run in about
> > 1/2 the time expected.
>
> > Is postgresql optimising the inserts, knowing that it will rollback at the end ?
>
> > If not, why do the queries run faster after the failure ?
>
> 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 :-/

>
> I'd actually expect much more than a 2:1 speed differential, because the
> grammar is not a significant part of the runtime AFAICT. Perhaps you
> are including some large amount of communication overhead in that
> comparison?
>

Yes, now that I think about it - I am getting a bigger differential
I'm actually running queries to update two slightly different databases in parallel,
so the failing one is taking almost no time at all.

Thanks
JohnT

John Taylor <postgres(at)jtresponse(dot)co(dot)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

On Wed, 12 Jun 2002 16:07:26 +0100, John Taylor
<postgres(at)jtresponse(dot)co(dot)uk> wrote:
>
>Hi,
>
>I'm running a transaction with about 1600 INSERTs.
>Each INSERT involves a subselect.
>
>I've noticed that if one of the INSERTs fails, the remaining INSERTs run in about
>1/2 the time expected.
>
>Is postgresql optimising the inserts, knowing that it will rollback at the end ?
>
ISTM "optimising" is not the right word, it doesn't even try to
execute them.

fred=# BEGIN;
BEGIN
fred=# INSERT INTO a VALUES (1, 'x');
INSERT 174658 1
fred=# blabla;
ERROR: parser: parse error at or near "blabla"
fred=# INSERT INTO a VALUES (2, 'y');
NOTICE: current transaction is aborted, queries ignored until end of
transaction block
*ABORT STATE*
fred=# ROLLBACK;
ROLLBACK

Servus
Manfred

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(at)jtresponse(dot)co(dot)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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>
>