Skip site navigation (1) Skip section navigation (2)

Re: Batch INSERT failing with error 22P02

From: Eric Faulhaber <ecf(at)goldencode(dot)com>
To: Kris Jurka <books(at)ejurka(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Batch INSERT failing with error 22P02
Date: 2007-09-18 15:44:55
Message-ID: 46EFF277.1090309@goldencode.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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


In response to

Responses

pgsql-jdbc by date

Next:From: Eric FaulhaberDate: 2007-09-18 16:38:52
Subject: Re: Batch INSERT failing with error 22P02
Previous:From: Berkes AlexanderDate: 2007-09-18 12:33:55
Subject: AbstractJdbc2Connection.java getObject(String type, String value)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group