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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-jdbc by date

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