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

Re: Batch INSERT failing with error 22P02

From: Kris Jurka <books(at)ejurka(dot)com>
To: Eric Faulhaber <ecf(at)goldencode(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Batch INSERT failing with error 22P02
Date: 2007-09-20 02:46:55
Message-ID: Pine.BSO.4.64.0709181916350.449@leary.csoft.net (view raw or flat)
Thread:
Lists: pgsql-jdbc

On Tue, 18 Sep 2007, Eric Faulhaber wrote:

> 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...

Basically you have two text columns in your table (lot and serial_number) 
that alternate between backend type oids 1043 (varchar) and 26 (oid). 
This is most likely because hibernate sometimes calls setClob and 
sometimes it calls setString for these columns.  Additionally you have 
something that is setting an unknown type in the driver that the server 
must resolve.  This could be something like setNull(x, Types.OTHER), 
setObject(x, null), or by using the URL parameter stringtype=unspecified. 
An unspecified type is common when using timestamps, but you aren't using 
any in this table.

So what's going on is, as you work through the rows you want to insert the 
types of these columns occasionally changes and a the driver must reparse 
a new server side prepared statement to handle the new type.  Some time 
after the new server side statement has been setup we get an unspecified 
type and we ask the server to tell us what type we should really use.  The 
server responds with a new type for something we don't think should have 
changed and we get an error.

Consider this set of events:

Server Has	Current Data	Action
nothing yet	1043, 26	No server statement yet, parse
1043, 26	1043, 26	server matches, execute away
1043, 26	1043, 1043	server doesn't match, reparse
1043, 1043	1043, 0		0 matches, so don't reparse, but describe

So we are passed an unknown type and we ask the server to resolve it. 
When we get the describe answer back we set it on the data, overwriting 
the 0 with the returned 1043.  So that's all legal and expected behavior, 
but for some reason in your case.

So one potential solution (and optimization) is to avoid the describe if 
we already have a prepared statement with type information filled in.  We 
expect the backend to just echo back what the statement was prepared with, 
but what's the point when we have that already have that information in 
the driver?  So this would likely avoid your problem, but it doesn't 
explain what's going on...

I diffed the 8.1-408 release with 8.3dev for the core/v3 directory and the 
only relevant change is SimpleParameterList#bind.  When deciding to 
overwrite an existing type we now add a check for NULL_OBJECT that 8.1 
does not have:

if (oid == Oid.UNSPECIFIED && paramTypes[index] != Oid.UNSPECIFIED && 
value == NULL_OBJECT)
             return;

I know I haven't described this well, especially for people who aren't 
versed in the frontend/backend protocol and how the driver issues queries, 
but I must admit I have no idea why this is failing.  There is a potential 
workaround, but it doesn't explain the root cause.  I'll keep 
investigating though...

1) Are you using stringtype=unspecified in your URL?

2) Any idea why these two columns are sometimes interpreted as strings and 
sometimes as Clobs?  It doesn't look to be the case with the other text 
fields in your table.

3) The log file you sent me says: "Using 2 threads for import".  Could you 
elaborate on what the different threads are doing?  Any chance they're 
using the same Connection and somehow interfering with each other?

Kris Jurka

In response to

Responses

pgsql-jdbc by date

Next:From: Albe LaurenzDate: 2007-09-20 07:37:01
Subject: Re: Postgres 8.2 and setFetchSize
Previous:From: Oliver JowettDate: 2007-09-19 14:17:33
Subject: Re: Postgres 8.2 and setFetchSize

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