Re: Re: Proposal to fix Statement.executeBatch() - Mailing list pgsql-jdbc

No problem.  Just checking.  Patch will remain in the queue and be
applied.

> 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@postgresql.org so that your
> >>message can get through to the mailing list cleanly
> >>
> >>
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026