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

Re: Proposal to fix Statement.executeBatch()

From: Barry Lind <barry(at)xythos(dot)com>
To: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
Cc: Rene Pijlman <rpijlman(at)wanadoo(dot)nl>, pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Proposal to fix Statement.executeBatch()
Date: 2001-08-28 19:59:57
Message-ID: 3B8BF83D.5040907@xythos.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Bruce,

I think the existing patch can be applied as is.  The issues I raised 
below are further improvements in the functionality that can be done and 
don't directly relate to the patch that was submitted.  Sorry if I 
confused things.

--Barry

Bruce Momjian wrote:
> Can someone suggest what is to be done with the propsed patch?
> 
> 
>> > What exactly is the behaviour of the backend in that scenario?
>> > Does it commit every separate SQL statement in the
>> > semicolon-separated list, or does it commit the list as a whole?
>> > Does it abort processing the statement list when an error occurs
>> > in one statement? And if it continues, does it return an error
>> > when only one statement in the middle of the list had an error?
>>
>>I do not know what the server does if you have autocommit enabled and 
>>you issue multiple statements in one try.  However, I would be OK with 
>>the driver issuing the statements one by one with autocommit on.  If you 
>>are running in this mode you just wouldn't get any performance improvement.
>>
>> > However, it would mean a change in behaviour of the driver that
>> > may break existing JDBC applications: the driver will no longer
>> > return update counts for all statements in a batch like it
>> > currently does, it will return "unknown" for most statements.
>> > I'm not sure if the performance improvement justifies this
>> > non-backwardly-compatible change, though I agree this is the
>> > intention of the feature. What do you think?
>>
>>I wouldn't worry about this 'change in behavior' because if the caller 
>>is JDBC complient it should be coded to handle the new behavior as it is 
>>complient with the spec.
>>
>>thanks,
>>--Barry
>>
>>
>>
>>
>>Rene Pijlman wrote:
>>
>>>On Mon, 27 Aug 2001 11:07:55 -0700, you wrote:
>>>[executeBatch() implemented as one round trip]
>>>
>>>
>>>>Here is how I would suggest this be done in a way that is spec 
>>>>compliant (Note: that I haven't looked at the patch you submited yet, so 
>>>>forgive me if you have already done it this way, but based on your 
>>>>comments in this email, my guess is that you have not).
>>>>
>>>>
>>>Indeed, I have not implemented this.
>>>
>>>
>>>
>>>>Statements should be batched together in a single statement with 
>>>>semicolons separating the individual statements (this will allow the 
>>>>backend to process them all in one round trip).
>>>>
>>>>The result array should return an element with the row count for each 
>>>>statement, however the value for all but the last statement will be 
>>>>'-2'.  (-2 is defined by the spec to mean the statement was processed 
>>>>successfully but the number of affected rows is unknown).
>>>>
>>>>
>>>Ah, I see. I hadn't thought of that solution.
>>>
>>>
>>>
>>>>In the event of an error, then the driver should return an array the 
>>>>size of the submitted batch with values of -3 for all elements. -3 is 
>>>>defined by the spec as the corresponding statement failed to execute 
>>>>successfully, or for statements that could not be processed for some 
>>>>reason.  Since in postgres when one statement fails (in non-autocommit 
>>>>mode), the entire transaction is aborted this is consistent with a 
>>>>return value of -3 in my reading of the spec.
>>>>
>>>>
>>>Not quite. A statement in a batch may also fail because its a
>>>succesful SELECT as far as the server is concerned (can't have
>>>select's in a batch). But that situation can also be handled
>>>correctly by setting the update count for that particular
>>>statement to -3. Its then up to the application to decide if it
>>>wants to rollback, I would say.
>>>
>>>But what to do when an error occurs with autocommit enabled?
>>>This is not recommended, but allowed by the spec, if I
>>>understand it correctly.
>>>
>>>What exactly is the behaviour of the backend in that scenario?
>>>Does it commit every separate SQL statement in the
>>>semicolon-separated list, or does it commit the list as a whole?
>>>Does it abort processing the statement list when an error occurs
>>>in one statement? And if it continues, does it return an error
>>>when only one statement in the middle of the list had an error?
>>>
>>>
>>>
>>>>I believe this approach makes the most sense because:
>>>>1) It implements batches in one round trip (the intention of the feature)
>>>>2) It is complient with the standard
>>>>3) It is complient with the current functionality of the backend
>>>>
>>>>
>>>If we can come up with an acceptable solution for an error with
>>>autocommit enabled, I agree. Otherwise, I'm not sure.
>>>
>>>However, it would mean a change in behaviour of the driver that
>>>may break existing JDBC applications: the driver will no longer
>>>return update counts for all statements in a batch like it
>>>currently does, it will return "unknown" for most statements.
>>>I'm not sure if the performance improvement justifies this
>>>non-backwardly-compatible change, though I agree this is the
>>>intention of the feature. What do you think?
>>>
>>>Regards,
>>>Ren? Pijlman
>>>
>>>
>>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 3: if posting/reading through Usenet, please send an appropriate
>>subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>>message can get through to the mailing list cleanly
>>
>>
> 



In response to

Responses

pgsql-jdbc by date

Next:From: Jens CarlbergDate: 2001-08-28 20:36:32
Subject: Re: next() and PreparedStatement
Previous:From: Barry LindDate: 2001-08-28 19:56:50
Subject: Re: Unterminated quoted string error.

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