Thread: Return Codes of BatchUpdateException in PostgreSql 9.6
Hello
I am migrating PostgreSql 9.5 to 9.6.0 and I am using the newest JDBC driver (9.4.1211.jre6)
So I am not sure if this is an driver issue or a bug in postgres.
We are making batch updates in our Java application.
Our JUnit Tests fail on 9.6 when querying the return code of a partially failed Batch Update.
If a statement of a batch update fails, all updateCounts are marked as failed.
In 9.5 everything works fine.
I try to give an example:
Preparation, with SQL
-----------------------------
create table TESTTABLE(id integer PRIMARY KEY) ;
INSERT INTO TESTTABLE VALUES (5);
Java Code with Java JDBC Update
-----------------------------
try{
for( int i = 0; i < 10;i++ )
{
stmt.addBatch("INSERT INTO TESTTABLE VALUES('"+i+"')" );
}
stmt.executeBatch();
con.commit();
}
catch( java.sql.BatchUpdateException x )
{
final int[] updateCounts = x.getUpdateCounts();
//==> in 9.5 only ther failed updates have return code java.sql.Statement.EXECUTE_FAILED = -3. This is CORRECT.
//==> in 9.6 all updatecounts have status java.sql.Statement.EXECUTE_FAILED = -3. This is NOT CORRECT.
}
In the sample, the 6th statement has an error, because the value 5 already exists in the database.
In this case in postgres 9.6 all fields updateCounts[0..9] are -3 (Statement.EXECUTE_FAILED)
The behavior prior 9.6 was, that only the specific entry in updateCounts[5] has the error code -3
Thank you for your help
Tillmann Schulz
Hello, I made some test with different driver versions. The result seems to be that it is an issue in the newer driver versionsand not in the postgresql database Postgres 9.5.0 with postgresql-jdbc41-9.4-1201.jar contains only updateCounts of first succeeded statements. --------------------------------------------------------------------------------------------------------------- updateCounts[0]=1 updateCounts[1]=1 updateCounts[2]=1 updateCounts[3]=1 updateCounts[4]=1 -->correct Postgres 9.6.0 with postgresql-jdbc41-9.4-1201.jar contains only updateCounts of first succeeded statements. --------------------------------------------------------------------------------------------------------------- updateCounts[0]=1 updateCounts[1]=1 updateCounts[2]=1 updateCounts[3]=1 updateCounts[4]=1 -->correct but can't use this driver with postgressql 9.6 Postgres 9.6.0 with postgresql-jdbc41-9.4-1211.jar contains updateCounts of all elements. --------------------------------------------------------------------------------------------------------------- updateCounts[0]=-3 updateCounts[1]=-3 updateCounts[2]=-3 updateCounts[3]=-3 updateCounts[4]=-3 updateCounts[5]=-3 updateCounts[6]=-3 updateCounts[7]=-3 updateCounts[8]=-3 updateCounts[9]=-3 -->Wrong.updateCounts[0..4] and updateCounts[6..9] contains wrong information. > will need to set logLevel=2 How can I set the logLevel. Is it in postgresql.conf? Bye, Tillmann >Hi, >Can you check what is returned by the back end for 9.5 and 9.6. You >will need to set > >logLevel=2 > >That will help to narrow down the problem. > >Jeremy
Hello, >With that enabled you can expect to see lots of low level protocol >logging. Exposing driver (FE) to back end (BE) and responses (BE -> FE). > >It is the BE->FE that is helpful in this case. thanks for the quick answer. Here are the logs (see also http://pastebin.com/g6k46UEU) Thanks Tillmann ========================================================================================================================================================================================================================= POSTGRES 9.5 logs ========================================================================================================================================================================================================================= DriverManager.getConnection("jdbc:postgresql://localhost:9432/app5?charSet=UTF-8&user=xxxx&password=xxx&logLevel=2&loglevel=2") trying org.postgresql.Driver 13:56:08.389 (1) PostgreSQL 9.4 JDBC4.1 (build 1201) 13:56:08.398 (1) Trying to establish a protocol version 3 connection to localhost:9432 13:56:08.427 (1) Receive Buffer Size is 8192 13:56:08.427 (1) Send Buffer Size is 8192 13:56:08.429 (1) FE=> StartupPacket(user=, database=app, client_encoding=UTF8, DateStyle=ISO, TimeZone=Europe/Berlin, extra_float_digits=2) 13:56:08.534 (1) <=BE AuthenticationReqMD5(salt=f9d204ba) 13:56:08.541 (1) FE=> Password(md5digest=md50d3fabec1f2d6d281c23820d1fa25c8e) 13:56:08.546 (1) <=BE AuthenticationOk 13:56:08.556 (1) <=BE NoticeResponse(DEBUG: CommitTransaction) SQLWarning: 13:56:08.556 (1) <=BE NoticeResponse(DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0,nestlvl: 1, children: ) SQLWarning: 13:56:08.556 (1) <=BE ParameterStatus(application_name = ) 13:56:08.556 (1) <=BE ParameterStatus(client_encoding = UTF8) 13:56:08.556 (1) <=BE ParameterStatus(DateStyle = ISO, DMY) 13:56:08.556 (1) <=BE ParameterStatus(integer_datetimes = on) 13:56:08.556 (1) <=BE ParameterStatus(IntervalStyle = postgres) 13:56:08.556 (1) <=BE ParameterStatus(is_superuser = on) 13:56:08.556 (1) <=BE ParameterStatus(server_encoding = UTF8) 13:56:08.556 (1) <=BE ParameterStatus(server_version = 9.5.0) 13:56:08.557 (1) <=BE ParameterStatus(session_authorization = xxxx) 13:56:08.557 (1) <=BE ParameterStatus(standard_conforming_strings = on) 13:56:08.557 (1) <=BE ParameterStatus(TimeZone = Europe/Berlin) 13:56:08.557 (1) <=BE BackendKeyData(pid=14144,ckey=2078122563) 13:56:08.557 (1) <=BE ReadyForQuery(I) 13:56:08.560 (1) simple execute, handler=org.postgresql.core.SetupQueryRunner$SimpleResultHandler@36aa7bc2, maxRows=0, fetchSize=0,flags=23 13:56:08.561 (1) FE=> Parse(stmt=null,query="SET extra_float_digits = 3",oids={}) 13:56:08.562 (1) FE=> Bind(stmt=null,portal=null) 13:56:08.562 (1) FE=> Execute(portal=null,limit=1) 13:56:08.562 (1) FE=> Sync 13:56:08.562 (1) <=BE NoticeResponse(DEBUG: Parsen <unnamed>: SET extra_float_digits = 3) SQLWarning: 13:56:08.562 (1) <=BE NoticeResponse(DEBUG: StartTransactionCommand) SQLWarning: 13:56:08.562 (1) <=BE NoticeResponse(DEBUG: StartTransaction) SQLWarning: 13:56:08.562 (1) <=BE NoticeResponse(DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0,nestlvl: 1, children: ) SQLWarning: 13:56:08.563 (1) <=BE ParseComplete [null] 13:56:08.563 (1) <=BE NoticeResponse(DEBUG: Binden <unnamed> an <unnamed>) SQLWarning: 13:56:08.563 (1) <=BE BindComplete [null] 13:56:08.563 (1) <=BE NoticeResponse(DEBUG: ProcessUtility) SQLWarning: 13:56:08.563 (1) <=BE CommandStatus(SET) 13:56:08.563 (1) <=BE NoticeResponse(DEBUG: CommitTransactionCommand) SQLWarning: 13:56:08.564 (1) <=BE NoticeResponse(DEBUG: CommitTransaction) SQLWarning: 13:56:08.564 (1) <=BE NoticeResponse(DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0,nestlvl: 1, children: ) SQLWarning: 13:56:08.564 (1) <=BE ReadyForQuery(I) 13:56:08.565 (1) compatible = 90400 13:56:08.565 (1) loglevel = 2 13:56:08.565 (1) prepare threshold = 5 13:56:08.567 (1) types using binary send = TIMESTAMPTZ,UUID,INT2_ARRAY,INT4_ARRAY,BYTEA,TEXT_ARRAY,TIMETZ,INT8,INT2,INT4,VARCHAR_ARRAY,INT8_ARRAY,POINT,TIMESTAMP,TIME,BOX,FLOAT4,FLOAT8,FLOAT4_ARRAY,FLOAT8_ARRAY 13:56:08.568 (1) types using binary receive = TIMESTAMPTZ,UUID,INT2_ARRAY,INT4_ARRAY,BYTEA,TEXT_ARRAY,TIMETZ,INT8,INT2,INT4,VARCHAR_ARRAY,INT8_ARRAY,POINT,DATE,TIMESTAMP,TIME,BOX,FLOAT4,FLOAT8,FLOAT4_ARRAY,FLOAT8_ARRAY 13:56:08.568 (1) integer date/time = true getConnection returning org.postgresql.Driver 13:56:08.587 (1) batch execute 10 queries, handler=org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler@2a33fae0,maxRows=0, fetchSize=0, flags=5 13:56:08.587 (1) FE=> Parse(stmt=null,query="BEGIN",oids={}) 13:56:08.587 (1) FE=> Bind(stmt=null,portal=null) 13:56:08.587 (1) FE=> Execute(portal=null,limit=0) 13:56:08.588 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('0')",oids={}) 13:56:08.588 (1) FE=> Bind(stmt=null,portal=null) 13:56:08.588 (1) FE=> Describe(portal=null) 13:56:08.588 (1) FE=> Execute(portal=null,limit=1) 13:56:08.588 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('1')",oids={}) 13:56:08.588 (1) FE=> Bind(stmt=null,portal=null) 13:56:08.588 (1) FE=> Describe(portal=null) 13:56:08.588 (1) FE=> Execute(portal=null,limit=1) 13:56:08.588 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('2')",oids={}) 13:56:08.588 (1) FE=> Bind(stmt=null,portal=null) 13:56:08.588 (1) FE=> Describe(portal=null) 13:56:08.588 (1) FE=> Execute(portal=null,limit=1) 13:56:08.589 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('3')",oids={}) 13:56:08.589 (1) FE=> Bind(stmt=null,portal=null) 13:56:08.589 (1) FE=> Describe(portal=null) 13:56:08.589 (1) FE=> Execute(portal=null,limit=1) 13:56:08.589 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('4')",oids={}) 13:56:08.589 (1) FE=> Bind(stmt=null,portal=null) 13:56:08.589 (1) FE=> Describe(portal=null) 13:56:08.589 (1) FE=> Execute(portal=null,limit=1) 13:56:08.589 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('5')",oids={}) 13:56:08.589 (1) FE=> Bind(stmt=null,portal=null) 13:56:08.589 (1) FE=> Describe(portal=null) 13:56:08.589 (1) FE=> Execute(portal=null,limit=1) 13:56:08.589 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('6')",oids={}) 13:56:08.589 (1) FE=> Bind(stmt=null,portal=null) 13:56:08.589 (1) FE=> Describe(portal=null) 13:56:08.589 (1) FE=> Execute(portal=null,limit=1) 13:56:08.589 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('7')",oids={}) 13:56:08.589 (1) FE=> Bind(stmt=null,portal=null) 13:56:08.590 (1) FE=> Describe(portal=null) 13:56:08.590 (1) FE=> Execute(portal=null,limit=1) 13:56:08.590 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('8')",oids={}) 13:56:08.590 (1) FE=> Bind(stmt=null,portal=null) 13:56:08.590 (1) FE=> Describe(portal=null) 13:56:08.590 (1) FE=> Execute(portal=null,limit=1) 13:56:08.590 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('9')",oids={}) 13:56:08.590 (1) FE=> Bind(stmt=null,portal=null) 13:56:08.590 (1) FE=> Describe(portal=null) 13:56:08.590 (1) FE=> Execute(portal=null,limit=1) 13:56:08.590 (1) FE=> Sync 13:56:08.591 (1) <=BE NoticeResponse(DEBUG: Parsen <unnamed>: BEGIN) SQLWarning: 13:56:08.591 (1) <=BE NoticeResponse(DEBUG: StartTransactionCommand) SQLWarning: 13:56:08.591 (1) <=BE NoticeResponse(DEBUG: StartTransaction) SQLWarning: 13:56:08.591 (1) <=BE NoticeResponse(DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0,nestlvl: 1, children: ) SQLWarning: 13:56:08.593 (1) <=BE ParseComplete [null] 13:56:08.593 (1) <=BE NoticeResponse(DEBUG: Binden <unnamed> an <unnamed>) SQLWarning: 13:56:08.594 (1) <=BE BindComplete [null] 13:56:08.594 (1) <=BE NoticeResponse(DEBUG: ProcessUtility) SQLWarning: 13:56:08.594 (1) <=BE NoticeResponse(DEBUG: CommitTransactionCommand) SQLWarning: 13:56:08.594 (1) <=BE CommandStatus(BEGIN) 13:56:08.594 (1) <=BE NoticeResponse(DEBUG: Parsen <unnamed>: INSERT INTO TESTTABLE VALUES('0')) SQLWarning: 13:56:08.594 (1) <=BE NoticeResponse(DEBUG: StartTransactionCommand) SQLWarning: 13:56:08.594 (1) <=BE ParseComplete [null] 13:56:08.594 (1) <=BE NoticeResponse(DEBUG: Binden <unnamed> an <unnamed>) SQLWarning: 13:56:08.594 (1) <=BE BindComplete [null] 13:56:08.594 (1) <=BE NoData 13:56:08.594 (1) <=BE NoticeResponse(DEBUG: ProcessQuery) SQLWarning: 13:56:08.596 (1) <=BE NoticeResponse(DEBUG: mapped win32 error code 2 to 2) SQLWarning: 13:56:08.600 (1) <=BE CommandStatus(INSERT 0 1) 13:56:08.601 (1) <=BE NoticeResponse(DEBUG: Parsen <unnamed>: INSERT INTO TESTTABLE VALUES('1')) SQLWarning: 13:56:08.601 (1) <=BE ParseComplete [null] 13:56:08.601 (1) <=BE NoticeResponse(DEBUG: Binden <unnamed> an <unnamed>) SQLWarning: 13:56:08.601 (1) <=BE BindComplete [null] 13:56:08.601 (1) <=BE NoData 13:56:08.601 (1) <=BE NoticeResponse(DEBUG: ProcessQuery) SQLWarning: 13:56:08.601 (1) <=BE CommandStatus(INSERT 0 1) 13:56:08.601 (1) <=BE NoticeResponse(DEBUG: Parsen <unnamed>: INSERT INTO TESTTABLE VALUES('2')) SQLWarning: 13:56:08.601 (1) <=BE ParseComplete [null] 13:56:08.601 (1) <=BE NoticeResponse(DEBUG: Binden <unnamed> an <unnamed>) SQLWarning: 13:56:08.601 (1) <=BE BindComplete [null] 13:56:08.601 (1) <=BE NoData 13:56:08.601 (1) <=BE NoticeResponse(DEBUG: ProcessQuery) SQLWarning: 13:56:08.601 (1) <=BE CommandStatus(INSERT 0 1) 13:56:08.601 (1) <=BE NoticeResponse(DEBUG: Parsen <unnamed>: INSERT INTO TESTTABLE VALUES('3')) SQLWarning: 13:56:08.601 (1) <=BE ParseComplete [null] 13:56:08.601 (1) <=BE NoticeResponse(DEBUG: Binden <unnamed> an <unnamed>) SQLWarning: 13:56:08.602 (1) <=BE BindComplete [null] 13:56:08.602 (1) <=BE NoData 13:56:08.602 (1) <=BE NoticeResponse(DEBUG: ProcessQuery) SQLWarning: 13:56:08.602 (1) <=BE CommandStatus(INSERT 0 1) 13:56:08.602 (1) <=BE NoticeResponse(DEBUG: Parsen <unnamed>: INSERT INTO TESTTABLE VALUES('4')) SQLWarning: 13:56:08.602 (1) <=BE ParseComplete [null] 13:56:08.602 (1) <=BE NoticeResponse(DEBUG: Binden <unnamed> an <unnamed>) SQLWarning: 13:56:08.602 (1) <=BE BindComplete [null] 13:56:08.602 (1) <=BE NoData 13:56:08.602 (1) <=BE NoticeResponse(DEBUG: ProcessQuery) SQLWarning: 13:56:08.602 (1) <=BE CommandStatus(INSERT 0 1) 13:56:08.602 (1) <=BE NoticeResponse(DEBUG: Parsen <unnamed>: INSERT INTO TESTTABLE VALUES('5')) SQLWarning: 13:56:08.602 (1) <=BE ParseComplete [null] 13:56:08.602 (1) <=BE NoticeResponse(DEBUG: Binden <unnamed> an <unnamed>) SQLWarning: 13:56:08.602 (1) <=BE BindComplete [null] 13:56:08.603 (1) <=BE NoData 13:56:08.603 (1) <=BE NoticeResponse(DEBUG: ProcessQuery) SQLWarning: 13:56:08.608 (1) <=BE ErrorMessage(FEHLER: doppelter Schlüsselwert verletzt Unique-Constraint „testtable_pkey“ Detail: Schlüssel „(id)=(5)“ existiert bereits.) org.postgresql.util.PSQLException: FEHLER: doppelter Schlüsselwert verletzt Unique-Constraint „testtable_pkey“ Detail: Schlüssel „(id)=(5)“ existiert bereits. at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:421) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2929) ========================================================================================================================================================================================================================= POSTGRES 9.6 logs ========================================================================================================================================================================================================================= DriverManager.getConnection("jdbc:postgresql://localhost:10433/app?charSet=UTF-8&user=xxxx&password=xxxx&logLevel=2&loglevel=2") trying org.postgresql.Driver 13:52:38.914 (1) PostgreSQL 9.4.1211 13:52:38.920 (1) Trying to establish a protocol version 3 connection to localhost:10433 13:52:38.934 (1) Receive Buffer Size is 8192 13:52:38.934 (1) Send Buffer Size is 8192 13:52:38.936 (1) FE=> StartupPacket(user=xxxx, database=app, client_encoding=UTF8, DateStyle=ISO, TimeZone=Europe/Berlin,extra_float_digits=2) 13:52:38.978 (1) <=BE AuthenticationReqMD5(salt=1b61dbea) 13:52:38.985 (1) FE=> Password(md5digest=md557d9e4eaa7fd9cedc1141ad09145b449) 13:52:38.986 (1) <=BE AuthenticationOk 13:52:39.003 (1) <=BE ParameterStatus(application_name = ) 13:52:39.003 (1) <=BE ParameterStatus(client_encoding = UTF8) 13:52:39.004 (1) <=BE ParameterStatus(DateStyle = ISO, DMY) 13:52:39.004 (1) <=BE ParameterStatus(integer_datetimes = on) 13:52:39.004 (1) <=BE ParameterStatus(IntervalStyle = postgres) 13:52:39.004 (1) <=BE ParameterStatus(is_superuser = on) 13:52:39.004 (1) <=BE ParameterStatus(server_encoding = UTF8) 13:52:39.004 (1) <=BE ParameterStatus(server_version = 9.6.0) 13:52:39.004 (1) <=BE ParameterStatus(session_authorization = xxxx) 13:52:39.004 (1) <=BE ParameterStatus(standard_conforming_strings = on) 13:52:39.004 (1) <=BE ParameterStatus(TimeZone = Europe/Berlin) 13:52:39.004 (1) <=BE BackendKeyData(pid=9324,ckey=2044033931) 13:52:39.004 (1) <=BE ReadyForQuery(I) 13:52:39.006 (1) simple execute, handler=org.postgresql.core.SetupQueryRunner$SimpleResultHandler@4aa8f0b4, maxRows=0, fetchSize=0,flags=1047 13:52:39.007 (1) FE=> Parse(stmt=null,query="SET extra_float_digits = 3",oids={}) 13:52:39.007 (1) FE=> Bind(stmt=null,portal=null) 13:52:39.007 (1) FE=> Execute(portal=null,limit=1) 13:52:39.007 (1) FE=> Sync 13:52:39.009 (1) <=BE ParseComplete [null] 13:52:39.009 (1) <=BE BindComplete [unnamed] 13:52:39.009 (1) <=BE CommandStatus(SET) 13:52:39.010 (1) <=BE ReadyForQuery(I) 13:52:39.010 (1) compatible = 90400 13:52:39.010 (1) loglevel = 2 13:52:39.010 (1) prepare threshold = 5 13:52:39.012 (1) types using binary send = TIMESTAMPTZ,UUID,INT2_ARRAY,INT4_ARRAY,BYTEA,TEXT_ARRAY,TIMETZ,INT8,INT2,INT4,VARCHAR_ARRAY,INT8_ARRAY,POINT,TIMESTAMP,TIME,BOX,FLOAT4,FLOAT8,FLOAT4_ARRAY,FLOAT8_ARRAY 13:52:39.013 (1) types using binary receive = TIMESTAMPTZ,UUID,INT2_ARRAY,INT4_ARRAY,BYTEA,TEXT_ARRAY,TIMETZ,INT8,INT2,INT4,VARCHAR_ARRAY,INT8_ARRAY,POINT,DATE,TIMESTAMP,TIME,BOX,FLOAT4,FLOAT8,FLOAT4_ARRAY,FLOAT8_ARRAY 13:52:39.013 (1) integer date/time = true getConnection returning org.postgresql.Driver 13:52:39.023 (1) batch execute 10 queries, handler=org.postgresql.jdbc.BatchResultHandler@6d86b085, maxRows=0, fetchSize=0,flags=5 13:52:39.023 (1) FE=> Parse(stmt=null,query="BEGIN",oids={}) 13:52:39.023 (1) FE=> Bind(stmt=null,portal=null) 13:52:39.024 (1) FE=> Execute(portal=null,limit=0) 13:52:39.024 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('0')",oids={}) 13:52:39.024 (1) FE=> Bind(stmt=null,portal=null) 13:52:39.024 (1) FE=> Describe(portal=null) 13:52:39.024 (1) FE=> Execute(portal=null,limit=1) 13:52:39.024 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('1')",oids={}) 13:52:39.025 (1) FE=> Bind(stmt=null,portal=null) 13:52:39.025 (1) FE=> Describe(portal=null) 13:52:39.025 (1) FE=> Execute(portal=null,limit=1) 13:52:39.025 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('2')",oids={}) 13:52:39.025 (1) FE=> Bind(stmt=null,portal=null) 13:52:39.025 (1) FE=> Describe(portal=null) 13:52:39.025 (1) FE=> Execute(portal=null,limit=1) 13:52:39.025 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('3')",oids={}) 13:52:39.025 (1) FE=> Bind(stmt=null,portal=null) 13:52:39.025 (1) FE=> Describe(portal=null) 13:52:39.025 (1) FE=> Execute(portal=null,limit=1) 13:52:39.025 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('4')",oids={}) 13:52:39.025 (1) FE=> Bind(stmt=null,portal=null) 13:52:39.025 (1) FE=> Describe(portal=null) 13:52:39.025 (1) FE=> Execute(portal=null,limit=1) 13:52:39.025 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('5')",oids={}) 13:52:39.025 (1) FE=> Bind(stmt=null,portal=null) 13:52:39.025 (1) FE=> Describe(portal=null) 13:52:39.025 (1) FE=> Execute(portal=null,limit=1) 13:52:39.026 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('6')",oids={}) 13:52:39.026 (1) FE=> Bind(stmt=null,portal=null) 13:52:39.026 (1) FE=> Describe(portal=null) 13:52:39.026 (1) FE=> Execute(portal=null,limit=1) 13:52:39.026 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('7')",oids={}) 13:52:39.026 (1) FE=> Bind(stmt=null,portal=null) 13:52:39.026 (1) FE=> Describe(portal=null) 13:52:39.026 (1) FE=> Execute(portal=null,limit=1) 13:52:39.026 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('8')",oids={}) 13:52:39.026 (1) FE=> Bind(stmt=null,portal=null) 13:52:39.026 (1) FE=> Describe(portal=null) 13:52:39.026 (1) FE=> Execute(portal=null,limit=1) 13:52:39.026 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('9')",oids={}) 13:52:39.026 (1) FE=> Bind(stmt=null,portal=null) 13:52:39.026 (1) FE=> Describe(portal=null) 13:52:39.026 (1) FE=> Execute(portal=null,limit=1) 13:52:39.026 (1) FE=> Sync 13:52:39.030 (1) <=BE ParseComplete [null] 13:52:39.030 (1) <=BE BindComplete [unnamed] 13:52:39.030 (1) <=BE CommandStatus(BEGIN) 13:52:39.030 (1) <=BE ParseComplete [null] 13:52:39.030 (1) <=BE BindComplete [unnamed] 13:52:39.030 (1) <=BE NoData 13:52:39.030 (1) <=BE CommandStatus(INSERT 0 1) 13:52:39.030 (1) <=BE ParseComplete [null] 13:52:39.030 (1) <=BE BindComplete [unnamed] 13:52:39.030 (1) <=BE NoData 13:52:39.030 (1) <=BE CommandStatus(INSERT 0 1) 13:52:39.031 (1) <=BE ParseComplete [null] 13:52:39.031 (1) <=BE BindComplete [unnamed] 13:52:39.031 (1) <=BE NoData 13:52:39.031 (1) <=BE CommandStatus(INSERT 0 1) 13:52:39.031 (1) <=BE ParseComplete [null] 13:52:39.031 (1) <=BE BindComplete [unnamed] 13:52:39.031 (1) <=BE NoData 13:52:39.031 (1) <=BE CommandStatus(INSERT 0 1) 13:52:39.031 (1) <=BE ParseComplete [null] 13:52:39.031 (1) <=BE BindComplete [unnamed] 13:52:39.031 (1) <=BE NoData 13:52:39.031 (1) <=BE CommandStatus(INSERT 0 1) 13:52:39.031 (1) <=BE ParseComplete [null] 13:52:39.031 (1) <=BE BindComplete [unnamed] 13:52:39.031 (1) <=BE NoData 13:52:39.034 (1) <=BE ErrorMessage(FEHLER: doppelter Schlüsselwert verletzt Unique-Constraint »testtable_pkey« Detail: Schlüssel »(id)=(5)« existiert bereits.) org.postgresql.util.PSQLException: FEHLER: doppelter Schlüsselwert verletzt Unique-Constraint »testtable_pkey« Detail: Schlüssel »(id)=(5)« existiert bereits. at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2458) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2158) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:463) at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:794) at com.app.generator.TestDataGenerator.generateRecipients(TestDataGenerator.java:31) at com.app.generator.TestReturnCodes.main(TestReturnCodes.java:66) SQLException: SQLState(23505) SQLState(23505) vendor code(0) java.sql.BatchUpdateException: Batch entry 5 INSERT INTO TESTTABLE VALUES('5') was aborted: FEHLER: doppelter Schlüsselwertverletzt Unique-Constraint »testtable_pkey« Detail: Schlüssel »(id)=(5)« existiert bereits. Call getNextException to see other errors in the batch. at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:151) at org.postgresql.core.ResultHandlerDelegate.handleError(ResultHandlerDelegate.java:45) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2159) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:463) at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:794) at com.inxmail.generator.TestDataGenerator.generateRecipients(TestDataGenerator.java:31) at com.inxmail.generator.TestReturnCodes.main(TestReturnCodes.java:66) 13:52:39.044 (1) <=BE ReadyForQuery(E)
>> updateCounts[0]=1 >> updateCounts[1]=1 >> updateCounts[2]=1 >> updateCounts[3]=1 >> updateCounts[4]=1 >> -->correct >The value 1 is the batched statement return code for >j.s.Statement.SUCCESS_NO_INFO I think the value 1 is the update count ( 1 statement successfully inserted) Statement.SUCCESS_NO_INFO has code -2.
Hello,
here are the logs with postgres 9.50./9.6.0 and postgresql-jdbc41-9.4-1201.jar
Bye
Tillmann
=========================================================================================================================================
Postgres 9.5.0 / postgresql-jdbc41-9.4-1201.jar
=========================================================================================================================================
DriverManager.getConnection("jdbc:postgresql://localhost:9432/xxxx5?charSet=UTF-8&user=xxxx&password=xxxx&logLevel=2&loglevel=2")
trying org.postgresql.Driver
09:04:59.269 (1) PostgreSQL 9.4 JDBC4.1 (build 1201)
09:04:59.284 (1) Trying to establish a protocol version 3 connection to localhost:9432
09:04:59.318 (1) Receive Buffer Size is 8192
09:04:59.319 (1) Send Buffer Size is 8192
09:04:59.321 (1) FE=> StartupPacket(user=xxxx, database=xxxx5, client_encoding=UTF8, DateStyle=ISO,
TimeZone=Europe/Berlin,extra_float_digits=2)
09:04:59.389 (1) <=BE AuthenticationReqMD5(salt=d5b8ec19)
09:04:59.395 (1) FE=> Password(md5digest=md5d64a93fa4940047d56e0be735eaba6ec)
09:04:59.401 (1) <=BE AuthenticationOk
09:04:59.412 (1) <=BE ParameterStatus(application_name = )
09:04:59.412 (1) <=BE ParameterStatus(client_encoding = UTF8)
09:04:59.412 (1) <=BE ParameterStatus(DateStyle = ISO, DMY)
09:04:59.412 (1) <=BE ParameterStatus(integer_datetimes = on)
09:04:59.412 (1) <=BE ParameterStatus(IntervalStyle = postgres)
09:04:59.412 (1) <=BE ParameterStatus(is_superuser = on)
09:04:59.412 (1) <=BE ParameterStatus(server_encoding = UTF8)
09:04:59.412 (1) <=BE ParameterStatus(server_version = 9.5.0)
09:04:59.412 (1) <=BE ParameterStatus(session_authorization = xxxx)
09:04:59.412 (1) <=BE ParameterStatus(standard_conforming_strings = on)
09:04:59.412 (1) <=BE ParameterStatus(TimeZone = Europe/Berlin)
09:04:59.412 (1) <=BE BackendKeyData(pid=10084,ckey=314920069)
09:04:59.412 (1) <=BE ReadyForQuery(I)
09:04:59.414 (1) simple execute, handler=org.postgresql.core.SetupQueryRunner$SimpleResultHandler@123a439b, maxRows=0,
fetchSize=0,flags=23
09:04:59.414 (1) FE=> Parse(stmt=null,query="SET extra_float_digits = 3",oids={})
09:04:59.414 (1) FE=> Bind(stmt=null,portal=null)
09:04:59.414 (1) FE=> Execute(portal=null,limit=1)
09:04:59.414 (1) FE=> Sync
09:04:59.424 (1) <=BE ParseComplete [null]
09:04:59.424 (1) <=BE BindComplete [null]
09:04:59.424 (1) <=BE CommandStatus(SET)
09:04:59.424 (1) <=BE ReadyForQuery(I)
09:04:59.425 (1) compatible = 90400
09:04:59.425 (1) loglevel = 2
09:04:59.425 (1) prepare threshold = 5
09:04:59.427 (1) types using binary send =
TIMESTAMPTZ,UUID,INT2_ARRAY,INT4_ARRAY,BYTEA,TEXT_ARRAY,TIMETZ,INT8,INT2,INT4,VARCHAR_ARRAY,INT8_ARRAY,POINT,TIMESTAMP,TIME,BOX,FLOAT4,FLOAT8,FLOAT4_ARRAY,FLOAT8_ARRAY
09:04:59.427 (1) types using binary receive =
TIMESTAMPTZ,UUID,INT2_ARRAY,INT4_ARRAY,BYTEA,TEXT_ARRAY,TIMETZ,INT8,INT2,INT4,VARCHAR_ARRAY,INT8_ARRAY,POINT,DATE,TIMESTAMP,TIME,BOX,FLOAT4,FLOAT8,FLOAT4_ARRAY,FLOAT8_ARRAY
09:04:59.427 (1) integer date/time = true
getConnection returning org.postgresql.Driver
09:04:59.442 (1) batch execute 10 queries,
handler=org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler@311d617d,maxRows=0, fetchSize=0, flags=5
09:04:59.442 (1) FE=> Parse(stmt=null,query="BEGIN",oids={})
09:04:59.442 (1) FE=> Bind(stmt=null,portal=null)
09:04:59.442 (1) FE=> Execute(portal=null,limit=0)
09:04:59.442 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('0')",oids={})
09:04:59.442 (1) FE=> Bind(stmt=null,portal=null)
09:04:59.442 (1) FE=> Describe(portal=null)
09:04:59.442 (1) FE=> Execute(portal=null,limit=1)
09:04:59.442 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('1')",oids={})
09:04:59.443 (1) FE=> Bind(stmt=null,portal=null)
09:04:59.443 (1) FE=> Describe(portal=null)
09:04:59.443 (1) FE=> Execute(portal=null,limit=1)
09:04:59.443 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('2')",oids={})
09:04:59.443 (1) FE=> Bind(stmt=null,portal=null)
09:04:59.443 (1) FE=> Describe(portal=null)
09:04:59.443 (1) FE=> Execute(portal=null,limit=1)
09:04:59.443 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('3')",oids={})
09:04:59.443 (1) FE=> Bind(stmt=null,portal=null)
09:04:59.443 (1) FE=> Describe(portal=null)
09:04:59.443 (1) FE=> Execute(portal=null,limit=1)
09:04:59.443 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('4')",oids={})
09:04:59.443 (1) FE=> Bind(stmt=null,portal=null)
09:04:59.443 (1) FE=> Describe(portal=null)
09:04:59.443 (1) FE=> Execute(portal=null,limit=1)
09:04:59.443 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('5')",oids={})
09:04:59.443 (1) FE=> Bind(stmt=null,portal=null)
09:04:59.443 (1) FE=> Describe(portal=null)
09:04:59.443 (1) FE=> Execute(portal=null,limit=1)
09:04:59.443 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('6')",oids={})
09:04:59.443 (1) FE=> Bind(stmt=null,portal=null)
09:04:59.443 (1) FE=> Describe(portal=null)
09:04:59.443 (1) FE=> Execute(portal=null,limit=1)
09:04:59.444 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('7')",oids={})
09:04:59.444 (1) FE=> Bind(stmt=null,portal=null)
09:04:59.444 (1) FE=> Describe(portal=null)
09:04:59.444 (1) FE=> Execute(portal=null,limit=1)
09:04:59.444 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('8')",oids={})
09:04:59.444 (1) FE=> Bind(stmt=null,portal=null)
09:04:59.444 (1) FE=> Describe(portal=null)
09:04:59.444 (1) FE=> Execute(portal=null,limit=1)
09:04:59.444 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('9')",oids={})
09:04:59.444 (1) FE=> Bind(stmt=null,portal=null)
09:04:59.444 (1) FE=> Describe(portal=null)
09:04:59.444 (1) FE=> Execute(portal=null,limit=1)
09:04:59.444 (1) FE=> Sync
09:04:59.479 (1) <=BE ParseComplete [null]
09:04:59.479 (1) <=BE BindComplete [null]
09:04:59.479 (1) <=BE CommandStatus(BEGIN)
09:04:59.479 (1) <=BE ParseComplete [null]
09:04:59.479 (1) <=BE BindComplete [null]
09:04:59.479 (1) <=BE NoData
09:04:59.479 (1) <=BE CommandStatus(INSERT 0 1)
09:04:59.479 (1) <=BE ParseComplete [null]
09:04:59.479 (1) <=BE BindComplete [null]
09:04:59.479 (1) <=BE NoData
09:04:59.479 (1) <=BE CommandStatus(INSERT 0 1)
09:04:59.479 (1) <=BE ParseComplete [null]
09:04:59.479 (1) <=BE BindComplete [null]
09:04:59.479 (1) <=BE NoData
09:04:59.479 (1) <=BE CommandStatus(INSERT 0 1)
09:04:59.479 (1) <=BE ParseComplete [null]
09:04:59.479 (1) <=BE BindComplete [null]
09:04:59.479 (1) <=BE NoData
09:04:59.479 (1) <=BE CommandStatus(INSERT 0 1)
09:04:59.479 (1) <=BE ParseComplete [null]
09:04:59.479 (1) <=BE BindComplete [null]
09:04:59.480 (1) <=BE NoData
09:04:59.480 (1) <=BE CommandStatus(INSERT 0 1)
09:04:59.480 (1) <=BE ParseComplete [null]
09:04:59.480 (1) <=BE BindComplete [null]
09:04:59.480 (1) <=BE NoData
09:04:59.487 (1) <=BE ErrorMessage(FEHLER: doppelter Schlüsselwert verletzt Unique-Constraint „testtable_pkey“
Detail: Schlüssel „(id)=(5)“ existiert bereits.)
org.postgresql.util.PSQLException: FEHLER: doppelter Schlüsselwert verletzt Unique-Constraint „testtable_pkey“
Detail: Schlüssel „(id)=(5)“ existiert bereits.
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:421)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2929)
at com.xxxx.generator.TestDataGenerator.generateRecipients(TestDataGenerator.java:31)
at com.xxxx.generator.TestReturnCodes.main(TestReturnCodes.java:66)
SQLException: SQLState(23505)
SQLState(23505) vendor code(0)
=========================================================================================================================================
Postgres 9.6.0 / postgresql-jdbc41-9.4-1201.jar
=========================================================================================================================================
DriverManager.getConnection("jdbc:postgresql://localhost:10433/xxxx5?charSet=UTF-8&user=xxxx&password=xxxx&logLevel=2&loglevel=2")
trying org.postgresql.Driver
09:06:57.267 (1) PostgreSQL 9.4 JDBC4.1 (build 1201)
09:06:57.272 (1) Trying to establish a protocol version 3 connection to localhost:10433
09:06:57.284 (1) Receive Buffer Size is 8192
09:06:57.284 (1) Send Buffer Size is 8192
09:06:57.285 (1) FE=> StartupPacket(user=xxxx, database=xxxx5, client_encoding=UTF8, DateStyle=ISO,
TimeZone=Europe/Berlin,extra_float_digits=2)
09:06:57.343 (1) <=BE AuthenticationReqMD5(salt=6ec50370)
09:06:57.352 (1) FE=> Password(md5digest=md5436a132216c8872a44464ed12bfef57f)
09:06:57.361 (1) <=BE AuthenticationOk
09:06:57.371 (1) <=BE ParameterStatus(application_name = )
09:06:57.371 (1) <=BE ParameterStatus(client_encoding = UTF8)
09:06:57.371 (1) <=BE ParameterStatus(DateStyle = ISO, DMY)
09:06:57.371 (1) <=BE ParameterStatus(integer_datetimes = on)
09:06:57.371 (1) <=BE ParameterStatus(IntervalStyle = postgres)
09:06:57.371 (1) <=BE ParameterStatus(is_superuser = on)
09:06:57.371 (1) <=BE ParameterStatus(server_encoding = UTF8)
09:06:57.371 (1) <=BE ParameterStatus(server_version = 9.6.0)
09:06:57.371 (1) <=BE ParameterStatus(session_authorization = xxxx)
09:06:57.372 (1) <=BE ParameterStatus(standard_conforming_strings = on)
09:06:57.372 (1) <=BE ParameterStatus(TimeZone = Europe/Berlin)
09:06:57.372 (1) <=BE BackendKeyData(pid=1420,ckey=46619645)
09:06:57.372 (1) <=BE ReadyForQuery(I)
09:06:57.374 (1) simple execute, handler=org.postgresql.core.SetupQueryRunner$SimpleResultHandler@123a439b, maxRows=0,
fetchSize=0,flags=23
09:06:57.375 (1) FE=> Parse(stmt=null,query="SET extra_float_digits = 3",oids={})
09:06:57.375 (1) FE=> Bind(stmt=null,portal=null)
09:06:57.375 (1) FE=> Execute(portal=null,limit=1)
09:06:57.375 (1) FE=> Sync
09:06:57.380 (1) <=BE ParseComplete [null]
09:06:57.380 (1) <=BE BindComplete [null]
09:06:57.380 (1) <=BE CommandStatus(SET)
09:06:57.380 (1) <=BE ReadyForQuery(I)
09:06:57.381 (1) compatible = 90400
09:06:57.381 (1) loglevel = 2
09:06:57.381 (1) prepare threshold = 5
09:06:57.386 (1) types using binary send =
TIMESTAMPTZ,UUID,INT2_ARRAY,INT4_ARRAY,BYTEA,TEXT_ARRAY,TIMETZ,INT8,INT2,INT4,VARCHAR_ARRAY,INT8_ARRAY,POINT,TIMESTAMP,TIME,BOX,FLOAT4,FLOAT8,FLOAT4_ARRAY,FLOAT8_ARRAY
09:06:57.387 (1) types using binary receive =
TIMESTAMPTZ,UUID,INT2_ARRAY,INT4_ARRAY,BYTEA,TEXT_ARRAY,TIMETZ,INT8,INT2,INT4,VARCHAR_ARRAY,INT8_ARRAY,POINT,DATE,TIMESTAMP,TIME,BOX,FLOAT4,FLOAT8,FLOAT4_ARRAY,FLOAT8_ARRAY
09:06:57.387 (1) integer date/time = true
getConnection returning org.postgresql.Driver
09:06:57.406 (1) batch execute 10 queries,
handler=org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler@311d617d,maxRows=0, fetchSize=0, flags=5
09:06:57.406 (1) FE=> Parse(stmt=null,query="BEGIN",oids={})
09:06:57.406 (1) FE=> Bind(stmt=null,portal=null)
09:06:57.406 (1) FE=> Execute(portal=null,limit=0)
09:06:57.407 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('0')",oids={})
09:06:57.407 (1) FE=> Bind(stmt=null,portal=null)
09:06:57.407 (1) FE=> Describe(portal=null)
09:06:57.407 (1) FE=> Execute(portal=null,limit=1)
09:06:57.407 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('1')",oids={})
09:06:57.407 (1) FE=> Bind(stmt=null,portal=null)
09:06:57.407 (1) FE=> Describe(portal=null)
09:06:57.407 (1) FE=> Execute(portal=null,limit=1)
09:06:57.407 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('2')",oids={})
09:06:57.407 (1) FE=> Bind(stmt=null,portal=null)
09:06:57.407 (1) FE=> Describe(portal=null)
09:06:57.407 (1) FE=> Execute(portal=null,limit=1)
09:06:57.407 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('3')",oids={})
09:06:57.407 (1) FE=> Bind(stmt=null,portal=null)
09:06:57.407 (1) FE=> Describe(portal=null)
09:06:57.407 (1) FE=> Execute(portal=null,limit=1)
09:06:57.407 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('4')",oids={})
09:06:57.407 (1) FE=> Bind(stmt=null,portal=null)
09:06:57.407 (1) FE=> Describe(portal=null)
09:06:57.407 (1) FE=> Execute(portal=null,limit=1)
09:06:57.407 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('5')",oids={})
09:06:57.407 (1) FE=> Bind(stmt=null,portal=null)
09:06:57.407 (1) FE=> Describe(portal=null)
09:06:57.408 (1) FE=> Execute(portal=null,limit=1)
09:06:57.408 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('6')",oids={})
09:06:57.408 (1) FE=> Bind(stmt=null,portal=null)
09:06:57.408 (1) FE=> Describe(portal=null)
09:06:57.408 (1) FE=> Execute(portal=null,limit=1)
09:06:57.408 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('7')",oids={})
09:06:57.408 (1) FE=> Bind(stmt=null,portal=null)
09:06:57.408 (1) FE=> Describe(portal=null)
09:06:57.408 (1) FE=> Execute(portal=null,limit=1)
09:06:57.408 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('8')",oids={})
09:06:57.408 (1) FE=> Bind(stmt=null,portal=null)
09:06:57.408 (1) FE=> Describe(portal=null)
09:06:57.408 (1) FE=> Execute(portal=null,limit=1)
09:06:57.408 (1) FE=> Parse(stmt=null,query="INSERT INTO TESTTABLE VALUES('9')",oids={})
09:06:57.408 (1) FE=> Bind(stmt=null,portal=null)
09:06:57.408 (1) FE=> Describe(portal=null)
09:06:57.408 (1) FE=> Execute(portal=null,limit=1)
09:06:57.408 (1) FE=> Sync
09:06:57.454 (1) <=BE ParseComplete [null]
09:06:57.454 (1) <=BE BindComplete [null]
09:06:57.454 (1) <=BE CommandStatus(BEGIN)
09:06:57.454 (1) <=BE ParseComplete [null]
09:06:57.454 (1) <=BE BindComplete [null]
09:06:57.454 (1) <=BE NoData
09:06:57.454 (1) <=BE CommandStatus(INSERT 0 1)
09:06:57.454 (1) <=BE ParseComplete [null]
09:06:57.454 (1) <=BE BindComplete [null]
09:06:57.454 (1) <=BE NoData
09:06:57.454 (1) <=BE CommandStatus(INSERT 0 1)
09:06:57.455 (1) <=BE ParseComplete [null]
09:06:57.455 (1) <=BE BindComplete [null]
09:06:57.455 (1) <=BE NoData
09:06:57.455 (1) <=BE CommandStatus(INSERT 0 1)
09:06:57.455 (1) <=BE ParseComplete [null]
09:06:57.455 (1) <=BE BindComplete [null]
09:06:57.455 (1) <=BE NoData
09:06:57.455 (1) <=BE CommandStatus(INSERT 0 1)
09:06:57.455 (1) <=BE ParseComplete [null]
09:06:57.455 (1) <=BE BindComplete [null]
09:06:57.455 (1) <=BE NoData
09:06:57.455 (1) <=BE CommandStatus(INSERT 0 1)
09:06:57.455 (1) <=BE ParseComplete [null]
09:06:57.455 (1) <=BE BindComplete [null]
09:06:57.455 (1) <=BE NoData
09:06:57.466 (1) <=BE ErrorMessage(FEHLER: doppelter Schlüsselwert verletzt Unique-Constraint »testtable_pkey«
Detail: Schlüssel »(id)=(5)« existiert bereits.)
org.postgresql.util.PSQLException: FEHLER: doppelter Schlüsselwert verletzt Unique-Constraint »testtable_pkey«
Detail: Schlüssel »(id)=(5)« existiert bereits.
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:421)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2929)
at com.xxxx.generator.TestDataGenerator.generateRecipients(TestDataGenerator.java:31)
at com.xxxx.generator.TestReturnCodes.main(TestReturnCodes.java:66)
SQLException: SQLState(23505)
SQLState(23505) vendor code(0)
Hi,
After debugging my problem with the updateCount in the 9.4.1211 JDBC Driver I found the line in the code which causes
theerror.
It's
org.postgresql.jdbc.BatchResultHandler, Line 138 , JDBC Driver postgresql-9.4.1211
@Override
public void handleError(SQLException newError) {
if (getException() == null) {
Arrays.fill(updateCounts, committedRows, updateCounts.length, Statement.EXECUTE_FAILED); //138 <-- BUG
//...
Before the line "Arrays.fill..." the updateCounts contain the information of the already successfully inserted records
[1,1,1,1,1,0,0,0,0,0].
After filling the array with Statement.EXECUTE_FAILED the array contains [-3,-3,-3,-3,-3,-3,-3,-3,-3,-3]
This is a not correct.
The correct result is [1,1,1,1,1] or [1,1,1,1,1,-3]
How to get this bug fixed? Can you rethink your implementation?
Thank you for your help,
Tillmann
Hi, thanks for the response. I have a simple test program which first creates a testtable, fills it with one record and does a batch insert of 10 records. The 5th insert causes a BatchUpdateException at stmt.executeBatch() when the data is NOT commited. The question is, which return codes should be in x.getUpdateCounts(). For better reading I placed the test programm on http://pastebin.com/0P1S6zEc Bye, Tillmann import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; public class TestUpdateCount { public static void main( String[] args ) throws SQLException, ClassNotFoundException, IOException { ////////////////////////////////////////////////////////////////////////////////////// // CHANGE THIS String dbUrl = "jdbc:postgresql://localhost:5432/db?charSet=UTF-8"; String dbUser = "user"; String dbPassword = "xxxx"; ////////////////////////////////////////////////////////////////////////////////////// try { Connection con = openConnection( dbUrl, dbUser, dbPassword ); con.setAutoCommit( false ); try { // Prepare Table Statement stmtDrop = con.createStatement(); stmtDrop.execute( "DROP TABLE IF EXISTS TESTTABLE " ); con.commit(); Statement stmtCreate = con.createStatement(); stmtCreate.execute( "CREATE TABLE TESTTABLE (id INTEGER PRIMARY KEY)" ); con.commit(); Statement stmtData = con.createStatement(); stmtData.execute( "INSERT INTO TESTTABLE VALUES (5)" ); con.commit(); // TestCase: Insert 10 statements via jdbc batch. // 5h statement should fail because of duplicate key error con = openConnection( dbUrl, dbUser, dbPassword ); con.setAutoCommit( false ); Statement stmt = con.createStatement(); for( int i = 0; i < 10; i++ ) { stmt.addBatch( "INSERT INTO TESTTABLE VALUES('" + i + "')" ); } stmt.executeBatch(); con.commit(); } catch( java.sql.BatchUpdateException x ) { final int[] updateCounts = x.getUpdateCounts(); for( int i = 0; i < updateCounts.length; i++ ) { System.err.println( "updateCounts[" + i + "]=" + updateCounts[i] ); } // First 5 statements are successfully, so update count should be 1 for( int i = 0; i < 5; i++ ) { if( updateCounts[i] != 1 ) System.err.println( "Wrong information returned by driver for update Count " + i ); } // 5th statement if( updateCounts[5] == -3 ) System.err.println( "Correct information returned by driver for update Count " + 5 ); } } catch( SQLException s ) { s.printStackTrace(); if( s.getNextException() != null ) { s.getNextException().printStackTrace(); } } catch( Exception s ) { s.printStackTrace(); } } private static Connection openConnection( String url, String user, String password ) throws ClassNotFoundException, SQLException, IOException { try { Class.forName( "org.postgresql.Driver" ); } catch( ClassNotFoundException e ) { System.err.println( "Could not load driver!" ); throw e; } Properties props = new Properties(); // Activate for logging // props.setProperty( "loglevel", "2" ); // FileWriter fw = new FileWriter( "C:\\temp\\test12345.txt" ); // DriverManager.setLogWriter( new PrintWriter( fw ) ); props.setProperty( "user", user ); props.setProperty( "password", password ); return DriverManager.getConnection( url, props ); } }