Re: Batch INSERT failing with error 22P02

Kris Jurka wrote:
>
>
> On Mon, 17 Sep 2007, Eric Faulhaber wrote:
>
>> I recently upgraded from PG 8.1.8 to 8.2.4.  I made a corresponding
>> move in JDBC driver versions from postgresql-8.1-408.jdbc3.jar to
>> postgresql-8.2-506.jdbc4.jar (binary distributions in both cases).
>> During a lengthy import using Hibernate (yes, I realize it's not the
>> most efficient mechanism for bulk import), I hit the following error:
>>
>> SEVERE: ERROR: invalid input syntax for type oid: ""
>>
>> Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into
>> turnaround (turn_number, site, item, purchase_order_number,
>> purchase_order_line, lot, serial_number, quantity_issued,
>> expiration_date, id) values (0000XBJG, GSO, 0A0001H9L, 427190, 15, ,
>> , -14000.00000, NULL, 59657893) was aborted.  Call getNextException
>> to see the cause.
>> SEVERE: An SQLException was provoked by the following failure:
>> java.lang.IllegalArgumentException: Can't change resolved type for
>> param: 7 from 1043 to 26
>> ....
>
> We've seen this report once before, but weren't able to get to the
> bottom of it because it also occurred on a large dataset.
>
> http://archives.postgresql.org/pgsql-jdbc/2007-06/threads.php#00041

Yes, before posting I googled the error message and found part of this
discussion, as well as the resulting change to the error message in
SimpleParameterList.java.

>
> In his case int4 and int8 were getting mixed up which is a little more
> understandable, in your case it's confusing varchar and oid which
> seems less likely.

Any idea what component is mixing up these types?  As I understand it
from setting a breakpoint at the point the exception is thrown and
tracking back through the stack, the OID type (26) is reported by the
back-end and is compared with the type the driver expects for the given
parameter.  But then I wonder why we would not see this error with the
same back-end and the older JDBC driver...

>   Is there any chance you can run this with the URL parameter
> loglevel=2, which will produce tons of logging information, or can you
> make this test case available to me?

OK, I generated the debug log file (>1.4GB).  How do I get this to you?
Even the relevant portions alone are probably too long to inline in a
message to the list.

>
> Kris Jurka
>
>

Thanks,
Eric Faulhaber