Re: ArrayIndexOutOfBoundsException from ByteConverter.int8 when resolving generated keys

Today's stats:
08:30:36 Inserting 1 records >> EXCEPTION
08:30:47 Inserting 1 records >> EXCEPTION
09:13:59 Inserting 7 records >> OK
09:14:17 Inserting 18 records >> OK
09:14:27 Inserting 2 records >> OK
09:14:52 Inserting 4 records >> OK
09:15:02 Inserting 4 records >> OK
09:15:13 Inserting 2 records >> OK
09:15:38 Inserting 8 records >> OK
09:26:05 Inserting 1 records >> EXCEPTION
09:42:18 Inserting 5 records >> OK
09:42:28 Inserting 4 records >> OK
10:43:08 Inserting 2 records >> OK
10:43:56 Inserting 13 records >> OK
15:30:14 Inserting 1 records >> OK
15:31:27 Inserting 1 records >> OK
15:31:42 Inserting 1 records >> OK
15:31:44 Inserting 1 records >> OK
15:32:07 Inserting 2 records >> OK

So there is no clear pattern.

WA3) rewrite my code and stop using batch inserts and do single inserts
because it doesn't matter if the code takes 30ms instead of 15ms to run
WA4) before batch insert, select X keys from sequence and then insert with
those specific keys and stop using the generated keys in the batch methods

Anyways is this a hard thing to fix on the jdbc side?

BR,
Erko

On 11 May 2017 at 15:36, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:

>
> On 10 May 2017 at 16:35, Vladimir Sitnikov <sitnikov(dot)vladimir(at)gmail(dot)com>
> wrote:
>
>> Here's the case:
>> 1) Execute "insert" via non-batch multiple times. This causes pgjdbc to
>> server-prepare the insert and use binary transfer for the generated keys.
>> Note: so far so good, it is perfectly fine.
>> 2) Execute the same SQL via batch API. Here things break. Batch-insert
>> wants generated keys in text format (there's QUERY_NO_BINARY_TRANSFER
>> flag exactly for that case), and it does ask backend to send the data in
>> text format. However, query metadata at pgjdbc side specify that "output
>> columns are in binary format", thus generated keys resultset tries to
>> access columns as binary and fails.
>>
>
> I would have thought this would have been solved by now :(
>
>>
>> So possible workarounds are:
>> WA1) Add special marker (that is a comment) to batch SQL. That will make
>> sure batch-insert uses its own statement.
>>
> This is ugly
>
>> WA2) mark INT8 as text-only. It is something
>> like binaryTransferDisable=INT8 connection property
>>
> This is probably preferable
>
>
>
> Dave Cramer
>
> davec(at)postgresintl(dot)com
> www.postgresintl.com
>
>>
>>>