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

Re: Strange behavior after setting timestamp field to null - A bug?

From: Kris Jurka <books(at)ejurka(dot)com>
To: "Jeenicke, Martti" <martti(dot)jeenicke(at)coremedia(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Strange behavior after setting timestamp field to null - A bug?
Date: 2010-05-01 16:08:52
Message-ID: alpine.BSO.2.00.1005011208320.13830@leary.csoft.net (view raw or flat)
Thread:
Lists: pgsql-jdbc

On Mon, 8 Feb 2010, Kris Jurka wrote:

> On Mon, 8 Feb 2010, Jeenicke, Martti wrote:
>
>> Hi there,
>> 
>> we have noticed an odd problem/bug when working with timestamp fields in
>> prepared statements. The problem arises when setting the timestamp field
>> to null. The test class to reproduce the behavior produces the following
>> output:
>> 
>> 08.02.2010 16:36:20
>> 08.02.2010 16:36:20
>> 08.02.2010 16:36:20
>> 08.02.2010 17:36:20
>
> I've looked into this a little bit.  The problem is how the data gets typed 
> when it is sent to the server.  When calling setTimestamp, the driver doesn't 
> know whether the server type that will be used will be with or without a 
> timezone.  (The SQL Standard and the JDBC API don't match up well here.)  So 
> it sends the data as type "unknown" and lets the server figure out how to 
> deal with it because it has additional type information. When calling 
> setNull, the driver thought it was safe to type it as timestamp with timezone 
> to try and help type inference because NULL values look the same with or 
> without timezones.  This is looked OK, but you've caught the case here where 
> it is not.  By default, the fifth execution of a PreparedStatement will 
> establish a more permanent execution plan that will then be re-used for later 
> executions.  So the fifth execution in your test is a setNull case and that 
> is effectively establishing the types that a later execution will use as 
> well.  So later executions fail to pass the data as "unknown" and are instead 
> passing it as "timestamp with tz" which does not match up with your table, so 
> you get the mystery drift.
>
> The attached patch fixes things for me in a simple test, but I'd like to look 
> at it a little more before I commit it.
>

I've committed this patch to CVS.

Kris Jurka

In response to

pgsql-jdbc by date

Next:From: Maxime LévesqueDate: 2010-05-01 16:11:21
Subject: SQLException.getErrorCode ?
Previous:From: Kris JurkaDate: 2010-05-01 15:47:47
Subject: Re: Glassfish ignoring "ssl" property in connection pool of PostgreSQL

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