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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Maxime Lévesque | 2010-05-01 16:11:21 | SQLException.getErrorCode ? |
Previous Message | Kris Jurka | 2010-05-01 15:47:47 | Re: Glassfish ignoring "ssl" property in connection pool of PostgreSQL |