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: