Re: 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(dot)text(dot)SimpleDateFormat(at)3459f7da(dot)

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(dot)text(dot)SimpleDateFormat(at)3459f7da(dot)
>

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

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

Kris Jurka <books(at)ejurka(dot)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 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