Re: [JDBC] ArrayIndexOutOfBoundsException from ByteConverter.int8when resolving generated keys - Mailing list pgsql-jdbc

Thank you for getting back so quickly,

1) I know that this problem has occurred multiple times when inserting only 1 record. The end users *think* that they have seen it when handling multiple records too. Unfortunately I can't be sure because the transactions are rolled back and we don't log the "potentially inserted data". I will improve logging to track the number of rows in the batch and when the exception happens next time, I can give more information.

2) There was a similar problem discussed in 2012, which ended with an explanation: https://www.postgresql.org/message-id/alpine.BSO.2.00.1211081338240.29600%40leary.csoft.net

Was this ever fixed?

Br,

Erko

On 10 May 2017 at 13:56, Dave Cramer <pg@fastcrypt.com> wrote:

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