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

Re: Re: Proposal to fix Statement.executeBatch()

From: Barry Lind <barry(at)xythos(dot)com>
To: Rene Pijlman <rpijlman(at)wanadoo(dot)nl>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Re: Proposal to fix Statement.executeBatch()
Date: 2001-08-28 05:57:13
Message-ID: 3B8B32B9.6040307@xythos.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
 > 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
> 
> 



In response to

Responses

pgsql-jdbc by date

Next:From: BhuvaneswariDate: 2001-08-28 09:43:14
Subject: Regarding Vacuumdb
Previous:From: Barry LindDate: 2001-08-28 05:48:33
Subject: Re: [PATCHES] Attempt to clean up ExecSql() in JDBC

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