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
On Thu, 2016-10-13 at 08:25 +0000, Tillmann Schulz wrote: > 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 > > > According to the doco, getUpdateCounts():- Returns: an array of int containing the update counts for the updates that were executed successfully before this error occurred. Or, if the driver continues to process commands after an error, one of the following for every command in the batch: 1. an update count 2. Statement.SUCCESS_NO_INFO to indicate that the command executed successfully but the number of rows affected is unknown 3. Statement.EXECUTE_FAILED to indicate that the command failed to execute successfully I do not know if the Postgres "driver continues to process commands after an error" but if it does then the array should contain 10 entries and one would have the "Statement.EXECUTE_FAILED". Tomorrow, I'll dig a bit deeper if nobody else can explain this. HTH, Rob
The docu says: If the driver continues processing after a failure (and this is the case in my example), BatchUpdateException.getUpdateCountswill have an element for every command. In my case there is an element for every command but all elements contain -3 (Statement.Execute_Failed) in postgres 9.6. I think only the failed statmements should been marked with -3! This behaviour is new in 9.6 and seems to be a bug. Thanks Tillmann >According to the doco, getUpdateCounts():- > >Returns: > an array of int containing the update counts for the updates that >were executed successfully before this error occurred. Or, if the >driver continues to process commands after an error, one of the >following for every command in the batch: > > 1. an update count > 2. Statement.SUCCESS_NO_INFO to indicate that the command executed >successfully but the number of rows affected is unknown > 3. Statement.EXECUTE_FAILED to indicate that the command failed to >execute successfully > > >I do not know if the Postgres "driver continues to process commands >after an error" but if it does then the array should contain 10 entries >and one would have the "Statement.EXECUTE_FAILED". > >Tomorrow, I'll dig a bit deeper if nobody else can explain this. > >HTH, >Rob
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 On 13/10/16 14:24, Tillmann Schulz wrote: > The docu says: > If the driver continues processing after a failure (and this is the case in my example), BatchUpdateException.getUpdateCountswill have an element for every command. > > In my case there is an element for every command but all elements contain -3 (Statement.Execute_Failed) in postgres 9.6. > > I think only the failed statmements should been marked with -3! This behaviour is new in 9.6 and seems to be a bug. > > > Thanks > > Tillmann > > >> According to the doco, getUpdateCounts():- >> >> Returns: >> an array of int containing the update counts for the updates that >> were executed successfully before this error occurred. Or, if the >> driver continues to process commands after an error, one of the >> following for every command in the batch: >> >> 1. an update count >> 2. Statement.SUCCESS_NO_INFO to indicate that the command executed >> successfully but the number of rows affected is unknown >> 3. Statement.EXECUTE_FAILED to indicate that the command failed to >> execute successfully >> >> >> I do not know if the Postgres "driver continues to process commands >> after an error" but if it does then the array should contain 10 entries >> and one would have the "Statement.EXECUTE_FAILED". >> >> Tomorrow, I'll dig a bit deeper if nobody else can explain this. >> >> HTH, >> Rob >
On Thu, 2016-10-13 at 08:25 +0000, Tillmann Schulz wrote: > 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 > > > According to the doco, getUpdateCounts():- Returns: an array of int containing the update counts for the updates that were executed successfully before this error occurred. Or, if the driver continues to process commands after an error, one of the following for every command in the batch: 1. an update count 2. Statement.SUCCESS_NO_INFO to indicate that the command executed successfully but the number of rows affected is unknown 3. Statement.EXECUTE_FAILED to indicate that the command failed to execute successfully I do not know if the Postgres "driver continues to process commands after an error" but if it does then the array should contain 10 entries and one would have the "Statement.EXECUTE_FAILED". Tomorrow, I'll dig a bit deeper if nobody else can explain this. HTH, Rob
The docu says: If the driver continues processing after a failure (and this is the case in my example), BatchUpdateException.getUpdateCountswill have an element for every command. In my case there is an element for every command but all elements contain -3 (Statement.Execute_Failed) in postgres 9.6. I think only the failed statmements should been marked with -3! This behaviour is new in 9.6 and seems to be a bug. Thanks Tillmann >According to the doco, getUpdateCounts():- > >Returns: > an array of int containing the update counts for the updates that >were executed successfully before this error occurred. Or, if the >driver continues to process commands after an error, one of the >following for every command in the batch: > > 1. an update count > 2. Statement.SUCCESS_NO_INFO to indicate that the command executed >successfully but the number of rows affected is unknown > 3. Statement.EXECUTE_FAILED to indicate that the command failed to >execute successfully > > >I do not know if the Postgres "driver continues to process commands >after an error" but if it does then the array should contain 10 entries >and one would have the "Statement.EXECUTE_FAILED". > >Tomorrow, I'll dig a bit deeper if nobody else can explain this. > >HTH, >Rob
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 On 13/10/16 14:24, Tillmann Schulz wrote: > The docu says: > If the driver continues processing after a failure (and this is the case in my example), BatchUpdateException.getUpdateCountswill have an element for every command. > > In my case there is an element for every command but all elements contain -3 (Statement.Execute_Failed) in postgres 9.6. > > I think only the failed statmements should been marked with -3! This behaviour is new in 9.6 and seems to be a bug. > > > Thanks > > Tillmann > > >> According to the doco, getUpdateCounts():- >> >> Returns: >> an array of int containing the update counts for the updates that >> were executed successfully before this error occurred. Or, if the >> driver continues to process commands after an error, one of the >> following for every command in the batch: >> >> 1. an update count >> 2. Statement.SUCCESS_NO_INFO to indicate that the command executed >> successfully but the number of rows affected is unknown >> 3. Statement.EXECUTE_FAILED to indicate that the command failed to >> execute successfully >> >> >> I do not know if the Postgres "driver continues to process commands >> after an error" but if it does then the array should contain 10 entries >> and one would have the "Statement.EXECUTE_FAILED". >> >> Tomorrow, I'll dig a bit deeper if nobody else can explain this. >> >> HTH, >> Rob >
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
Hi, Comments in-line.... On 14/10/16 09:58, Tillmann Schulz wrote: > 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 The value 1 is the batched statement return code for j.s.Statement.SUCCESS_NO_INFO > > > 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 > See above. > > 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? logLevel is a connection property. 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. Regards, Jeremy > > Bye, > > Tillmann > >
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.
Hi, Comments in-line.... On 14/10/16 09:58, Tillmann Schulz wrote: > 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 The value 1 is the batched statement return code for j.s.Statement.SUCCESS_NO_INFO > > > 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 > See above. > > 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? logLevel is a connection property. 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. Regards, Jeremy > > Bye, > > Tillmann > >
Hi, The first and second test inserts 5 rather than the 10 rows in the third. An apples and pairs comparison. What happens when you insert 10 statements in a batch using Postgres 9.5.0/9.6.0 with postgresql-jdbc41-9.4-1201.jar ? I want to see what happens when the unique constraint error condition and the resulting counts. As is demonstrated in the log. Jeremy On 14/10/16 13:15, Tillmann Schulz wrote: > 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) > >
Hi, The first and second test inserts 5 rather than the 10 rows in the third. An apples and pairs comparison. What happens when you insert 10 statements in a batch using Postgres 9.5.0/9.6.0 with postgresql-jdbc41-9.4-1201.jar ? I want to see what happens when the unique constraint error condition and the resulting counts. As is demonstrated in the log. Jeremy On 14/10/16 13:15, Tillmann Schulz wrote: > 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) > >
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 all,
I recently updated my version of the JDBC driver in production.
is it possible to release an hotfix ?
thank you very much
Enrico Olivelli
Il giorno mar, 18/10/2016 alle 13.14 +0000, Tillmann Schulz ha scritto:
Hi, After debugging my problem with the updateCount in the 9.4.1211 JDBC Driver I found the line in the code which causes the error. It's org.postgresql.jdbc.BatchResultHandler, Line 138 , JDBC Driver postgresql-9.4.1211 @Overridepublic 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
--Enrico Olivelli Software Development Manager @Diennea Tel.: (+39) 0546 066100 - Int. 925 Viale G.Marconi 30/14 - 48018 Faenza (RA) MagNews - E-mail Marketing Solutions http://www.magnews.it Diennea - Digital Marketing Solutions http://www.diennea.com
Iscriviti alla nostra newsletter per rimanere aggiornato su digital ed email marketing! http://www.magnews.it/newsletter/
The information in this email is confidential and may be legally privileged. If you are not the intended recipient please notify the sender immediately and destroy this email. Any unauthorized, direct or indirect, disclosure, copying, storage, distribution or other use is strictly forbidden.
Hi,
Can you provide a test case that reproduces the problem?
Vladimir
Vladimir
ср, 19 окт. 2016 г. в 12:24, Enrico Olivelli - Diennea <enrico.olivelli@diennea.com>:
Hi all,I recently updated my version of the JDBC driver in production.is it possible to release an hotfix ?thank you very muchEnrico OlivelliIl giorno mar, 18/10/2016 alle 13.14 +0000, Tillmann Schulz ha scritto:Hi, After debugging my problem with the updateCount in the 9.4.1211 JDBC Driver I found the line in the code which causes the error. It's org.postgresql.jdbc.BatchResultHandler, Line 138 , JDBC Driver postgresql-9.4.1211 @Overridepublic 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--Enrico Olivelli Software Development Manager @Diennea Tel.: (+39) 0546 066100 - Int. 925 Viale G.Marconi 30/14 - 48018 Faenza (RA) MagNews - E-mail Marketing Solutions http://www.magnews.it Diennea - Digital Marketing Solutions http://www.diennea.com
Iscriviti alla nostra newsletter per rimanere aggiornato su digital ed email marketing! http://www.magnews.it/newsletter/
The information in this email is confidential and may be legally privileged. If you are not the intended recipient please notify the sender immediately and destroy this email. Any unauthorized, direct or indirect, disclosure, copying, storage, distribution or other use is strictly forbidden.
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 ); } }
Hi all,
I recently updated my version of the JDBC driver in production.
is it possible to release an hotfix ?
thank you very much
Enrico Olivelli
Il giorno mar, 18/10/2016 alle 13.14 +0000, Tillmann Schulz ha scritto:
Hi, After debugging my problem with the updateCount in the 9.4.1211 JDBC Driver I found the line in the code which causes the error. It's org.postgresql.jdbc.BatchResultHandler, Line 138 , JDBC Driver postgresql-9.4.1211 @Overridepublic 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
--Enrico Olivelli Software Development Manager @Diennea Tel.: (+39) 0546 066100 - Int. 925 Viale G.Marconi 30/14 - 48018 Faenza (RA) MagNews - E-mail Marketing Solutions http://www.magnews.it Diennea - Digital Marketing Solutions http://www.diennea.com
Iscriviti alla nostra newsletter per rimanere aggiornato su digital ed email marketing! http://www.magnews.it/newsletter/
The information in this email is confidential and may be legally privileged. If you are not the intended recipient please notify the sender immediately and destroy this email. Any unauthorized, direct or indirect, disclosure, copying, storage, distribution or other use is strictly forbidden.
Hi,
Can you provide a test case that reproduces the problem?
Vladimir
Vladimir
ср, 19 окт. 2016 г. в 12:24, Enrico Olivelli - Diennea <enrico.olivelli@diennea.com>:
Hi all,I recently updated my version of the JDBC driver in production.is it possible to release an hotfix ?thank you very muchEnrico OlivelliIl giorno mar, 18/10/2016 alle 13.14 +0000, Tillmann Schulz ha scritto:Hi, After debugging my problem with the updateCount in the 9.4.1211 JDBC Driver I found the line in the code which causes the error. It's org.postgresql.jdbc.BatchResultHandler, Line 138 , JDBC Driver postgresql-9.4.1211 @Overridepublic 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--Enrico Olivelli Software Development Manager @Diennea Tel.: (+39) 0546 066100 - Int. 925 Viale G.Marconi 30/14 - 48018 Faenza (RA) MagNews - E-mail Marketing Solutions http://www.magnews.it Diennea - Digital Marketing Solutions http://www.diennea.com
Iscriviti alla nostra newsletter per rimanere aggiornato su digital ed email marketing! http://www.magnews.it/newsletter/
The information in this email is confidential and may be legally privileged. If you are not the intended recipient please notify the sender immediately and destroy this email. Any unauthorized, direct or indirect, disclosure, copying, storage, distribution or other use is strictly forbidden.
Hi Tillmann, Thank you for providing the test case. I suggest you take a look at this class [1] in the testsuite. It may answer your question on what to expect in updateCount. If you question is not answered and the tests in that class do not exercise what is provided in your test case then I suggest the following: a) Create an issue [2] on Github. b) Fork the pgjdbc project. c) Add your contributed test into [1] as a new test method. Even better is to include a fix along with the test case. d) Create a Pull Request (PR) to have your test merged into master. Also update your created issue with details of the PR. So the two are linked. This way the project can ensure this problem you are seeing does not reoccur again in the future. If it is indeed a defect. Regards, Jeremy [1] https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/test/java/org/postgresql/test/jdbc2/BatchExecuteTest.java [2] https://github.com/pgjdbc/pgjdbc/issues On 19/10/16 12:49, Tillmann Schulz wrote: > 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 ); > > } > > } > > -- Jeremy Whiting Senior Software Engineer, Middleware Performance Team JBoss, by Red Hat ------------------------------------------------------------ Registered Address: RED HAT UK LIMITED, Peninsular House, 30-36 Monument Street, 4th floor, London. EC3R 8NB United Kingdom Registered in UK and Wales under Company Registration No. 3798903 Directors: Michael Cunningham (US), Michael ("Mike") O'Neill(Ireland) and Eric Shander (US)
Hi, thanks for your response. Meanwhile I found the issue, that causes my problem: https://github.com/pgjdbc/pgjdbc/issues/502 So the question is "if the driver should mark all the rows in int[] executeBatch() with Statement.EXECUTE_FAILED" when asingle statement fails. Other drivers like oracle-jdbc work the same way like postgres does before issue #502 was released. The fix would be to remove the code of issue #502 from the driver. As next step for me I will create an issue on github. Bye Tillmann ----- Ursprüngliche Message ----- Von: Jeremy Whiting <jwhiting@redhat.com> An: Tillmann Schulz <tillmann73@yahoo.de>; "pgsql-jdbc@postgresql.org" <pgsql-jdbc@postgresql.org> Gesendet: 11:18 Donnerstag, 20.Oktober 2016 Betreff: Re: [JDBC] Return Codes of BatchUpdateException in PostgreSql 9.6 Hi Tillmann, Thank you for providing the test case. I suggest you take a look at this class [1] in the testsuite. It may answer your question on what to expect in updateCount. If you question is not answered and the tests in that class do not exercise what is provided in your test case then I suggest the following: a) Create an issue [2] on Github. b) Fork the pgjdbc project. c) Add your contributed test into [1] as a new test method. Even better is to include a fix along with the test case. d) Create a Pull Request (PR) to have your test merged into master. Also update your created issue with details of the PR. So the two are linked. This way the project can ensure this problem you are seeing does not reoccur again in the future. If it is indeed a defect. Regards, Jeremy [1] https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/test/java/org/postgresql/test/jdbc2/BatchExecuteTest.java [2] https://github.com/pgjdbc/pgjdbc/issues On 19/10/16 12:49, Tillmann Schulz wrote: > 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 ); > > } > > } > > -- Jeremy Whiting Senior Software Engineer, Middleware Performance Team JBoss, by Red Hat ------------------------------------------------------------ Registered Address: RED HAT UK LIMITED, Peninsular House, 30-36 Monument Street, 4th floor, London. EC3R 8NB United Kingdom Registered in UK and Wales under Company Registration No. 3798903 Directors: Michael Cunningham (US), Michael ("Mike") O'Neill(Ireland) and Eric Shander (US)
Jeremy Whiting <jwhiting@redhat.com>:
Hi Tillmann,
Thank you for providing the test case.
Are you exchanging testcases in private?
Just in case, there's a relevant test case that explores different aspects of batch execution vs failures: https://github.com/pgjdbc/pgjdbc/blob/56c04d0ec95ede31b5f13a70ab76f2b4df09aef5/pgjdbc/src/test/java/org/postgresql/test/jdbc2/BatchFailureTest.java
Vladimir
Hi Vladimir,
Nope. Tillmann pasted his test case at the end of his email [1] and provided a link to a copy on pastebin.
Jeremy
[1] https://www.postgresql.org/message-id/65622026.6539291.1476877760626%40mail.yahoo.com
On 20/10/16 11:01, Vladimir Sitnikov wrote:
Nope. Tillmann pasted his test case at the end of his email [1] and provided a link to a copy on pastebin.
Jeremy
[1] https://www.postgresql.org/message-id/65622026.6539291.1476877760626%40mail.yahoo.com
On 20/10/16 11:01, Vladimir Sitnikov wrote:
Jeremy Whiting <jwhiting@redhat.com>:Hi Tillmann,
Thank you for providing the test case.Are you exchanging testcases in private?Just in case, there's a relevant test case that explores different aspects of batch execution vs failures: https://github.com/pgjdbc/pgjdbc/blob/56c04d0ec95ede31b5f13a70ab76f2b4df09aef5/pgjdbc/src/test/java/org/postgresql/test/jdbc2/BatchFailureTest.javaVladimir
Hi Tillmann, Thank you for providing the test case. I suggest you take a look at this class [1] in the testsuite. It may answer your question on what to expect in updateCount. If you question is not answered and the tests in that class do not exercise what is provided in your test case then I suggest the following: a) Create an issue [2] on Github. b) Fork the pgjdbc project. c) Add your contributed test into [1] as a new test method. Even better is to include a fix along with the test case. d) Create a Pull Request (PR) to have your test merged into master. Also update your created issue with details of the PR. So the two are linked. This way the project can ensure this problem you are seeing does not reoccur again in the future. If it is indeed a defect. Regards, Jeremy [1] https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/test/java/org/postgresql/test/jdbc2/BatchExecuteTest.java [2] https://github.com/pgjdbc/pgjdbc/issues On 19/10/16 12:49, Tillmann Schulz wrote: > 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 ); > > } > > } > > -- Jeremy Whiting Senior Software Engineer, Middleware Performance Team JBoss, by Red Hat ------------------------------------------------------------ Registered Address: RED HAT UK LIMITED, Peninsular House, 30-36 Monument Street, 4th floor, London. EC3R 8NB United Kingdom Registered in UK and Wales under Company Registration No. 3798903 Directors: Michael Cunningham (US), Michael ("Mike") O'Neill(Ireland) and Eric Shander (US)
Hi,
I added a new issue on github:
So this is a better place for discussions.
Hope that helps.
Thanks,
Tillmann
Hi, thanks for your response. Meanwhile I found the issue, that causes my problem: https://github.com/pgjdbc/pgjdbc/issues/502 So the question is "if the driver should mark all the rows in int[] executeBatch() with Statement.EXECUTE_FAILED" when asingle statement fails. Other drivers like oracle-jdbc work the same way like postgres does before issue #502 was released. The fix would be to remove the code of issue #502 from the driver. As next step for me I will create an issue on github. Bye Tillmann ----- Ursprüngliche Message ----- Von: Jeremy Whiting <jwhiting@redhat.com> An: Tillmann Schulz <tillmann73@yahoo.de>; "pgsql-jdbc@postgresql.org" <pgsql-jdbc@postgresql.org> Gesendet: 11:18 Donnerstag, 20.Oktober 2016 Betreff: Re: [JDBC] Return Codes of BatchUpdateException in PostgreSql 9.6 Hi Tillmann, Thank you for providing the test case. I suggest you take a look at this class [1] in the testsuite. It may answer your question on what to expect in updateCount. If you question is not answered and the tests in that class do not exercise what is provided in your test case then I suggest the following: a) Create an issue [2] on Github. b) Fork the pgjdbc project. c) Add your contributed test into [1] as a new test method. Even better is to include a fix along with the test case. d) Create a Pull Request (PR) to have your test merged into master. Also update your created issue with details of the PR. So the two are linked. This way the project can ensure this problem you are seeing does not reoccur again in the future. If it is indeed a defect. Regards, Jeremy [1] https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/test/java/org/postgresql/test/jdbc2/BatchExecuteTest.java [2] https://github.com/pgjdbc/pgjdbc/issues On 19/10/16 12:49, Tillmann Schulz wrote: > 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 ); > > } > > } > > -- Jeremy Whiting Senior Software Engineer, Middleware Performance Team JBoss, by Red Hat ------------------------------------------------------------ Registered Address: RED HAT UK LIMITED, Peninsular House, 30-36 Monument Street, 4th floor, London. EC3R 8NB United Kingdom Registered in UK and Wales under Company Registration No. 3798903 Directors: Michael Cunningham (US), Michael ("Mike") O'Neill(Ireland) and Eric Shander (US)
Jeremy Whiting <jwhiting@redhat.com>:
Hi Tillmann,
Thank you for providing the test case.
Are you exchanging testcases in private?
Just in case, there's a relevant test case that explores different aspects of batch execution vs failures: https://github.com/pgjdbc/pgjdbc/blob/56c04d0ec95ede31b5f13a70ab76f2b4df09aef5/pgjdbc/src/test/java/org/postgresql/test/jdbc2/BatchFailureTest.java
Vladimir
Hi Vladimir,
Nope. Tillmann pasted his test case at the end of his email [1] and provided a link to a copy on pastebin.
Jeremy
[1] https://www.postgresql.org/message-id/65622026.6539291.1476877760626%40mail.yahoo.com
On 20/10/16 11:01, Vladimir Sitnikov wrote:
Nope. Tillmann pasted his test case at the end of his email [1] and provided a link to a copy on pastebin.
Jeremy
[1] https://www.postgresql.org/message-id/65622026.6539291.1476877760626%40mail.yahoo.com
On 20/10/16 11:01, Vladimir Sitnikov wrote:
Jeremy Whiting <jwhiting@redhat.com>:Hi Tillmann,
Thank you for providing the test case.Are you exchanging testcases in private?Just in case, there's a relevant test case that explores different aspects of batch execution vs failures: https://github.com/pgjdbc/pgjdbc/blob/56c04d0ec95ede31b5f13a70ab76f2b4df09aef5/pgjdbc/src/test/java/org/postgresql/test/jdbc2/BatchFailureTest.javaVladimir
Hi,
I added a new issue on github:
So this is a better place for discussions.
Hope that helps.
Thanks,
Tillmann