Обсуждение: Problems with infinity
Hi, I'm using PostgreSQL 7.4.6 with pg74.215.jdbc3.jar and have trouble with infinite timestamp values. org.postgres relevant Trace: Caused by: org.postgresql.util.PSQLException: Bad Timestamp Format at 0 in 9999-01-01 at org.postgresql.jdbc1.AbstractJdbc1ResultSet.toTimestamp(AbstractJdbc1ResultSet.java:1188) at org.postgresql.jdbc1.AbstractJdbc1ResultSet.getTimestamp(AbstractJdbc1ResultSet.java:381) at org.postgresql.jdbc1.AbstractJdbc1ResultSet.getTimestamp(AbstractJdbc1ResultSet.java:541) Furthermore, in your TODO list I read "The conversion to/from datatypes supporting infinity is silly because Java doesn't have a similar concept. Throw an exception instead.". That's really bad. I can't change the database content because several stored procedures are based on infinite timestamp values. Of course I understand that conversion infinite from/to 9999-01-01 or 0001-01-01 is silly...but its better than being forced to use another language to talk to the database, isn't it? Cheers, Oliver
On Thu, 13 Jan 2005, Oliver Siegmar wrote: > I'm using PostgreSQL 7.4.6 with pg74.215.jdbc3.jar and have trouble with > infinite timestamp values. > > Caused by: org.postgresql.util.PSQLException: Bad Timestamp Format at 0 in > 9999-01-01 This is indeed a bug in the 7.4 drivers. > Furthermore, in your TODO list I read "The conversion to/from datatypes > supporting infinity is silly because Java doesn't have a similar concept. > Throw an exception instead.". Actually the fact that the 7.4 drivers don't even work and no one has ever complained previously is a pretty strong endorsement of this todo item (which has already been done in the 8.0 codebase.) > That's really bad. I can't change the database content because several stored > procedures are based on infinite timestamp values. > You may use getString() on the column and check for infinity yourself. Using setString(x, "Infinity") will work with the 7.4 drivers, but not 8.0. Perhaps we could make setObject(x, "Infinity", Types.TIMESTAMP) accomodate this though... Kris Jurka
On Thursday 13 January 2005 14:42, Kris Jurka wrote: > On Thu, 13 Jan 2005, Oliver Siegmar wrote: > > Caused by: org.postgresql.util.PSQLException: Bad Timestamp Format at 0 > > in 9999-01-01 > > This is indeed a bug in the 7.4 drivers. Will you fix it? ;-) > You may use getString() on the column and check for infinity yourself. > Using setString(x, "Infinity") will work with the 7.4 drivers, but not > 8.0. Perhaps we could make setObject(x, "Infinity", Types.TIMESTAMP) > accomodate this though... I'm using Hibernate for getting/setting data - I don't access the data directly. Dropping infinity support, means I cannot work with Hibernate on databases with infinite timestamps :-(( Couldn't you make it configurable (like 'convert_infinite_to_min_max = true')? Cheers, Oliver -- - Technik - www.vitrado.de Fax: +49 (0)89 - 62 44 77 - 39 vitrado GmbH Grillparzerstrasse 3 81675 München ______________________________________ Geschäftsführer: Thorsten Meier, Martin Plöckl Amtsgericht München, HRB 13119
On Thu, 13 Jan 2005, Oliver Siegmar wrote: > On Thursday 13 January 2005 14:42, Kris Jurka wrote: > > > > This is indeed a bug in the 7.4 drivers. > > Will you fix it? ;-) Sure, but you should be aware that the driver only does this conversion on output (rs.getTimestamp()). No conversion is done for setTimestamp, so it's a one way solution. This was part of my reasoning for removing it. > Couldn't you make it configurable (like 'convert_infinite_to_min_max = true')? > That could be done, but how do you define min/max. There are at least three possible definitions I can think of: Java's min/max value for Timestamp and pg's min/max, but pg's depends on how it was compiled (--enable-integer-datetimes). So how do you pick which one? You've got to pick the one with the smallest range, so it fits inside the other's range, which kind of sucks because then you have a special magic value that isn't even at the extremes of the type range. The user level code to manipulate such a thing will certainly be ugly. Kris Jurka
On Thu, 13 Jan 2005, Oliver Siegmar wrote: > > This is indeed a bug in the 7.4 drivers. > > Will you fix it? ;-) > Fixed in cvs. Prebuilt jars available here: http://www.ejurka.com/pgsql/jars/OliverSiegmar/ Kris Jurka
On Thursday 13 January 2005 15:45, Kris Jurka wrote: > On Thu, 13 Jan 2005, Oliver Siegmar wrote: > > > This is indeed a bug in the 7.4 drivers. > > > > Will you fix it? ;-) > > Fixed in cvs. Prebuilt jars available here: > > http://www.ejurka.com/pgsql/jars/OliverSiegmar/ Thanks a lot. It works. By the way...the pg80b1.308.jdbc3 driver response with Caused by: org.postgresql.util.PSQLException: The timestamp given 9999-01- GMT-01-01 does not match the format required: java.text.SimpleDateFormat@3459f7da. I know, you said that this driver won't support infinite convesion...but this exception message is a bit weird, isn't it? Cheers, Oliver
On Thursday 13 January 2005 15:18, Kris Jurka wrote: > On Thu, 13 Jan 2005, Oliver Siegmar wrote: > > On Thursday 13 January 2005 14:42, Kris Jurka wrote: > > > This is indeed a bug in the 7.4 drivers. > > > > Will you fix it? ;-) > > Sure, but you should be aware that the driver only does this conversion on > output (rs.getTimestamp()). No conversion is done for setTimestamp, so > it's a one way solution. This was part of my reasoning for removing it. Of course it should work in both directions. > > Couldn't you make it configurable (like 'convert_infinite_to_min_max = > > true')? > > That could be done, but how do you define min/max. There are at least > three possible definitions I can think of: Java's min/max value for > Timestamp and pg's min/max, but pg's depends on how it was compiled > (--enable-integer-datetimes). So how do you pick which one? You've got > to pick the one with the smallest range, so it fits inside the other's > range, which kind of sucks because then you have a special magic value > that isn't even at the extremes of the type range. The user level > code to manipulate such a thing will certainly be ugly. Well, I'd set Long.MIN_VALUE / Long.MAX_VALUE to the Timestamp - the pgsql driver could simply convert this to '-infinity' / 'infinity' strings, not? This could be done for both directions. I agree, that these are special magic values but its better than someone can break down my java applications by inserting a infinity value to a timestamp field. What do you think? Cheers, Oliver
On Fri, 14 Jan 2005, Oliver Siegmar wrote: > By the way...the pg80b1.308.jdbc3 driver response with > > Caused by: org.postgresql.util.PSQLException: The timestamp given 9999-01- > GMT-01-01 does not match the format required: > java.text.SimpleDateFormat@3459f7da. > The cvs version responds with: Exception in thread "main" org.postgresql.util.PSQLException: Infinite value found for timestamp. Java has no corresponding representation. Kris Jurka
On Fri, 14 Jan 2005, Oliver Siegmar wrote: > Well, I'd set Long.MIN_VALUE / Long.MAX_VALUE to the Timestamp - the pgsql > driver could simply convert this to '-infinity' / 'infinity' strings, not? > This could be done for both directions. > Ahh, of course! The pg timestamp range is completely irrelevent to this discussion because we are using infinity. That does sound workable. I'll put it on the todo, but I personally don't think it's very high priority. Kris Jurka
On Friday 14 January 2005 09:05, Kris Jurka wrote: > On Fri, 14 Jan 2005, Oliver Siegmar wrote: > > Well, I'd set Long.MIN_VALUE / Long.MAX_VALUE to the Timestamp - the > > pgsql driver could simply convert this to '-infinity' / 'infinity' > > strings, not? This could be done for both directions. > > Ahh, of course! The pg timestamp range is completely irrelevent to this > discussion because we are using infinity. That does sound workable. I'll > put it on the todo, but I personally don't think it's very high priority. Well...it has high priority for me, so drop it from your todo and apply my patch ;-)) I tested it and it works well here. I tried to use your coding style, hope everything is well. Maybe performance could be tweaked a bit by externalising "infinity" and "-infinity" strings to a final static, but I think java handles this properly with its internal string pool. I also removed the now obsolete exception message from the .po files - so the line numbers have to be renumbered (don't know how to do). Of course it has to be well tested. I don't know how it will perform on other PostgreSQL versions other than 7.4. I hope I haven't created any bad side effects. Cheers, Oliver
Вложения
On Fri, 14 Jan 2005, Oliver Siegmar wrote: > Well...it has high priority for me, so drop it from your todo and apply my > patch ;-)) The beauty of open source at work. > I tested it and it works well here. I tried to use your coding style, hope > everything is well. Maybe performance could be tweaked a bit by externalising > "infinity" and "-infinity" strings to a final static, but I think java > handles this properly with its internal string pool. This looks alright for timestamp, but I don't think this is the right thing to do for date and time. The result of getDate should have no hour, minute, and seconds component. Likewise for getTime the date components should be set to the unix zero epoch. This means that setDate and setTime can't really take infinite values (at least how you're proposing), but that's not a real issue because pg's date and time types don't support infinity. Do you think it is important to support these or is timestamp alone enough? > I also removed the now obsolete exception message from the .po files - so the > line numbers have to be renumbered (don't know how to do). Don't worry about these. This process is handled automatically by gettext. The only people who touch these are translators. > Of course it has to be well tested. I don't know how it will perform on other > PostgreSQL versions other than 7.4. I hope I haven't created any bad side > effects. The way to make sure this works (both now and in the future) is to add a test or two to the test suite. See org/postgresql/test/ and http://jdbc.postgresql.org/development/intro.html#Test+Suite Kris Jurka
On Friday 14 January 2005 13:29, Kris Jurka wrote: > This looks alright for timestamp, but I don't think this is the right > thing to do for date and time. The result of getDate should have no hour, You're right. I thought of java datatypes instead of Postgres datatypes. The conversion is only needed for timestamp, of course. > The way to make sure this works (both now and in the future) is to add a > test or two to the test suite. See org/postgresql/test/ and > http://jdbc.postgresql.org/development/intro.html#Test+Suite I added tests to the main method within TimestampUtils, but I can't run the test. A simple 'ant test' runs flawlessly without any errors, but it doesn't do anything - Buildfile: build.xml prepare: check_versions: check_driver: driver: compile: examples: jar: testjar: runtest: test: BUILD SUCCESSFUL Total time: 1 second ...but no database operations were performed. Oliver
Вложения
On Fri, 14 Jan 2005, Oliver Siegmar wrote: > I added tests to the main method within TimestampUtils, but I can't run the > test. A simple 'ant test' runs flawlessly without any errors, but it doesn't > do anything - > The main method in TimestampUtils is a one off and is not part of the main test suite. Consider adding something to org/postgresql/test/jdbc2/TimestampTest.java. The test suite does not run because you have do not have junit (http://junit.org) installed and available in your classpath. Kris Jurka
Kris Jurka <books@ejurka.com> writes: > This looks alright for timestamp, but I don't think this is the right > thing to do for date and time. The result of getDate should have no hour, > minute, and seconds component. Likewise for getTime the date components > should be set to the unix zero epoch. This means that setDate and setTime > can't really take infinite values (at least how you're proposing), but > that's not a real issue because pg's date and time types don't support > infinity. Do you think it is important to support these or is timestamp > alone enough? There is a TODO item to allow +infinity and -infinity in the DATE datatype. So I'd recommend making sure you handle that case, even if it can't occur today. Don't recall anyone having ever asked for infinity in the TIME type. regards, tom lane
On Friday 14 January 2005 14:03, Kris Jurka wrote: > On Fri, 14 Jan 2005, Oliver Siegmar wrote: > The main method in TimestampUtils is a one off and is not part of the main > test suite. Consider adding something to > org/postgresql/test/jdbc2/TimestampTest.java. The test suite does not run > because you have do not have junit (http://junit.org) installed and > available in your classpath. Right...I hadn't it in my classpath. Tests are attached to this mail - I'm not sure if they're enough. Will there be new 8.0 beta drivers with my patches included, soon? Cheers, Oliver
Вложения
On Fri, 14 Jan 2005, Oliver Siegmar wrote: > Will there be new 8.0 beta drivers with my patches included, soon? > I'm planning on putting out the official release sometime this weekend, but I don't think your patch(es) are quite ready yet. As I mentioned on the original one, returning LONG.MAX_VALUE based data for getDate and getTime is not a valid thing to do. So that should be removed. With that gone it will break the tests you sent in, so those will need to get updated. I was also thinking about getTimestamp(int, Calendar). I think this also neeeds some special casing for the infinite value otherwise you could wraparound the long value's range when adjusting the timezone. Kris Jurka
On Mon, 17 Jan 2005, Oliver Siegmar wrote: > Okay...then we cannot use Long.MAX_VALUE and Long.MIN_VALUE. I've > searched for the nearest values to MAX/MIN which have > hour/minute/second/millisecond set to 0. These are static finals within > TimstampUtils now. Hope that's okay. > > For consistently you have to use it for Date _AND_ Timestamp. > OK, this patch looks good and complete, but I want to discuss this design decision a little more, we seem to have lost the mailling list cc: in this discussion, so I'm readding and summarizing. The issue is that pg timestamps support values of +/- infinity which have no equivalent in Java. There was some code that tried to map these values to pretty arbitrary date values which didn't really work and I removed that and made the code throw an Exception when trying to call getTimestamp on such a value. Oliver Siegmar has found the need to use infinte values in the database and access them from jdbc and has proposed readding the mapping functionality and completing support for it. The main issue is what value to map to, the original discussion for timestamps was to use Long.MIN_VALUE / Long.MAX_VALUE when constructing Timestamp objects. I personally liked this idea, but then it starts to get complicated when considering the date datatype as well. Tom Lane weighed in noting that while the pg date datatype does not currently support infinite values, but that is on the todo. Oliver Siegmar also wanted to be able to call getDate() on an infinite value timestams, but Long.MAX_VALUE is not a valid java.sql.Date value because it has non-zero hour/minute/second components. He proposed using different constants in org.postgresql.jdbc2.TimestampUtils instead that did have the zero hour/minute/second properties. I personally like the "cleanliness" of Long.MAX_VALUE as it doesn't require the pg jdbc driver to compile and just not supporting infinite date values. I don't feel that the backend todo is going to be completed any time soon. The counter argument is that eventually the todo item will be done and that the supposed "cleanliness" is just an illusion because no other database is going to allow you store a timestamp of Long.MAX_VALUE so there is already an implicit assumption of using pg, why not make it explicit? Perhaps TimestampUtils isn't the place to put an end user visible constant, but it should somewhere in the pg driver. Thoughts? Kris Jurka
On Tuesday 18 January 2005 01:50, Kris Jurka wrote: > The counter argument is that eventually the todo item will be done and > that the supposed "cleanliness" is just an illusion because no other > database is going to allow you store a timestamp of Long.MAX_VALUE so > there is already an implicit assumption of using pg, why not make it > explicit? I did some research to find other databases / jdbc drivers that are able to set infinity values for timestamps. Unfortunately I haven't found one. Oracle seems to support infinity values but I couldn't find any driver implementation details. Does someone here has kwnoledge about different jdbc implementations and their infinity usage? The current infinity implementation in pgjdbc is a hack, because you have to set/get magic values that represents '-infinity' and 'infinity', but it is imho the best way to do until there might be an NEGATIVE_INFINITY and POSITIVE_INFINTIY for java.util.Date like the one for java.lang.Double. Oliver
On Tue, 18 Jan 2005, Oliver Siegmar wrote: > The current infinity implementation in pgjdbc is a hack, because you have to > set/get magic values that represents '-infinity' and 'infinity', but it is > imho the best way to do until there might be an NEGATIVE_INFINITY and > POSITIVE_INFINTIY for java.util.Date like the one for java.lang.Double. > Well, no one seems to have any better ideas, how about a better place for the infinity constants. org.postgresql.jdbc2.TimestampUtils is not a public interface. PGStatement? http://jdbc.postgresql.org/documentation/publicapi/index.html Kris Jurka
On Friday 21 January 2005 09:06, Kris Jurka wrote: > On Tue, 18 Jan 2005, Oliver Siegmar wrote: > > The current infinity implementation in pgjdbc is a hack, because you have > > to set/get magic values that represents '-infinity' and 'infinity', but > > it is imho the best way to do until there might be an NEGATIVE_INFINITY > > and POSITIVE_INFINTIY for java.util.Date like the one for > > java.lang.Double. > > Well, no one seems to have any better ideas, how about a better place for > the infinity constants. org.postgresql.jdbc2.TimestampUtils is not a > public interface. PGStatement? I vote for PGStatement :-) Oliver
On Fri, 21 Jan 2005, Oliver Siegmar wrote: > > Well, no one seems to have any better ideas, how about a better place for > > the infinity constants. org.postgresql.jdbc2.TimestampUtils is not a > > public interface. PGStatement? > > I vote for PGStatement :-) > I was getting ready to apply this patch when I decided to add a couple more tests and I'm getting a failure for getDate() that I do not have time at the momemnt to investigate. Could you take a look at the attached patch and see what's going on. Kris Jurka
Вложения
On Tuesday 25 January 2005 09:35, Kris Jurka wrote: > I was getting ready to apply this patch when I decided to add a couple > more tests and I'm getting a failure for getDate() that I do not have time > at the momemnt to investigate. Could you take a look at the attached > patch and see what's going on. I applied the patch and run the tests. No problems were detected. I'm willing to help, but a little more information would be very helpful :) Especially what error occured with getDate()? Oliver
On Tue, 25 Jan 2005, Oliver Siegmar wrote: > On Tuesday 25 January 2005 09:35, Kris Jurka wrote: > > I was getting ready to apply this patch when I decided to add a couple > > more tests and I'm getting a failure for getDate() that I do not have time > > at the momemnt to investigate. Could you take a look at the attached > > patch and see what's going on. > > I applied the patch and run the tests. No problems were detected. I'm willing > to help, but a little more information would be very helpful :) > runtest: [junit] Testsuite: org.postgresql.test.jdbc2.Jdbc2TestSuite [junit] Tests run: 176, Failures: 1, Errors: 0, Time elapsed: 151.39 sec [junit] Testcase: testInfinity(org.postgresql.test.jdbc2.TimestampTest): FAILED [junit] expected:<9223372036825200000> but was:<9223372036767600000> [junit] junit.framework.AssertionFailedError: expected:<9223372036825200000> but was:<9223372036767600000> [junit] at org.postgresql.test.jdbc2.TimestampTest.runInfinityTests(TimestampTest.java:93) [junit] at org.postgresql.test.jdbc2.TimestampTest.testInfinity(TimestampTest.java:53) [junit] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [junit] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) [junit] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) So it appears the constant you have selected only works as a date for your timezone. Kris Jurka
The last weeks were rather busy, so I couldn't do any tests...but I tried today (with the current CVS version). To track down the problem (infinity in another timezone) I changed the timezone of my PgSQL server from 'CET' to 'EST'. Even without the infinity-patch applied I get a test-exception: runtest: [junit] Testsuite: org.postgresql.test.jdbc2.Jdbc2TestSuite [junit] Tests run: 180, Failures: 2, Errors: 0, Time elapsed: 12.149 sec [junit] Testcase: testGetTimestampWTZ(org.postgresql.test.jdbc2.TimestampTest): FAILED [junit] expected:<-627872399900> but was:<-627847199900> [junit] junit.framework.AssertionFailedError: expected:<-627872399900> but was:<-627847199900> [junit] at org.postgresql.test.jdbc2.TimestampTest.timestampTestWTZ(TimestampTest.java:306) [junit] at org.postgresql.test.jdbc2.TimestampTest.testGetTimestampWTZ(TimestampTest.java:78) [junit] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [junit] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) [junit] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) [junit] Testcase: testSetTimestampWOTZ(org.postgresql.test.jdbc2.TimestampTest): FAILED [junit] null [junit] junit.framework.AssertionFailedError [junit] at org.postgresql.test.jdbc2.TimestampTest.timestampTestWOTZ(TimestampTest.java:367) [junit] at org.postgresql.test.jdbc2.TimestampTest.testSetTimestampWOTZ(TimestampTest.java:259) [junit] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [junit] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) [junit] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) [junit] Test org.postgresql.test.jdbc2.Jdbc2TestSuite FAILED [junit] Testsuite: org.postgresql.test.jdbc2.optional.OptionalTestSuite [junit] Tests run: 38, Failures: 0, Errors: 0, Time elapsed: 1.561 sec [junit] Testsuite: org.postgresql.test.jdbc3.Jdbc3TestSuite [junit] Tests run: 15, Failures: 0, Errors: 0, Time elapsed: 0.436 sec This needs to be fixed before I can track down the infinity problem. Greets Oliver
On Fri, 4 Feb 2005, Oliver Siegmar wrote: > To track down the problem (infinity in another timezone) I changed the > timezone of my PgSQL server from 'CET' to 'EST'. Even without the > infinity-patch applied I get a test-exception: > > testGetTimestampWTZ(org.postgresql.test.jdbc2.TimestampTest): FAILED > [junit] expected:<-627872399900> but was:<-627847199900> This is a known problem when you have timestamp without time zone and the server and client timezone is different. If you set your client timezone to EST as well it should go away. http://archives.postgresql.org/pgsql-jdbc/2004-12/msg00137.php Kris Jurka
On Tuesday 25 January 2005 09:35, Kris Jurka wrote: > On Fri, 21 Jan 2005, Oliver Siegmar wrote: > > > Well, no one seems to have any better ideas, how about a better place > > > for the infinity constants. org.postgresql.jdbc2.TimestampUtils is not > > > a public interface. PGStatement? > > > > I vote for PGStatement :-) > > I was getting ready to apply this patch when I decided to add a couple > more tests and I'm getting a failure for getDate() that I do not have time > at the momemnt to investigate. Could you take a look at the attached > patch and see what's going on. Attached is a new patch (diffed against current CVS) which fullfills the "requirements" of all junit tests. I've tested timezones 'Europe/Berlin' and 'America/Los_Angeles'. Anyway I'm not 100% sure if this is a good way handling this. Oliver
Вложения
On Fri, 4 Feb 2005, Oliver Siegmar wrote: > Attached is a new patch (diffed against current CVS) which fullfills the > "requirements" of all junit tests. > > I've tested timezones 'Europe/Berlin' and 'America/Los_Angeles'. > Applied to 8.0 and HEAD. Thanks. Kris Jurka