Re: Bug in JDBC-Driver?

Hi all,

I am testing Postgres 8.0.0 beta4 on Windows 2000. I checked the changelog
but could not find a corresponding topic.
I am facing a time problem with timestamp handling. Here is what I did: I
insert data via JDBC. The timestamp value in the prepared statement is still
'2003-08-19 11:40:08.0' as the debugger (of Eclipse) confirms. Inserted in
the database gets a tiemstamp 2003-08-19 09:40:08.0'. CURRENT_TIME of
DB-Server is an hour back to server time.
Before I do the insert I do a select to check if such data is already in the
DB. The select does not change the timestamp - so program goes in insert
method.
I used the JDBC-Driver pg80b1.308.jdbc3.jar coming with the German
postgresql-8.0.0-beta4.msi
Windows says RevisionNo: {6ABA084E-D3FB-4253-95FF-68A0A066F69B}, if that is
of any help?

Today I tried my program using the JDBC-Driver of Postgres 7.4.6
(pg74.215.jdbc3.jar). With that JDBC- driver everything works fine. Is there
any parameter I missed to set in version 8, or is it a bug?

Regards,
Antje Stejskal

On Mon, 29 Nov 2004 Antje(dot)Stejskal(at)ppi(dot)de wrote:

> I am facing a time problem with timestamp handling. Here is what I did: I
> insert data via JDBC. The timestamp value in the prepared statement is still
> '2003-08-19 11:40:08.0' as the debugger (of Eclipse) confirms. Inserted in
> the database gets a tiemstamp 2003-08-19 09:40:08.0'. CURRENT_TIME of
> DB-Server is an hour back to server time.
> I used the JDBC-Driver pg80b1.308.jdbc3.jar
>
> Today I tried my program using the JDBC-Driver of Postgres 7.4.6
> (pg74.215.jdbc3.jar). With that JDBC- driver everything works fine. Is there
> any parameter I missed to set in version 8, or is it a bug?
>

Yes, this looks like a driver bug, but I don't see an easy way to get
around it. The problem arises from the fact that you are using a
timestamp without time zone and the 8.0 driver using the V3 protocol types
all java.sql.Timestamp objects as timestamp with time zone. In a -08
timezone note:

Just discards the zone information

jurka=# SELECT '2004-11-29 22:09:59.079-09'::timestamp;
timestamp
-------------------------
2004-11-29 22:09:59.079
(1 row)

Converts to the server's zone and then discards.

jurka=# SELECT '2004-11-29 22:09:59.079-09'::timestamptz::timestamp;
timestamp
-------------------------
2004-11-29 23:09:59.079
(1 row)

So you by going through the intermediate with time zone type the value is
changed. For your situation you can just switch to a column type that has
zone information. To fix this in the driver it seems we would need to be
aware of the database server's timezone and adjust data going in one
direction or the other.

The attached test case illustrates the problem by setting up the server to
be in US Eastern and the java client to be in US Pacific timezones and
comparing the results when running with the V2/V3 protocols.

Kris Jurka

Kris,

Seems to me we would have the same issues wth Date without time zone, no?

Here's a couple of possible solutions.
Since timestamp without timezone is a postgresql extension, that falls
outside the specification of the interface

we could 1) create an org.postgresql.Statement.setTimeStampz()
2) create an org.postgresql.types.Timestampz, Datez

This would also solve the issue with interval, as currently the driver
doesn't handle interval (AFAIK)

Dave

Kris Jurka wrote:

>On Mon, 29 Nov 2004 Antje(dot)Stejskal(at)ppi(dot)de wrote:
>
>
>
>>I am facing a time problem with timestamp handling. Here is what I did: I
>>insert data via JDBC. The timestamp value in the prepared statement is still
>>'2003-08-19 11:40:08.0' as the debugger (of Eclipse) confirms. Inserted in
>>the database gets a tiemstamp 2003-08-19 09:40:08.0'. CURRENT_TIME of
>>DB-Server is an hour back to server time.
>>I used the JDBC-Driver pg80b1.308.jdbc3.jar
>>
>>Today I tried my program using the JDBC-Driver of Postgres 7.4.6
>>(pg74.215.jdbc3.jar). With that JDBC- driver everything works fine. Is there
>>any parameter I missed to set in version 8, or is it a bug?
>>
>>
>>
>
>Yes, this looks like a driver bug, but I don't see an easy way to get
>around it. The problem arises from the fact that you are using a
>timestamp without time zone and the 8.0 driver using the V3 protocol types
>all java.sql.Timestamp objects as timestamp with time zone. In a -08
>timezone note:
>
>Just discards the zone information
>
>jurka=# SELECT '2004-11-29 22:09:59.079-09'::timestamp;
> timestamp
>-------------------------
> 2004-11-29 22:09:59.079
>(1 row)
>
>Converts to the server's zone and then discards.
>
>jurka=# SELECT '2004-11-29 22:09:59.079-09'::timestamptz::timestamp;
> timestamp
>-------------------------
> 2004-11-29 23:09:59.079
>(1 row)
>
>So you by going through the intermediate with time zone type the value is
>changed. For your situation you can just switch to a column type that has
>zone information. To fix this in the driver it seems we would need to be
>aware of the database server's timezone and adjust data going in one
>direction or the other.
>
>The attached test case illustrates the problem by setting up the server to
>be in US Eastern and the java client to be in US Pacific timezones and
>comparing the results when running with the V2/V3 protocols.
>
>Kris Jurka
>
>------------------------------------------------------------------------
>
>import java.sql.*;
>
>public class zone {
>
> public static void main(String args[]) throws Exception {
> Class.forName("org.postgresql.Driver");
>
> // Setup the Java client to be in PST8PDT.
> java.util.TimeZone.setDefault(java.util.TimeZone.getTimeZone("America/Los_Angeles"));
> runWith(2);
> runWith(3);
> }
>
> private static void runWith(int protocolVersion) throws SQLException {
> System.out.println("Running with protocol: " + protocolVersion);
> Connection conn = DriverManager.getConnection("jdbc:postgresql://192.168.3.3:5432/jurka?protocolVersion="+protocolVersion,"jurka","");
>
> Statement stmt = conn.createStatement();
> // Setup the db server to be in EST5EDT.
> stmt.execute("SET TIMEZONE TO 'EST5EDT'");
>
> // Create a test table showing the difference with/without tz.
> stmt.execute("CREATE TEMP TABLE tstest(a timestamp without time zone, b timestamp with time zone)");
>
>
> // This timestamp object will have a timezone of the default
> // java setting we configured earlier.
> Timestamp ts = new Timestamp((new java.util.Date()).getTime());
> System.out.println("Before insert = " + formatTS(ts));
>
> PreparedStatement pstmt = conn.prepareStatement("INSERT INTO tstest(a,b) VALUES(?,?)");
> pstmt.setTimestamp(1, ts);
> pstmt.setTimestamp(2, ts);
> pstmt.executeUpdate();
> pstmt.close();
>
> ResultSet rs = stmt.executeQuery("SELECT a,b FROM tstest");
> while (rs.next()) {
> System.out.println("getString() = getTimestamp()");
> System.out.println(rs.getString(1) + " = " + formatTS(rs.getTimestamp(1)));
> System.out.println(rs.getString(2) + " = " + formatTS(rs.getTimestamp(2)));
> }
> rs.close();
>
> stmt.close();
> conn.close();
> System.out.println();
> }
>
> private static String formatTS(Timestamp ts) {
> int offset = ts.getTimezoneOffset();
> int absoff = Math.abs(offset);
> int hrs = absoff/60;
> int mins = absoff - hrs*60;
> String sign = (offset > 0) ? "-" : "+";
> String hrStr = ((hrs < 10) ? "0" : "") + hrs;
> String minStr = ((mins < 10) ? "0" : "") + mins;
> return ts.toString() + sign + hrStr + ":" + minStr;
> }
>
>}
>
>
>------------------------------------------------------------------------
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561

On Tue, 30 Nov 2004, Dave Cramer wrote:

> Seems to me we would have the same issues wth Date without time zone, no?

dates do not have timezones, but we do have time with and without time
zone that is undoubtedly affected by this same problem.

> Since timestamp without timezone is a postgresql extension

This is not true.

> This would also solve the issue with interval, as currently the driver
> doesn't handle interval (AFAIK)
>

Interval is handled by

setObject(new org.postgresql.util.PGInterval("1 day"));

Kris Jurka

Kris Jurka wrote:

>On Tue, 30 Nov 2004, Dave Cramer wrote:
>
>
>
>>Seems to me we would have the same issues wth Date without time zone, no?
>>
>>
>
>dates do not have timezones, but we do have time with and without time
>zone that is undoubtedly affected by this same problem.
>
>
>
>>Since timestamp without timezone is a postgresql extension
>>
>>
>
>This is not true.
>
>
Well, one of them must be an extension so it is timestampz ?

>
>
>>This would also solve the issue with interval, as currently the driver
>>doesn't handle interval (AFAIK)
>>
>>
>>
>
>Interval is handled by
>
>setObject(new org.postgresql.util.PGInterval("1 day"))
>
>
>Kris Jurka
>
>
>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561

Dave Cramer <pg(at)fastcrypt(dot)com> writes:
> Kris Jurka wrote:
>> On Tue, 30 Nov 2004, Dave Cramer wrote:
>>> Since timestamp without timezone is a postgresql extension
>>
>> This is not true.
>>
> Well, one of them must be an extension so it is timestampz ?

timestamp with and without timezone are both required by the SQL
standard. If JDBC only supports one of them I'd think the JDBC
spec is a tad broken.

regards, tom lane

Well, I'm not going to defend the spec, as it's broken in a few places.

However there is no setTimestamp without timezone.

Dave

Tom Lane wrote:

>Dave Cramer <pg(at)fastcrypt(dot)com> writes:
>
>
>>Kris Jurka wrote:
>>
>>
>>>On Tue, 30 Nov 2004, Dave Cramer wrote:
>>>
>>>
>>>>Since timestamp without timezone is a postgresql extension
>>>>
>>>>
>>>This is not true.
>>>
>>>
>>>
>>Well, one of them must be an extension so it is timestampz ?
>>
>>
>
>timestamp with and without timezone are both required by the SQL
>standard. If JDBC only supports one of them I'd think the JDBC
>spec is a tad broken.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>
>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561

Dave Cramer wrote:
> Well, I'm not going to defend the spec, as it's broken in a few places.

Only a few? ;)

> However there is no setTimestamp without timezone.

java.sql.Timestamp stores UTC milliseconds-since-epoch. It also stores a
timezone offset (in minutes?!) and has YMD/HMS accessors -- but all the
timezone functionality is deprecated since JDK1.1 in favor of passing
the timestamp to an appropriate Calendar object (that has timezone info
plus locale-specific formatting logic). So it seems like Timestamp
itself is essentially "without timezone".

There is PreparedStatement.setTimestamp(column, timestamp) vs.
PreparedStatement.setTimestamp(column, timestamp, calendar). It's not
clear whether this is meant to do without-timezone vs. with-timezone,
though. The javadoc for the second variant talks about using the
provided calendar to construct a SQL DATE value, but does that mean
"store timestamp with calendar's timezone" or "convert timestamp to
local time in the given calendar and store without timezone" or
something else again? And the first variant says nothing at all about
timezones!

ResultSet has getTimestamp(column) and getTimestamp(column, calendar).
The second variant claims to do conversion of timestamp without timezone
to UTC milliseconds using the given calendar, but only for the "without
timezone" case. If you have "with timezone" the conversion to UTC just
happens automatically, and there seems to be no way to retrieve the
stored timezone information separately. Perhaps the driver is meant to
setTimezone() on the supplied calendar, but the javadoc says nothing
about this.

My head hurts..

How do other JDBC drivers handle this?

-O

Hi All,

We are performing intensive stress test of C-JDBC on top of a cluster of
4 postgresql servers.

During a run, one of the insert request was blocked in the postgresql
JDBC driver (latest version ...)

Here is the stack trace:
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.read(SocketInputStream.java:129)
at java.io.BufferedInputStream.fill(BufferedInputStream.java:183)
at java.io.BufferedInputStream.read(BufferedInputStream.java:201)
- locked <0xe7bf4cf0> (a java.io.BufferedInputStream)
at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:166)
at
org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:127)
- locked <0xe7bf2c10> (a org.postgresql.core.PGStream)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:100)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43)
at
org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:517)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:50)
at
org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:273)
at
org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:259)

Does that mean the postgresql server failed to send the result of the
update ?
Any idea on how to prevent this ?

Any help would be appreciated,

Nicolas Modrzyk,

On Tue, 30 Nov 2004, Kris Jurka wrote:

> Yes, this looks like a driver bug, but I don't see an easy way to get
> around it. The problem arises from the fact that you are using a
> timestamp without time zone and the 8.0 driver using the V3 protocol types
> all java.sql.Timestamp objects as timestamp with time zone.

The solution to this requires that the data sent to the server is already
in the server's timezone, so that when it converts to the server's
timezone nothing happens. There are two ways to make this
happen, for the driver to be aware of the server's timezone and
modify the data to match before sending, or to force the server's
timezone to be that of the client.

The first approach is hard because it is difficult to ascertain the
server's timezone (pre 8.0). In 7.4 SHOW timezone; usually gives
"unknown". You could try doing things like

SELECT now() - now() AT TIME ZONE 'UTC';

but that only gives you the offset on the current date, not an arbitrary
date.

The second approach forces the server timezone to be the JVM's via the
pseudo sql/java:

SET TIMEZONE = java.util.TimeZone.getDefault().getID();

This is a little more intrusive on the server side, but it should not be
visible using standard JDBC calls for date and time information. The
attached patch implements this and I plan to apply it unless someone has a
better idea or a reason it won't work.

Kris Jurka

Kris Jurka <books(at)ejurka(dot)com> writes:
> The second approach forces the server timezone to be the JVM's via the
> pseudo sql/java:

> SET TIMEZONE = java.util.TimeZone.getDefault().getID();

> This is a little more intrusive on the server side, but it should not be
> visible using standard JDBC calls for date and time information. The
> attached patch implements this and I plan to apply it unless someone has a
> better idea or a reason it won't work.

The really serious problem with this is the assumption that the server
has the same set of available timezones (and spellings of same) as the
client does.

I'm not sure that there is a better alternative, mind you. Just
pointing out that the emperor doesn't really have any clothes on.

In 8.0 and later you can presume that the server is following the
zic database's set of timezone names, but I dunno whether that has
anything to do with the Java universe. Pre-8.0, you'd be foolish
to assume much of anything.

It's not quite too late to make TimeZone be one of the GUC_REPORT
settings in 8.0, if that would help your problem.

regards, tom lane

On Sat, 18 Dec 2004, Tom Lane wrote:

> The really serious problem with this is the assumption that the server
> has the same set of available timezones (and spellings of same) as the
> client does.

I did some testing here and found of the 558 available timezones (in JDK
1.4) that 526 were accepted on 7.4 and 527 were accepted on 8.0, this is
on an unstable debian box, but it seemed like a pretty good percent.

> In 8.0 and later you can presume that the server is following the
> zic database's set of timezone names, but I dunno whether that has
> anything to do with the Java universe. Pre-8.0, you'd be foolish
> to assume much of anything.
>
> It's not quite too late to make TimeZone be one of the GUC_REPORT
> settings in 8.0, if that would help your problem.
>

Well this just kind of reverses the problem, now Java must have the same
set of timezones as the zic database, but I think making TimeZone a
GUC_REPORT is a good idea because we may want to go that route down the
line and now we certainly want to detect if someone has manually set the
TimeZone behind our backs.

Kris Jurka

> The really serious problem with this is the assumption that the server
> has the same set of available timezones (and spellings of same) as the
> client does.

timezones seriously suck.

China uses CST. So does North America. How you gonna resolve THAT ?

The proper solution is to use "ISO" time with -07 / +08 kinda timezones if you
need to get data in/out of timestampz fields.... Java can do this just by
specifying the right option when you convert a calendar time to a string.

On Fri, 17 Dec 2004, John R Pierce wrote:

> > The really serious problem with this is the assumption that the server
> > has the same set of available timezones (and spellings of same) as the
> > client does.
>
> timezones seriously suck.
>
> China uses CST. So does North America. How you gonna resolve THAT ?

Java (and pg) timezones aren't just abbreviations. Mine is
"America/Los_Angeles" which is pretty unique.

> The proper solution is to use "ISO" time with -07 / +08 kinda timezones
> if you need to get data in/out of timestampz fields.... Java can do
> this just by specifying the right option when you convert a calendar
> time to a string.
>

When sending timestamp information to/from the database this is indeed
what is used, but this thread is about resolving the fact that Java's
concept of timestamp is halfway between with timezone and without timezone
which is tough to match up with pg and the sql standard.

Kris Jurka

Kris Jurka <books(at)ejurka(dot)com> writes:
> On Sat, 18 Dec 2004, Tom Lane wrote:
>> It's not quite too late to make TimeZone be one of the GUC_REPORT
>> settings in 8.0, if that would help your problem.

> Well this just kind of reverses the problem, now Java must have the same
> set of timezones as the zic database, but I think making TimeZone a
> GUC_REPORT is a good idea because we may want to go that route down the
> line and now we certainly want to detect if someone has manually set the
> TimeZone behind our backs.

OK, done for 8.0.

regards, tom lane