Thread: Multiple semicolon separated statements and autocommit
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>
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
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>
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 >
"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.
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
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>