Skip site navigation (1) Skip section navigation (2)

Re: Optimising inside transactions

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: John Taylor <postgres(at)jtresponse(dot)co(dot)uk>
Cc: "PgSQL Novice" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Optimising inside transactions
Date: 2002-06-12 15:36:30
Message-ID: 10113.1023896190@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-jdbcpgsql-novice
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

In response to

Responses

pgsql-novice by date

Next:From: Henshall, Stuart - WCPDate: 2002-06-12 15:37:30
Subject: Re: How efficient are Views
Previous:From: Tom LaneDate: 2002-06-12 15:31:49
Subject: Re: How efficient are Views

pgsql-hackers by date

Next:From: John TaylorDate: 2002-06-12 15:42:46
Subject: Re: Optimising inside transactions
Previous:From: John TaylorDate: 2002-06-12 15:07:26
Subject: Optimising inside transactions

pgsql-jdbc by date

Next:From: John TaylorDate: 2002-06-12 15:42:46
Subject: Re: Optimising inside transactions
Previous:From: John TaylorDate: 2002-06-12 15:07:26
Subject: Optimising inside transactions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group