Re: [JDBC] ArrayIndexOutOfBoundsException from ByteConverter.int8when resolving generated keys

Поиск
Список
Период
Сортировка
От Erko Hansar
Тема Re: [JDBC] ArrayIndexOutOfBoundsException from ByteConverter.int8when resolving generated keys
Дата
Msg-id CACf-fcLNTL3wyUs0L+7oMmfk12JoQoPG+XoS-xT4D3mu1cTKqw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [JDBC] ArrayIndexOutOfBoundsException from ByteConverter.int8when resolving generated keys  (Dave Cramer <pg@fastcrypt.com>)
Список pgsql-jdbc
Дерево обсуждения
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@fastcrypt.com> wrote:

On 10 May 2017 at 16:35, Vladimir Sitnikov <sitnikov.vladimir@gmail.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 





В списке pgsql-jdbc по дате отправления:

Предыдущее
От: Dave Cramer
Дата:
Сообщение: Re: [JDBC] ArrayIndexOutOfBoundsException from ByteConverter.int8when resolving generated keys
Следующее
От: Thomas Kellerer
Дата:
Сообщение: [JDBC] Postgres 10 partitioned tables