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

Batch INSERT failing with error 22P02

From: Eric Faulhaber <ecf(at)goldencode(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Batch INSERT failing with error 22P02
Date: 2007-09-17 20:34:55
Message-ID: 46EEE4EF.80608@goldencode.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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:

Sep 4, 2007 3:39:10 AM org.hibernate.util.JDBCExceptionReporter 
logExceptions
WARNING: SQL Error: 0, SQLState: null
Sep 4, 2007 3:39:10 AM org.hibernate.util.JDBCExceptionReporter 
logExceptions
SEVERE: 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.
Sep 4, 2007 3:39:10 AM org.hibernate.util.JDBCExceptionReporter 
logExceptions
WARNING: SQL Error: 0, SQLState: 22P02
Sep 4, 2007 3:39:10 AM org.hibernate.util.JDBCExceptionReporter 
logExceptions
SEVERE: ERROR: invalid input syntax for type oid: ""
Sep 4, 2007 3:39:10 AM 
org.hibernate.event.def.AbstractFlushingEventListener performExecutions
SEVERE: Could not synchronize database state with session

... <Hibernate stack trace clutter omitted> ...

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.
        at 
org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2534)
        at 
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1317)
        at 
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:350)
        at 
org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2596)
        at 
com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)
        at 
org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:57)
        at 
org.hibernate.jdbc.BatchingBatcher.addToBatch(BatchingBatcher.java:33)
        at 
org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:1853)
        ... 11 more
Sep 4, 2007 3:39:10 AM com.goldencode.p2j.schema.ImportWorker$Library 
removeFailingRecord
SEVERE: Dropped record #1225351 in turnarnd.d due to error:  ERROR: 
invalid input syntax for type oid: ""
Sep 4, 2007 3:40:26 AM org.hibernate.util.JDBCExceptionReporter 
logExceptions
WARNING: SQL Error: 0, SQLState: null
Sep 4, 2007 3:40:26 AM org.hibernate.util.JDBCExceptionReporter 
logExceptions
SEVERE: An SQLException was provoked by the following failure: 
java.lang.IllegalArgumentException: Can't change resolved type for 
param: 7 from 1043 to 26
...

The table schema looks like so at the time of the failure:

          Table "public.turnaround"
        Column         |  Type   | Modifiers
-----------------------+---------+-----------
 id                    | integer | not null
 turn_number           | text    |
 site                  | text    |
 item                  | text    |
 purchase_order_number | integer |
 purchase_order_line   | integer |
 lot                   | text    |
 serial_number         | text    |
 quantity_issued       | numeric |
 expiration_date       | date    |
Indexes:
    "turnaround_pkey" PRIMARY KEY, btree (id)
    "idx__turnaround_pi_turnarnd" UNIQUE, btree (upper(rtrim(turn_number, '
'::text)))

I hit this error 1,225,351 rows into a ~1.7 million row table.  
Unfortunately, I have not been able to recreate this problem with a 
smaller testcase.  If I try cutting down my import data set to just the 
failing row, or to that row and nearby rows, the import is clean.  I 
should note there was no such error using the identical data set with 
the 8.1.8 back-end/8.1-408 JDBC driver combination.

I tried using the development driver (postgresql-8.3dev-601.jdbc4.jar), 
but the same problem occurs.  Interestingly, if I drop back to the older 
driver I was using with the 8.1.8 back-end 
(postgresql-8.1-408.jdbc3.jar), but run it against the 8.2.4 back-end, 
the import completes without error!

So, my questions:

A) Can anyone think what might have changed between the 8.1-408 and 
8.2-506 versions of the JDBC driver which might result in an INVALID 
TEXT REPRESENTATION (22P02) error?

B) While the JDBC drivers are documented to be backward compatible with 
older back-ends, is there anything that would make it obviously unsafe 
for me to go the other way round, using the 8.1 JDBC driver with the 
8.2.4 back-end?  Although this doesn't seem to be a viable permanent 
solution, for the time being I have no working alternative for PG 8.2.4.

Thanks in advance,
Eric Faulhaber



Responses

pgsql-jdbc by date

Next:From: Dave CramerDate: 2007-09-17 21:59:22
Subject: Re: Batch INSERT failing with error 22P02
Previous:From: teknokratDate: 2007-09-17 12:58:53
Subject: Re: making the XARessource serializable

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