Re: Regression: Problems with Timestamp arguments

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(at)fastcrypt(dot)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(dot)dobrev(at)gmail(dot)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(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>
>
>