Thread: What changed?

What changed?

From
Paul Tomblin
Date:
This code worked in various versions of Postgres up to and including 7.4
(as bundled with CentOS 4.3), but fails on 8.1 (as bundled with CentOS
5.0) with an exception about being unable to determine the type of
parameter $2:

    java.sql.Timestamp startDBDate = null;
    if (startDate != null)
    {
      startDBDate = new java.sql.Timestamp(startDate.getTime());
    }

    try
    {
      PreparedStatement stmt = con.prepareStatement(
                "SELECT    " +  SHOWLIST_COLUMNS +
                "FROM           showlists s, showlistsplaylists sc " +
                "WHERE          s.id = sc.showlistid AND " +
                "               sc.playlistid = ? AND " +
                "               (? IS NULL OR s.showtime >= ?) " +
                "ORDER BY       screen, showtime");
      int               p = 1;
      stmt.setLong(p, playlistID);
      p++;
      if (startDate == null)
      {
        stmt.setNull(p, java.sql.Types.TIMESTAMP);
        p++;
        stmt.setNull(p, java.sql.Types.TIMESTAMP);
        p++;
      }
      else
      {
        stmt.setTimestamp(p, startDBDate);
        p++;
        stmt.setTimestamp(p, startDBDate);
        p++;
      }

      ResultSet rs      = stmt.executeQuery();
      SortedSet retList = parseShowListResultSet(con, rs, venueInfoList);

      stmt.close();

Based on something I saw in the list archives, I got it to work by casting
the timestamp parameters using "?::timestamptz".  But I'm wondering why
that changed, and are there any other gotchas lurking?  Should I cast any
timestamp parameter to either ::timestamp or ::timestamptz depending on
what it is in the db?

--
Paul Tomblin <ptomblin@xcski.com> http://blog.xcski.com/
I think I'd like to see a Simpsons episode starting up with Bart Simpson
writing 'I will not attempt to undermine the Usenet Cabal'.
              -- J. D. Falk

Re: What changed?

From
Mark Lewis
Date:
This is addressed in the FAQ, look at the section titled, "I upgraded
from 7.x to 8.x. Why did my application break?"

http://jdbc.postgresql.org/documentation/faq.html#upgradeTo80

There's also plenty of discussion in the archives that you'll find.

-- Mark Lewis

On Fri, 2007-04-13 at 14:47 -0400, Paul Tomblin wrote:
> This code worked in various versions of Postgres up to and including 7.4
> (as bundled with CentOS 4.3), but fails on 8.1 (as bundled with CentOS
> 5.0) with an exception about being unable to determine the type of
> parameter $2:
>
>     java.sql.Timestamp startDBDate = null;
>     if (startDate != null)
>     {
>       startDBDate = new java.sql.Timestamp(startDate.getTime());
>     }
>
>     try
>     {
>       PreparedStatement stmt = con.prepareStatement(
>                 "SELECT    " +  SHOWLIST_COLUMNS +
>                 "FROM           showlists s, showlistsplaylists sc " +
>                 "WHERE          s.id = sc.showlistid AND " +
>                 "               sc.playlistid = ? AND " +
>                 "               (? IS NULL OR s.showtime >= ?) " +
>                 "ORDER BY       screen, showtime");
>       int               p = 1;
>       stmt.setLong(p, playlistID);
>       p++;
>       if (startDate == null)
>       {
>         stmt.setNull(p, java.sql.Types.TIMESTAMP);
>         p++;
>         stmt.setNull(p, java.sql.Types.TIMESTAMP);
>         p++;
>       }
>       else
>       {
>         stmt.setTimestamp(p, startDBDate);
>         p++;
>         stmt.setTimestamp(p, startDBDate);
>         p++;
>       }
>
>       ResultSet rs      = stmt.executeQuery();
>       SortedSet retList = parseShowListResultSet(con, rs, venueInfoList);
>
>       stmt.close();
>
> Based on something I saw in the list archives, I got it to work by casting
> the timestamp parameters using "?::timestamptz".  But I'm wondering why
> that changed, and are there any other gotchas lurking?  Should I cast any
> timestamp parameter to either ::timestamp or ::timestamptz depending on
> what it is in the db?
>

Re: What changed?

From
Paul Tomblin
Date:
Quoting Mark Lewis (mark.lewis@mir3.com):
> This is addressed in the FAQ, look at the section titled, "I upgraded
> from 7.x to 8.x. Why did my application break?"
>
> http://jdbc.postgresql.org/documentation/faq.html#upgradeTo80

Except neither of the two categories documented there apply to me.  The
problem I'm having is that I'm calling either
    stmt.setNull(p, java.sql.Types.TIMESTAMP);
or
    stmt.setTimestamp(p, startDBDate);
and it's staying that it can't tell the type, in spite of the fact that I
do specify it in both cases.

--
Paul Tomblin <ptomblin@xcski.com> http://blog.xcski.com/
"We sealed our federal pact without bloodshed and without exploitation of
the weak by the strong.  All it took was fairness, justice and some
compromises on both sides." - George-Etienne Cartier.

Re: What changed?

From
Mark Lewis
Date:
On Fri, 2007-04-13 at 15:33 -0400, Paul Tomblin wrote:
> Quoting Mark Lewis (mark.lewis@mir3.com):
> > This is addressed in the FAQ, look at the section titled, "I upgraded
> > from 7.x to 8.x. Why did my application break?"
> >
> > http://jdbc.postgresql.org/documentation/faq.html#upgradeTo80
>
> Except neither of the two categories documented there apply to me.  The
> problem I'm having is that I'm calling either
>     stmt.setNull(p, java.sql.Types.TIMESTAMP);
> or
>     stmt.setTimestamp(p, startDBDate);
> and it's staying that it can't tell the type, in spite of the fact that I
> do specify it in both cases.

Sorry, guess I was reading a little too quickly the first time around :)

More recent versions of the driver send UNSPECIFIED for timestamps
(except in the setNull case where they always send TIMESTAMPTZ, but I
don't think that matters), which allows the server to "do the right
thing" most of the time.  I don't have time to really dig into it right
now but I'm guessing that you're hitting a case where it fails to do the
right thing.

-- Mark







Re: What changed?

From
Dave Cramer
Date:
Paul,

Have you tried a recent driver ?

As Mark pointed out we have some issues with timestamps because java
does not recognize two types.

Dave

On 13-Apr-07, at 2:47 PM, Paul Tomblin wrote:

> This code worked in various versions of Postgres up to and
> including 7.4
> (as bundled with CentOS 4.3), but fails on 8.1 (as bundled with CentOS
> 5.0) with an exception about being unable to determine the type of
> parameter $2:
>
>     java.sql.Timestamp startDBDate = null;
>     if (startDate != null)
>     {
>       startDBDate = new java.sql.Timestamp(startDate.getTime());
>     }
>
>     try
>     {
>       PreparedStatement stmt = con.prepareStatement(
>                 "SELECT    " +  SHOWLIST_COLUMNS +
>                 "FROM           showlists s, showlistsplaylists sc " +
>                 "WHERE          s.id = sc.showlistid AND " +
>                 "               sc.playlistid = ? AND " +
>                 "               (? IS NULL OR s.showtime >= ?) " +
>                 "ORDER BY       screen, showtime");
>       int               p = 1;
>       stmt.setLong(p, playlistID);
>       p++;
>       if (startDate == null)
>       {
>         stmt.setNull(p, java.sql.Types.TIMESTAMP);
>         p++;
>         stmt.setNull(p, java.sql.Types.TIMESTAMP);
>         p++;
>       }
>       else
>       {
>         stmt.setTimestamp(p, startDBDate);
>         p++;
>         stmt.setTimestamp(p, startDBDate);
>         p++;
>       }
>
>       ResultSet rs      = stmt.executeQuery();
>       SortedSet retList = parseShowListResultSet(con, rs,
> venueInfoList);
>
>       stmt.close();
>
> Based on something I saw in the list archives, I got it to work by
> casting
> the timestamp parameters using "?::timestamptz".  But I'm wondering
> why
> that changed, and are there any other gotchas lurking?  Should I
> cast any
> timestamp parameter to either ::timestamp or ::timestamptz
> depending on
> what it is in the db?
>
> --
> Paul Tomblin <ptomblin@xcski.com> http://blog.xcski.com/
> I think I'd like to see a Simpsons episode starting up with Bart
> Simpson
> writing 'I will not attempt to undermine the Usenet Cabal'.
>               -- J. D. Falk
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match


Re: What changed?

From
Paul Tomblin
Date:
Quoting Dave Cramer (pg@fastcrypt.com):
> Paul,
>
> Have you tried a recent driver ?
>
> As Mark pointed out we have some issues with timestamps because java
> does not recognize two types.

I'm constrained to keep the version that's in CentOS 5.0 unless I can make
a business case for upgrading.  But in this case I can work around it by
casting all timestamp and timestamptz parameters.  I'm just worried about
what other pitfalls I might fall into.

--
Paul Tomblin <ptomblin@xcski.com> http://blog.xcski.com/
Reliability went through the floor, tunnelled its way to the centre of
the Earth, and perished in the magma.
              -- Saundo