Re: ArrayIndexOutOfBoundsException from ByteConverter.int8 when 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(at)postgresintl(dot)com
www.postgresintl.com

On 10 May 2017 at 03:39, Erko Hansar <erko(dot)hansar(at)gmail(dot)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
>