We're discussing an implementation of JDBC's
Statement.executeBatch() on the pgsql-jdbc list. The idea is to
send multiple semicolon separated statements in one call to the
backend. The purpose of this feature is of course a performance
improvement, since it executes multiple (non-select) statements
with one round trip to the server.

If autocommit is _enabled_ and S1;S2;S3 is send to the database,
what exactly is the behaviour of the backend? For example, what
happens if S1 succeeds, S2 fails and S3 would succeed?

Does autocommit apply to the statement list send in one call as
a whole? Or does it apply to individual statements?

If autocommit applies to the list as a whole I assume the
failure of S2 would cause the entire statement list to fail and
be rolled back.

If autocommit applies to individual statements in the list, I
assume that S1 succeeds and is committed, S2 fails and is rolled
back. But is S3 still executed? And what update count is
returned to the client in that case?

I will summarize on pgsql-jdbc.

Regards,
Renï¿½ Pijlman <rene@lab.applinet.nl>

Thu, 30 Aug 2001 00:20:07 +0200
Rene Pijlman <rene@lab.applinet.nl>
Multiple semicolon separated statements and autocommit
Rene Pijlman writes:

> If autocommit is _enabled_ and S1;S2;S3 is send to the database,
> what exactly is the behaviour of the backend? For example, what
> happens if S1 succeeds, S2 fails and S3 would succeed?

All three commands are executed in a single transaction.  So if S2 fails,
S3 would not be executed.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Thu, 30 Aug 2001 19:56:53 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Multiple semicolon separated statements and autocommit
On Thu, 30 Aug 2001 19:56:53 +0200 (CEST), you wrote:
>Rene Pijlman writes:
>> If autocommit is _enabled_ and S1;S2;S3 is send to the database,
>> what exactly is the behaviour of the backend? For example, what
>> happens if S1 succeeds, S2 fails and S3 would succeed?
>
>All three commands are executed in a single transaction.  So if S2 fails,
>S3 would not be executed.

And both S1 and S2 will be rolled back, as I understand it. 

Thank you.

Regards,
Renï¿½ Pijlman <rene@lab.applinet.nl>

Thu, 30 Aug 2001 21:07:21 +0200
Rene Pijlman <rene@lab.applinet.nl>
Re: Multiple semicolon separated statements and autocommit
Are you sure?  I thought all that autocommit meant was that a statement that
is not enclosed within a begin/commit is automatically committed after it is
run.  So, in the this case all three queries will be independent, unless the
first statements is a 'begin;' and the last is a 'commit;'...

Chris

> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Peter Eisentraut
> Sent: Friday, 31 August 2001 1:57 AM
> To: Rene Pijlman
> Cc: pgsql-hackers@postgresql.org; barry@xythos.com
> Subject: Re: [HACKERS] Multiple semicolon separated statements and
> autocommit
>
>
> Rene Pijlman writes:
>
> > If autocommit is _enabled_ and S1;S2;S3 is send to the database,
> > what exactly is the behaviour of the backend? For example, what
> > happens if S1 succeeds, S2 fails and S3 would succeed?
>
> All three commands are executed in a single transaction.  So if S2 fails,
> S3 would not be executed.
>
> --
> Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>


Fri, 31 Aug 2001 09:14:21 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Multiple semicolon separated statements and autocommit
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:

> Are you sure?  I thought all that autocommit meant was that a statement that
> is not enclosed within a begin/commit is automatically committed after it is
> run.  So, in the this case all three queries will be independent, unless the
> first statements is a 'begin;' and the last is a 'commit;'...

What does the JDBC spec say about autocommit and ExecuteBatch()?

-Doug
-- 
Free Dmitry Sklyarov! 
http://www.freesklyarov.org/ 

We will return to our regularly scheduled signature shortly.

30 Aug 2001 21:35:42 -0400
Doug McNaught <doug@wireboard.com>
Re: Multiple semicolon separated statements and autocommit
Christopher Kings-Lynne writes:

> Are you sure?

Yes.

> I thought all that autocommit meant was that a statement that
> is not enclosed within a begin/commit is automatically committed after it is
> run.  So, in the this case all three queries will be independent, unless the
> first statements is a 'begin;' and the last is a 'commit;'...

Not if they're sent in the same query string.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Fri, 31 Aug 2001 10:51:09 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Multiple semicolon separated statements and autocommit
On 30 Aug 2001 21:35:42 -0400, you wrote:
>"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
>> Are you sure?  I thought all that autocommit meant was that a statement that
>> is not enclosed within a begin/commit is automatically committed after it is
>> run.  So, in the this case all three queries will be independent, unless the
>> first statements is a 'begin;' and the last is a 'commit;'...
>
>What does the JDBC spec say about autocommit and ExecuteBatch()?

Not much, but that's a different story. We're still in the
process of figuring out how to implement this feature exactly.
That discussion is on the pgsql-jdbc list.

Regards,
Renï¿½ Pijlman <rene@lab.applinet.nl>

Fri, 31 Aug 2001 12:02:40 +0200
Rene Pijlman <rene@lab.applinet.nl>
Re: Multiple semicolon separated statements and autocommit