Re: [JDBC] ArrayIndexOutOfBoundsException from ByteConverter.int8when resolving generated keys

Hi Erko,

Do you have any idea how many records are inserted in a batch? If so is there any correlation ?

As to your last point. Thanks that code can be changed

Dave Cramer

davec@postgresintl.com

www.postgresintl.com

On 10 May 2017 at 03:39, Erko Hansar <erko.hansar@gmail.com> wrote:

Hei,

PROBLEM: 

After inserting some records into a table via a PreparedStatement with batch inserts, when trying to get generated key values from the generated keys result set, we sometimes get an ArrayIndexOutOfBoundsException from JDBC driver code when it's trying to convert bytes into long. This happens randomly, like 5% of times this method is used in production.

EXCEPTION:

Some times it's this:

java.lang.ArrayIndexOutOfBoundsException: 5

        at org.postgresql.util.ByteConverter.int8(ByteConverter.java:27) ~[postgresql-42.0.0.jar:42.0.0]

        at org.postgresql.jdbc.PgResultSet.getLong(PgResultSet.java:2101) ~[postgresql-42.0.0.jar:42.0.0]

        at org.postgresql.jdbc.PgResultSet.getLong(PgResultSet.java:2505) ~[postgresql-42.0.0.jar:42.0.0]

        ... our code ...

Other times it's just (without a stacktrace!?):

java.lang.ArrayIndexOutOfBoundsException: null

Before upgrading to 42.0.0, we used 9.4.1212 and then the line numbers where a little different:

java.lang.ArrayIndexOutOfBoundsException: 5

        at org.postgresql.util.ByteConverter.int8(ByteConverter.java:27) ~[postgresql-9.4.1212.jar:9.4.1212]

        at org.postgresql.jdbc.PgResultSet.getLong(PgResultSet.java:2060) ~[postgresql-9.4.1212.jar:9.4.1212]

        at org.postgresql.jdbc.PgResultSet.getLong(PgResultSet.java:2486) ~[postgresql-9.4.1212.jar:9.4.1212]

        ... our code ...

CODE:

public void insert(List<Record> records) {

    String sql = "INSERT INTO portal.record (company_id, status, type, oop_expense, created_by) VALUES (?, ?, ?, ?, ?)";

    Connection connection = DataSourceUtils.getConnection(getJdbcTemplate().getDataSource());

    try (PreparedStatement ps = connection.prepareStatement(sql, new String[] {"id"})) {

        for (Record record : records) {

            ps.setLong(1, record.getCompanyId());

            ps.setString(2, record.getStatus());

            ps.setString(3, record.getType());

            ps.setBoolean(4, record.getOopExpense());

            ps.setString(5, "user123");

            ps.addBatch();

        }

        int[] ints = ps.executeBatch();

        ResultSet generatedKeys = ps.getGeneratedKeys();

        for (int i = 0; i < ints.length; i++) {

            if (ints[i] == 1) {

                if (!generatedKeys.next()) {

                    throw new RuntimeException("Returned keys count from INSERT does not match record count!");

                }

                Record record = records.get(i);

                record.setId(generatedKeys.getLong("id"));   // THIS IS WHERE THE EXCEPTION IS THROWN SOMETIMES

            }

        }

        generatedKeys.close();

    } catch (SQLException e) {

        throw new RuntimeException("Failed to insert records!", e);

    } finally {

        DataSourceUtils.releaseConnection(connection, getJdbcTemplate().getDataSource());

    }

}

CREATE TABLE portal.record

(

  id bigserial NOT NULL,

  company_id bigint NOT NULL,

  status character varying(100) NOT NULL,

  type character varying(100),

  created_by character varying(100) NOT NULL,

  created_date timestamp without time zone NOT NULL DEFAULT now(),

  updated_by character varying(100),

  updated_date timestamp without time zone,

  oop_expense boolean,

  CONSTRAINT pk_record_id PRIMARY KEY (id),

  CONSTRAINT fk_record_company FOREIGN KEY (company_id)

      REFERENCES portal.company (id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE NO ACTION

)

WITH (

  OIDS=FALSE

);

Id field sequence is currently at 21540.

BACKGROUND:

Production system

Java 8, Spring Framework, JDBC 42.0.0

PostgreSQL server 9.6.1

We are using similar batch inserts in a few other methods but haven't received exceptions from those.

OTHER:

In the https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/jdbc/PgResultSet.java the IF block with line numbers 2101-2103:

if (oid == Oid.INT8) {

    return ByteConverter.int8(this_row[col], 0);

}

seems unnecessary, because the readLongValue on the next line would do the same call anyways?

Best regards,

Erko