Re: ArrayIndexOutOfBoundsException from ByteConverter.int8 when resolving generated keys

Erko,

On 10 May 2017 at 07:12, Erko Hansar <erko(dot)hansar(at)gmail(dot)com> wrote:

> 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.
>
>
Thanks, that would be useful.

> 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?
>
>
I expect so, but have to do some digging

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

> 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
>>>
>>
>>
>