Re: ArrayIndexOutOfBoundsException from ByteConverter.int8 when resolving generated keys

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(at)fastcrypt(dot)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(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
>>
>
>