Обсуждение: Regression: Problems with Timestamp arguments

  Hello colleagues,

  There seems to be a problem with the latest driver and Timestamp
arguments uses in IS NULL comparisons:
  QUERY:     SELECT ? IS NULL
  ARGUMENTS: statement.setTimestamp(1, new
Timestamp(System.currentTimeMillis()))

  Result:
Exception in thread "main" org.postgresql.util.PSQLException: ERROR:
could not determine data type of parameter $1
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)

  The problem arises on:
  protocolVersion=3, or not specifying protocol version
  argument type: Timestamp

  The problem does not arise on other types (tested on BigDecimal and String)
  The problem does not arise when using protocolVersion=2
  The problem does not arise if the expression is not ? IS NULL

  List of work-around methods:
  - Degrade the protocol:
   jdbc:postgresql://host:port/database?protocolVersion=2
   * does not work with PgPool-2

  - Explicitly cast the argument
   SELECT ?::timestamp IS NULL
   SELECT CAST(? AS timestamp) IS NULL
   * requires rewriting of currently working code

  I believe this to be a bug.

   I (believe I) understand the issue.
  May I suggest a parameter to the Connection URL to specify how
Timestamp is sent to the server.

  I am not versed enough to make a patch to hold this proposal, but if
I must I will try to.

  How is this problem «solved» when using protocol version 2?
  Is there a way to degrade certain parts of the protocol?


2013/9/10 Dave Cramer <pg@fastcrypt.com>:
> OK, I recall the problem here. Timestamp is rather unique in that there are
> two different types of timestamps with and without timezone.
>
> As JDBC only has facility for one of them we have no way to say
> setTimestampTZ so we allow the server to infer the type.
>
> There is not much that can be done without breaking a bunch of other code,
> however I am open to suggestions
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
>
> On Tue, Sep 10, 2013 at 5:57 AM, Dave Cramer <pg@fastcrypt.com> wrote:
>>
>> Hi,
>>
>> if you try this: prepare foo as select $1 is null;
>> in psql you will get
>> ERROR:  could not determine data type of parameter $1
>>
>> Why it works with integers I don't know yet, but thought I would pass that
>> along
>>
>> Dave Cramer
>>
>> dave.cramer(at)credativ(dot)ca
>> http://www.credativ.ca
>>
>>
>> On Tue, Sep 10, 2013 at 5:17 AM, Lachezar Dobrev <l.dobrev@gmail.com>
>> wrote:
>>>
>>>   Hello colleagues,
>>>
>>>   There seems to be a problem with the latest driver and Timestamp
>>> arguments uses in IS NULL comparisons:
>>>   QUERY:     SELECT ? IS NULL
>>>   ARGUMENTS: statement.setTimestamp(1, new
>>> Timestamp(System.currentTimeMillis()))
>>>
>>>   Result:
>>> Exception in thread "main" org.postgresql.util.PSQLException: ERROR:
>>> could not determine data type of parameter $1
>>>     at
>>> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
>>>     at
>>> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
>>>     at
>>> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
>>>     at
>>> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
>>>     at
>>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
>>>     at
>>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
>>>
>>>   The problem arises on:
>>>   protocolVersion=3, or not specifying protocol version
>>>   argument type: Timestamp
>>>
>>>   The problem does not arise on other types (tested on BigDecimal and
>>> String)
>>>   The problem does not arise when using protocolVersion=2
>>>   The problem does not arise if the expression is not ? IS NULL
>>>
>>>   List of work-around methods:
>>>   - Degrade the protocol:
>>>    jdbc:postgresql://host:port/database?protocolVersion=2
>>>    * does not work with PgPool-2
>>>
>>>   - Explicitly cast the argument
>>>    SELECT ?::timestamp IS NULL
>>>    SELECT CAST(? AS timestamp) IS NULL
>>>    * requires rewriting of currently working code
>>>
>>>   I believe this to be a bug.
>>>
>>>
>>> --
>>> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>
>>
>

  This works:
   PREPARE is_date_reached AS SELECT NOW() < $1 OR $1 IS NULL;

  However JDBC can not use the same parameter more than once…
   SELECT NOW() < ? OR ? IS NULL;

  Hmm. That's not good.


2013/9/10 Dave Cramer <pg@fastcrypt.com>:
> Hi,
>
> if you try this: prepare foo as select $1 is null;
> in psql you will get
> ERROR:  could not determine data type of parameter $1
>
> Why it works with integers I don't know yet, but thought I would pass that
> along
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
>
> On Tue, Sep 10, 2013 at 5:17 AM, Lachezar Dobrev <l.dobrev@gmail.com> wrote:
>>
>>   Hello colleagues,
>>
>>   There seems to be a problem with the latest driver and Timestamp
>> arguments uses in IS NULL comparisons:
>>   QUERY:     SELECT ? IS NULL
>>   ARGUMENTS: statement.setTimestamp(1, new
>> Timestamp(System.currentTimeMillis()))
>>
>>   Result:
>> Exception in thread "main" org.postgresql.util.PSQLException: ERROR:
>> could not determine data type of parameter $1
>>     at
>> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
>>     at
>> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
>>     at
>> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
>>     at
>> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
>>     at
>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
>>     at
>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
>>
>>   The problem arises on:
>>   protocolVersion=3, or not specifying protocol version
>>   argument type: Timestamp
>>
>>   The problem does not arise on other types (tested on BigDecimal and
>> String)
>>   The problem does not arise when using protocolVersion=2
>>   The problem does not arise if the expression is not ? IS NULL
>>
>>   List of work-around methods:
>>   - Degrade the protocol:
>>    jdbc:postgresql://host:port/database?protocolVersion=2
>>    * does not work with PgPool-2
>>
>>   - Explicitly cast the argument
>>    SELECT ?::timestamp IS NULL
>>    SELECT CAST(? AS timestamp) IS NULL
>>    * requires rewriting of currently working code
>>
>>   I believe this to be a bug.
>>
>>
>> --
>> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>

2013/9/10 Dave Cramer <pg@fastcrypt.com>:
> Protocol 2 just does textual replacement of the parameters and does not use
> prepared statements on the server.

  I see…
  That would also answer another question about why null arguments
fail with the current protocol, but don't with protocol 2 (Hibernate
uses Serializable -> bytea which can not be used in any way).

> There is no way to degrade only certain parts. Why is it not possible to
> cast this to timestamp ?

  That works:
   SELECT CAST(? AS TIMESTAMP) IS NULL;

  However this is something that would mandate rewriting hundreds of
queries in a working (with protocol version 2) product.

> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca

  I've made a blunt stab at working around the issue.
  The attached patch adds a new parameter to the JDBC URL: timestamp.
The parameter can be wither 'with_timezone' or 'without_timezone'. If
the parameter is missing, or has a different value then the current
behaviour (using Oid.UNSPECIFIED) is exhibited. With the parameters
specified correctly one of the Oid.TIMESTAMP or Oid.TIMESTAMPTZ is
used when sending Timestamp arguments to the server.

  I am not sure whether this is the correct way to request new
features/enhancements, or what is the work-flow for accepting patches,
so feel free to scold me.

  I believe the patch to be backwards compatible and very
light-weight, but it may deviate from the code formatting and code
practices of the project.


2013/9/10 Lachezar Dobrev <l.dobrev@gmail.com>:
>    I (believe I) understand the issue.
>   May I suggest a parameter to the Connection URL to specify how
> Timestamp is sent to the server.
>
>   I am not versed enough to make a patch to hold this proposal, but if
> I must I will try to.
>
>   How is this problem «solved» when using protocol version 2?
>   Is there a way to degrade certain parts of the protocol?

  Please note, that a parasite line (for a missing file) had slipped
into the patch.
  I apologise for that. I will remake the patch if needed.

2013/9/10 Lachezar Dobrev <l.dobrev@gmail.com>:
>   I've made a blunt stab at working around the issue.
>   The attached patch adds a new parameter to the JDBC URL: timestamp.
> The parameter can be wither 'with_timezone' or 'without_timezone'. If
> the parameter is missing, or has a different value then the current
> behaviour (using Oid.UNSPECIFIED) is exhibited. With the parameters
> specified correctly one of the Oid.TIMESTAMP or Oid.TIMESTAMPTZ is
> used when sending Timestamp arguments to the server.
>
>   I am not sure whether this is the correct way to request new
> features/enhancements, or what is the work-flow for accepting patches,
> so feel free to scold me.
>
>   I believe the patch to be backwards compatible and very
> light-weight, but it may deviate from the code formatting and code
> practices of the project.
>
>
> 2013/9/10 Lachezar Dobrev <l.dobrev@gmail.com>:
>>    I (believe I) understand the issue.
>>   May I suggest a parameter to the Connection URL to specify how
>> Timestamp is sent to the server.
>>
>>   I am not versed enough to make a patch to hold this proposal, but if
>> I must I will try to.
>>
>>   How is this problem «solved» when using protocol version 2?
>>   Is there a way to degrade certain parts of the protocol?

On Tue, 10 Sep 2013, Lachezar Dobrev wrote:

>   I've made a blunt stab at working around the issue.
>   The attached patch adds a new parameter to the JDBC URL: timestamp.
> The parameter can be wither 'with_timezone' or 'without_timezone'. If
> the parameter is missing, or has a different value then the current
> behaviour (using Oid.UNSPECIFIED) is exhibited. With the parameters
> specified correctly one of the Oid.TIMESTAMP or Oid.TIMESTAMPTZ is
> used when sending Timestamp arguments to the server.

This won't really work in general though.  If you have a database that has
both timestamp and timestamptz fields in it, there is no valid setting of
this configuration option.  Setting it either way will result in silent
corruption of your data.

Kris Jurka

Why so? I thought if driver will pass timestamp with tz some queries won't work because of lack of implicit conversion, but I don't understand how can it corrupt anything.

On Wed, 11 Sep 2013, Vitalii Tymchyshyn wrote:

> Why so? I thought if driver will pass timestamp with tz some queries won't
> work because of lack of implicit conversion, but I don't understand how can
> it corrupt anything.
>

It's rather complicated, but currently without knowing the datatype on the
server side, the driver must pick a datatype (timestamp with or without
timestamp or unknown) and a string representation of that data (include or
not the timezone offset).  Now you can get away with this problem if the
client and server have the same timezone settings, but not if they don't.

Consider the attached test code.  It prepares a statement with all three
datatype options and then executes each statement using both string
representations.

When the client and server timezones are the same, it doesn't matter what
method we use, all the results are the same.  If they are different, then
only id 9 (unknown datatype, string with timezone offset) provides the
correct result for both table columns.  It might be possible to work
around this by explicitly setting the server timezone or querying the
server for its timezone and making adjustments on the client side to the
data before sending it to the server.

Kris Jurka

  You're technically correct.

  Maybe this can be applied to NULL arguments only? Would that be
'more' acceptable?
  Lately I've been proven wrong with many of my (reasonable)
expectations, but I *hope* NULL timestamps with or without time zones
are the same!

2013/9/11 Kris Jurka <books@ejurka.com>:
>
>
> On Tue, 10 Sep 2013, Lachezar Dobrev wrote:
>
>>   I've made a blunt stab at working around the issue.
>>   The attached patch adds a new parameter to the JDBC URL: timestamp.
>> The parameter can be wither 'with_timezone' or 'without_timezone'. If
>> the parameter is missing, or has a different value then the current
>> behaviour (using Oid.UNSPECIFIED) is exhibited. With the parameters
>> specified correctly one of the Oid.TIMESTAMP or Oid.TIMESTAMPTZ is
>> used when sending Timestamp arguments to the server.
>
> This won't really work in general though.  If you have a database that has
> both timestamp and timestamptz fields in it, there is no valid setting of
> this configuration option.  Setting it either way will result in silent
> corruption of your data.
>
> Kris Jurka

  Please disregard.
  The problem is not with NULL arguments, but with any Timestamps.

2013/9/11 Lachezar Dobrev <l.dobrev@gmail.com>:
>   You're technically correct.
>
>   Maybe this can be applied to NULL arguments only? Would that be
> 'more' acceptable?
>   Lately I've been proven wrong with many of my (reasonable)
> expectations, but I *hope* NULL timestamps with or without time zones
> are the same!
>
> 2013/9/11 Kris Jurka <books@ejurka.com>:
>>
>>
>> On Tue, 10 Sep 2013, Lachezar Dobrev wrote:
>>
>>>   I've made a blunt stab at working around the issue.
>>>   The attached patch adds a new parameter to the JDBC URL: timestamp.
>>> The parameter can be wither 'with_timezone' or 'without_timezone'. If
>>> the parameter is missing, or has a different value then the current
>>> behaviour (using Oid.UNSPECIFIED) is exhibited. With the parameters
>>> specified correctly one of the Oid.TIMESTAMP or Oid.TIMESTAMPTZ is
>>> used when sending Timestamp arguments to the server.
>>
>> This won't really work in general though.  If you have a database that has
>> both timestamp and timestamptz fields in it, there is no valid setting of
>> this configuration option.  Setting it either way will result in silent
>> corruption of your data.
>>
>> Kris Jurka