Re: Return Codes of BatchUpdateException in PostgreSql 9.6

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