
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

Wed, 12 Jun 2002 16:07:26 +0100
John Taylor <postgres@jtresponse.co.uk>
Optimising inside transactions
John Taylor <postgres@jtresponse.co.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

Wed, 12 Jun 2002 11:36:30 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Optimising inside transactions 
On Wednesday 12 June 2002 16:36, Tom Lane wrote:
> John Taylor <postgres@jtresponse.co.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

Wed, 12 Jun 2002 16:42:46 +0100
John Taylor <postgres@jtresponse.co.uk>
Re: 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

Wed, 12 Jun 2002 12:12:50 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Shouldn't "aborted transaction" be an ERROR? (was Re: [NOVICE]
	Optimising inside transactions)
On Wed, 12 Jun 2002 16:07:26 +0100, John Taylor
<postgres@jtresponse.co.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

Wed, 12 Jun 2002 20:14:13 +0200
Manfred Koizar <mkoi-pg@aon.at>
Re: Optimising inside transactions
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 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@postgresql.org
> 
> 




13 Jun 2002 10:44:46 -0400
Dave Cramer <Dave@micro-automation.net>
Re: Shouldn't "aborted transaction" be an ERROR?  (was Re: