Batch INSERT failing with error 22P02 - Mailing list pgsql-jdbc

From Eric Faulhaber
Subject Batch INSERT failing with error 22P02
Date
Msg-id 46EEE4EF.80608@goldencode.com
Whole thread Raw
Responses Re: Batch INSERT failing with error 22P02
Re: Batch INSERT failing with error 22P02
List 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



pgsql-jdbc by date:

Previous
From: teknokrat
Date:
Subject: Re: making the XARessource serializable
Next
From: Dave Cramer
Date:
Subject: Re: Batch INSERT failing with error 22P02