Re: Regression: Problems with Timestamp arguments

Protocol 2 just does textual replacement of the parameters and does not use
prepared statements on the server.

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

Dave Cramer

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

On Tue, Sep 10, 2013 at 6:48 AM, Lachezar Dobrev <l(dot)dobrev(at)gmail(dot)com> wrote:

> 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(at)fastcrypt(dot)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(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
> >>
> >>
> >
>
>
> --
> 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
>