Re: Batch INSERT failing with error 22P02

[Kris Jurka <books(at)ejurka(dot)com>]

On Thu, 20 Sep 2007, Eric Faulhaber wrote:

> Eric Faulhaber wrote:
> > Kris Jurka wrote:
> > > 2) Where's the unspecified type coming from?  I've put in a hack to the 
> > > driver to throw an exception when the first unspecified type is set which 
> > > should hopefully provide enough information to track it back to your 
> > > application.  Unspecified types are perfectly legal, so this isn't a real 
> > > problem, but it may be informational.
> >
> > I turned on some additional logging in our code to see what data is being 
> > bound to each parameter in our UserType implementation.  It seems the 
> > unspecified type is coming from a Date object bound with 
> > PreparedStatement.setDate(int, java.sql.Date).  At least, this is the first 
> > parameter that triggers the test.

I had forgotten that we treated dates like timestamps, so this is the 
cause.  We can't be sure what the underlying type for a java.sql.Timestamp 
object is because on the server side we have timestamps with and without 
timezones and if you pick the wrong type you can get an incorrect timezone 
shift, so we've got to use unspecified to let the server determine the 
correct type.  I'm not sure if Date needs the same treatment because you 
would expect a date column on the server, but mismatching these is 
something people do frequently, so I'm hesitant to make any changes here 
without a lot more thought about how date works.  But again this can't be 
the real problem/fix because this has to work like this if your column was 
a timestamp type.

> How relevant is the following change to this mystery?  This is an excerpt 
> from a diff of AbstractJdbc2Statement between the versions we've been 
> discussing, specifically, the internals of the setDate(int, Date, Calendar) 
> method:

That's just a rename.  See here:

http://gborg.postgresql.org/cgi-bin/cvsweb.cgi/pgjdbc/org/postgresql/core/Oid.java.diff?r1=1.9;r2=1.10;cvsroot=pgjdbc

> I mean, it obviously explains where the unspecified type is coming from, but 
> how likely is that to trigger the FE/BE type mismatch we're seeing many 
> records later?

It shouldn't cause a mismatch.  The fact that we have an unspecified type 
causes the describe statement which then triggers the verification that we 
got the same types back from the server that we have in our parameters, so 
it triggers the failure, but it is not the real problem.  The verification 
check specifically skips unspecified types (oid=0) and if you'll recall 
your error message was a conflict between 1043 and 26, so no oid=0 there 
at all.

Kris Jurka