Обсуждение: 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.Stejskal@ppi.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.Stejskal@ppi.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@postgresql.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@fastcrypt.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@fastcrypt.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@ejurka.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
I haven't been closely following this thread so I may be completely off base here. But wouldn't having both java and the server using the same known timezone have the same effect? So if you were to set the timezone to GMT in both the client and server, timestamps could then be passed correctly. --Barry -----Original Message----- From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Kris Jurka Sent: Friday, December 17, 2004 9:16 PM To: Antje.Stejskal@ppi.de Cc: pgsql-jdbc@postgresql.org Subject: Re: [JDBC] Bug in JDBC-Driver? 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@ejurka.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
On Mon, 20 Dec 2004, Barry Lind wrote: > I haven't been closely following this thread so I may be completely off > base here. But wouldn't having both java and the server using the same > known timezone have the same effect? So if you were to set the timezone > to GMT in both the client and server, timestamps could then be passed > correctly. > I initially thought this was a great idea, but it breaks down when they don't use a PreparedStatement and generate a query directly, then we can't intervene and adjust things. The advantage of setting the server to the client's timezone is that this will work as expected. Kris Jurka
OK. But IMHO, if you are using timestamps outside of PreparedStatements you are asking for trouble :-). Since you pointed out earlier that most of the timezones match between java and the server, you could first attempt to use the server's timezone, and if that doesn't work you could fall back to this method of using GMT on both client and server. --Barry -----Original Message----- From: Kris Jurka [mailto:books@ejurka.com] Sent: Wednesday, December 22, 2004 3:27 AM To: Barry Lind Cc: Antje.Stejskal@ppi.de; pgsql-jdbc@postgresql.org Subject: Re: [JDBC] Bug in JDBC-Driver? On Mon, 20 Dec 2004, Barry Lind wrote: > I haven't been closely following this thread so I may be completely off > base here. But wouldn't having both java and the server using the same > known timezone have the same effect? So if you were to set the timezone > to GMT in both the client and server, timestamps could then be passed > correctly. > I initially thought this was a great idea, but it breaks down when they don't use a PreparedStatement and generate a query directly, then we can't intervene and adjust things. The advantage of setting the server to the client's timezone is that this will work as expected. Kris Jurka
On Wed, 22 Dec 2004, Barry Lind wrote: > OK. But IMHO, if you are using timestamps outside of PreparedStatements > you are asking for trouble :-). Since you pointed out earlier that most > of the timezones match between java and the server, you could first > attempt to use the server's timezone, and if that doesn't work you could > fall back to this method of using GMT on both client and server. > My concern is that we don't break things for the situation where the client and server are already in the same timezone which is 99.9% of the time. Kris Jurka
Kris Jurka wrote: > I initially thought this was a great idea, but it breaks down when they > don't use a PreparedStatement and generate a query directly, then we can't > intervene and adjust things. The advantage of setting the server to the > client's timezone is that this will work as expected. What about the {ts ...} etc escapes for time/date values? Aren't you meant to use those for the case where you have literal date values in the query? -O