pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedConfuse about the behaveior of PreparedStatement.executeBatch (jdbc)
9+ messages / 5 participants
[nested] [flat]
* Confuse about the behaveior of PreparedStatement.executeBatch (jdbc)
@ 2012-12-13 10:44 Haifeng Liu <liuhaifeng@live.com>
2012-12-14 03:29 ` Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc) Haifeng Liu <liuhaifeng@live.com>
0 siblings, 1 reply; 9+ messages in thread
From: Haifeng Liu @ 2012-12-13 10:44 UTC (permalink / raw)
To: pgsql-admin
Hi,
I have a DB writer program written in Java, it do batch updates, and will try to update each record in a batch if it failed. I think no any record in a batch will be update if the batch failed, but weirdly I found a few records update twice(very little amount among all records).
Well, there is less stuff about postgresql jdbc driver. I wanna know if executeBatch really keep all the records in a batch untouched when the batch failed. Any help is appreciate.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc)
2012-12-13 10:44 Confuse about the behaveior of PreparedStatement.executeBatch (jdbc) Haifeng Liu <liuhaifeng@live.com>
@ 2012-12-14 03:29 ` Haifeng Liu <liuhaifeng@live.com>
2012-12-14 15:56 ` Re: Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc) dmp <danap@ttc-cmc.net>
2012-12-14 16:05 ` Re: Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc) Maciek Sakrejda <m.sakrejda@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Haifeng Liu @ 2012-12-14 03:29 UTC (permalink / raw)
To: pgsql-jdbc@postgresql.org
Begin forwarded message:
> From: Haifeng Liu <liuhaifeng@live.com>
> Subject: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc)
> Date: December 13, 2012 6:44:56 PM GMT+08:00
> To: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
>
> Hi,
>
> I have a DB writer program written in Java, it do batch updates, and will try to update each record in a batch if it failed. I think no any record in a batch will be update if the batch failed, but weirdly I found a few records update twice(very little amount among all records).
>
> Well, there is less stuff about postgresql jdbc driver. I wanna know if executeBatch really keep all the records in a batch untouched when the batch failed. Any help is appreciate.
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc)
2012-12-13 10:44 Confuse about the behaveior of PreparedStatement.executeBatch (jdbc) Haifeng Liu <liuhaifeng@live.com>
2012-12-14 03:29 ` Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc) Haifeng Liu <liuhaifeng@live.com>
@ 2012-12-14 15:56 ` dmp <danap@ttc-cmc.net>
2012-12-14 16:25 ` Re: Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc) Haifeng Liu <liuhaifeng@live.com>
1 sibling, 1 reply; 9+ messages in thread
From: dmp @ 2012-12-14 15:56 UTC (permalink / raw)
To: Haifeng Liu <liuhaifeng@live.com>; pgsql-jdbc@postgresql.org
Hello,
Though a simple example of your code would provide a better response
from the mailing list, I will speculate based on the context of the
Java 6 API, statement class and your comments. Yes, the batch appears
to be proceeding.
Statement.executeBatch().
executeBatch
int[] executeBatch() throws SQLException
~
~
~
If one of the commands in a batch update fails to execute properly,
this method throws a BatchUpdateException, and a JDBC driver may or may not
continue to process the remaining commands in the batch. However, the driver's
behavior must be consistent with a particular DBMS, either always continuing to
process commands or never continuing to process commands. If the driver
continues processing after a failure, the array returned by the method
BatchUpdateException.getUpdateCounts will contain as many elements as there are
commands in the batch, and at least one of the elements will be the following:
~
~
~
Returns:
an array of update counts containing one element for each command in
the batch. The elements of the array are ordered according to the order in which
commands were added to the batch.
Throws:
SQLException - if a database access error occurs, this method is called on
a closed Statement or the driver does not support batch statements. Throws
BatchUpdateException (a subclass of SQLException) if one of the commands sent to
the database fails to execute properly or attempts to return a result set.
You may be able to answer your own question by examining the int[] array that
is returned by way of BatchUpdateException.getUpdateCounts()
danap.
Haifeng Liu wrote:
>> Hi,
>>
>> I have a DB writer program written in Java, it do batch updates, and
>> will try to update each record in a batch if it failed. I think no any
>> record in a batch will be update if the batch failed, but weirdly I
>> found a few records update twice(very little amount among all records).
>>
>> Well, there is less stuff about postgresql jdbc driver. I wanna know
>> if executeBatch really keep all the records in a batch untouched when
>> the batch failed. Any help is appreciate.
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc)
2012-12-13 10:44 Confuse about the behaveior of PreparedStatement.executeBatch (jdbc) Haifeng Liu <liuhaifeng@live.com>
2012-12-14 03:29 ` Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc) Haifeng Liu <liuhaifeng@live.com>
2012-12-14 15:56 ` Re: Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc) dmp <danap@ttc-cmc.net>
@ 2012-12-14 16:25 ` Haifeng Liu <liuhaifeng@live.com>
2012-12-14 16:30 ` Re: Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc) dmp <danap@ttc-cmc.net>
2012-12-14 16:51 ` Re: Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc) Kris Jurka <books@ejurka.com>
0 siblings, 2 replies; 9+ messages in thread
From: Haifeng Liu @ 2012-12-14 16:25 UTC (permalink / raw)
To: dmp <danap@ttc-cmc.net>; +Cc: pgsql-jdbc@postgresql.org
example code:
Connection conn = getConnection();
PreparedStatement pstmt = conn.prepareStatement("insert/update...");
for (A a : AList) {
pstmt.setParameter(...);
pstmt.addBatch();
}
pstmt.executeBatch();
I did a simple test and found that if one of the batch failed, the other update may be execute partially. when I test with 10 updates a batch, none of them are updated, when I test with 1000 updates a batch, about 700+ of them are executed, but the failed update should be the last one, which means 999 executed updates is more reasonable than 700+. But really weird thing is the getUpdateCounts method returns the reasonable information.
I don't know what happened inside this driver. Currently I have to setAutoCommit to false and use commit/rollback to achieve my goal.
On Dec 14, 2012, at 11:56 PM, dmp <danap@ttc-cmc.net> wrote:
> Hello,
>
> Though a simple example of your code would provide a better response
> from the mailing list, I will speculate based on the context of the
> Java 6 API, statement class and your comments. Yes, the batch appears
> to be proceeding.
>
> Statement.executeBatch().
>
> executeBatch
>
> int[] executeBatch() throws SQLException
> ~
> ~
> ~
> If one of the commands in a batch update fails to execute properly, this method throws a BatchUpdateException, and a JDBC driver may or may not continue to process the remaining commands in the batch. However, the driver's behavior must be consistent with a particular DBMS, either always continuing to process commands or never continuing to process commands. If the driver continues processing after a failure, the array returned by the method BatchUpdateException.getUpdateCounts will contain as many elements as there are commands in the batch, and at least one of the elements will be the following:
> ~
> ~
> ~
> Returns:
> an array of update counts containing one element for each command in the batch. The elements of the array are ordered according to the order in which commands were added to the batch.
>
> Throws:
> SQLException - if a database access error occurs, this method is called on a closed Statement or the driver does not support batch statements. Throws BatchUpdateException (a subclass of SQLException) if one of the commands sent to the database fails to execute properly or attempts to return a result set.
>
> You may be able to answer your own question by examining the int[] array that
> is returned by way of BatchUpdateException.getUpdateCounts()
>
> danap.
>
> Haifeng Liu wrote:
>>> Hi,
>>>
>>> I have a DB writer program written in Java, it do batch updates, and
>>> will try to update each record in a batch if it failed. I think no any
>>> record in a batch will be update if the batch failed, but weirdly I
>>> found a few records update twice(very little amount among all records).
>>>
>>> Well, there is less stuff about postgresql jdbc driver. I wanna know
>>> if executeBatch really keep all the records in a batch untouched when
>>> the batch failed. Any help is appreciate.
>
>
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc)
2012-12-13 10:44 Confuse about the behaveior of PreparedStatement.executeBatch (jdbc) Haifeng Liu <liuhaifeng@live.com>
2012-12-14 03:29 ` Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc) Haifeng Liu <liuhaifeng@live.com>
2012-12-14 15:56 ` Re: Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc) dmp <danap@ttc-cmc.net>
2012-12-14 16:25 ` Re: Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc) Haifeng Liu <liuhaifeng@live.com>
@ 2012-12-14 16:30 ` dmp <danap@ttc-cmc.net>
2012-12-14 16:50 ` Fwd: [JDBC] Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc) Haifeng Liu <liuhaifeng@live.com>
1 sibling, 1 reply; 9+ messages in thread
From: dmp @ 2012-12-14 16:30 UTC (permalink / raw)
To: Haifeng Liu <liuhaifeng@live.com>; pgsql-jdbc@postgresql.org
Hello,
Perhaps you should pose the question to the server mailing list. It seems that
maybe the sequence is possibly being changed in execution. In a small update,
10, the sequence stays intact, but for a larger update the server perhaps is
optimizing the sequence for execution thereby changing the order. In any case
you appeared to answer your own question.
http://archives.postgresql.org/pgsql-general/
danap.
Haifeng Liu wrote:
> example code:
>
> Connection conn = getConnection();
> PreparedStatement pstmt = conn.prepareStatement("insert/update...");
> for (A a : AList) {
> pstmt.setParameter(...);
> pstmt.addBatch();
> }
> pstmt.executeBatch();
>
> I did a simple test and found that if one of the batch failed, the other update may be execute partially. when I test with 10 updates a batch, none of them are updated, when I test with 1000 updates a batch, about 700+ of them are executed, but the failed update should be the last one, which means 999 executed updates is more reasonable than 700+. But really weird thing is the getUpdateCounts method returns the reasonable information.
>
> I don't know what happened inside this driver. Currently I have to setAutoCommit to false and use commit/rollback to achieve my goal.
>
>
> On Dec 14, 2012, at 11:56 PM, dmp<danap@ttc-cmc.net> wrote:
>
>> Hello,
>>
>> Though a simple example of your code would provide a better response
>> from the mailing list, I will speculate based on the context of the
>> Java 6 API, statement class and your comments. Yes, the batch appears
>> to be proceeding.
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
^ permalink raw reply [nested|flat] 9+ messages in thread
* Fwd: [JDBC] Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc)
2012-12-13 10:44 Confuse about the behaveior of PreparedStatement.executeBatch (jdbc) Haifeng Liu <liuhaifeng@live.com>
2012-12-14 03:29 ` Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc) Haifeng Liu <liuhaifeng@live.com>
2012-12-14 15:56 ` Re: Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc) dmp <danap@ttc-cmc.net>
2012-12-14 16:25 ` Re: Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc) Haifeng Liu <liuhaifeng@live.com>
2012-12-14 16:30 ` Re: Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc) dmp <danap@ttc-cmc.net>
@ 2012-12-14 16:50 ` Haifeng Liu <liuhaifeng@live.com>
0 siblings, 0 replies; 9+ messages in thread
From: Haifeng Liu @ 2012-12-14 16:50 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Hello,
Can anyone please figure out this ?
Begin forwarded message:
> From: dmp <danap@ttc-cmc.net>
> Subject: Re: [JDBC] Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc)
> Date: December 15, 2012 12:30:50 AM GMT+08:00
> To: Haifeng Liu <liuhaifeng@live.com>, pgsql-jdbc@postgresql.org
>
> Hello,
>
> Perhaps you should pose the question to the server mailing list. It seems that
> maybe the sequence is possibly being changed in execution. In a small update,
> 10, the sequence stays intact, but for a larger update the server perhaps is
> optimizing the sequence for execution thereby changing the order. In any case
> you appeared to answer your own question.
>
> http://archives.postgresql.org/pgsql-general/
>
> danap.
>
> Haifeng Liu wrote:
>> example code:
>>
>> Connection conn = getConnection();
>> PreparedStatement pstmt = conn.prepareStatement("insert/update...");
>> for (A a : AList) {
>> pstmt.setParameter(...);
>> pstmt.addBatch();
>> }
>> pstmt.executeBatch();
>>
>> I did a simple test and found that if one of the batch failed, the other update may be execute partially. when I test with 10 updates a batch, none of them are updated, when I test with 1000 updates a batch, about 700+ of them are executed, but the failed update should be the last one, which means 999 executed updates is more reasonable than 700+. But really weird thing is the getUpdateCounts method returns the reasonable information.
>>
>> I don't know what happened inside this driver. Currently I have to setAutoCommit to false and use commit/rollback to achieve my goal.
>>
>>
>> On Dec 14, 2012, at 11:56 PM, dmp<danap@ttc-cmc.net> wrote:
>>
>>> Hello,
>>>
>>> Though a simple example of your code would provide a better response
>>> from the mailing list, I will speculate based on the context of the
>>> Java 6 API, statement class and your comments. Yes, the batch appears
>>> to be proceeding.
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc)
2012-12-13 10:44 Confuse about the behaveior of PreparedStatement.executeBatch (jdbc) Haifeng Liu <liuhaifeng@live.com>
2012-12-14 03:29 ` Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc) Haifeng Liu <liuhaifeng@live.com>
2012-12-14 15:56 ` Re: Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc) dmp <danap@ttc-cmc.net>
2012-12-14 16:25 ` Re: Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc) Haifeng Liu <liuhaifeng@live.com>
@ 2012-12-14 16:51 ` Kris Jurka <books@ejurka.com>
1 sibling, 0 replies; 9+ messages in thread
From: Kris Jurka @ 2012-12-14 16:51 UTC (permalink / raw)
To: Haifeng Liu <liuhaifeng@live.com>; +Cc: dmp <danap@ttc-cmc.net>; pgsql-jdbc@postgresql.org
On Sat, 15 Dec 2012, Haifeng Liu wrote:
> I did a simple test and found that if one of the batch failed, the other
> update may be execute partially. when I test with 10 updates a batch,
> none of them are updated, when I test with 1000 updates a batch, about
> 700+ of them are executed, but the failed update should be the last one,
> which means 999 executed updates is more reasonable than 700+. But
> really weird thing is the getUpdateCounts method returns the reasonable
> information.
>
> I don't know what happened inside this driver. Currently I have to
> setAutoCommit to false and use commit/rollback to achieve my goal.
>
Internally the driver splits each batch into sub-batches of 250 to send to
the server to reduce the possibility of deadlocking the network
connection. When auto-commit is enabled in the driver, the auto-commit
setting affects the whole sub-batch of 250 because of how the
frontend/backend protocol works. So you are seeing some oddities because
of some implementation details and you should not use auto-commit with
batch statements.
Kris Jurka
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc)
2012-12-13 10:44 Confuse about the behaveior of PreparedStatement.executeBatch (jdbc) Haifeng Liu <liuhaifeng@live.com>
2012-12-14 03:29 ` Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc) Haifeng Liu <liuhaifeng@live.com>
@ 2012-12-14 16:05 ` Maciek Sakrejda <m.sakrejda@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Maciek Sakrejda @ 2012-12-14 16:05 UTC (permalink / raw)
To: Haifeng Liu <liuhaifeng@live.com>; +Cc: pgsql-jdbc@postgresql.org
On Thu, Dec 13, 2012 at 7:29 PM, Haifeng Liu <liuhaifeng@live.com> wrote:
> Well, there is less stuff about postgresql jdbc driver. I wanna know if
> executeBatch really keep all the records in a batch untouched when the batch
> failed. Any help is appreciate.
No, executeBatch() is orthogonal to transactions:
http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html#executeBatch()
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Confuse about the behaveior of PreparedStatement.executeBatch (jdbc)
@ 2012-12-13 16:06 Kevin Grittner <kgrittn@mail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Kevin Grittner @ 2012-12-13 16:06 UTC (permalink / raw)
To: Haifeng Liu <liuhaifeng@live.com>; pgsql-admin
Haifeng Liu wrote:
> I wanna know if executeBatch really keep all the records in a
> batch untouched when the batch failed.
I recommend asking on the pgsql-jdbc list. You might want to
mention what autoCommit is set to during the attempt.
-Kevin
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2012-12-14 16:51 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-12-13 10:44 Confuse about the behaveior of PreparedStatement.executeBatch (jdbc) Haifeng Liu <liuhaifeng@live.com>
2012-12-14 03:29 ` Haifeng Liu <liuhaifeng@live.com>
2012-12-14 15:56 ` dmp <danap@ttc-cmc.net>
2012-12-14 16:25 ` Haifeng Liu <liuhaifeng@live.com>
2012-12-14 16:30 ` dmp <danap@ttc-cmc.net>
2012-12-14 16:50 ` Haifeng Liu <liuhaifeng@live.com>
2012-12-14 16:51 ` Kris Jurka <books@ejurka.com>
2012-12-14 16:05 ` Maciek Sakrejda <m.sakrejda@gmail.com>
2012-12-13 16:06 Re: Confuse about the behaveior of PreparedStatement.executeBatch (jdbc) Kevin Grittner <kgrittn@mail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox