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

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