--Apple-Mail-24-342511061
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=US-ASCII;
format=flowed

Just a followup:

the reason for this misbehaviour was an invalid timestamp value.
I've tried to dump/restore the db and the restore choked on a
"incorrect timestamp" namely:
4714-11--2147483624 -2147483648:-2147483648:-2147483648 BC
(out of the dump file)

After I've elimnated the lines containing this value, and successfully
restoring the db, the transcript below worked fine (w/o/ problems)

Don't know how the values got there in the first place (everything in
the db was
created through a java app through JDBC)

Christian
P.S.: The db was created with 7.2.3 and then upgraded to 7.3.3 (now
7.3.4)


On Thursday, August 7, 2003, at 08:52 AM, Christian van der Leeden
wrote:

Christian van der Leeden
Logic United GmbH
Tel: 089-189488-66 Mob: 0163-3747111
www.logicunited.com

--Apple-Mail-24-342511061
Content-Disposition: attachment;
filename="Christian van der Leeden.vcf"
Content-Transfer-Encoding: quoted-printable
Content-Type: text/directory;
x-unix-mode=0644;
name="Christian van der Leeden.vcf"

BEGIN:VCARD=0D
VERSION:3.0=0D
N:van der Leeden;Christian;;;=0D
FN:Christian van der Leeden=0D
ORG:Logic United GmbH;=0D
TITLE:Software Engineering=0D
EMAIL;type=3DHOME;type=3Dpref:leeden (AT) logicunited (DOT) com=0D
EMAIL;type=3DHOME:leeden (AT) samira (DOT) de=0D
TEL;type=3DWORK;type=3Dpref:+49-89-189488-66=0D
TEL;type=3DCELL:+49-163-3747111=0D
ADR;type=3DWORK;type=3Dpref:;;Brecherspitzstrasse 8;Muenchen;;81541;Germany=
=0D
URL:www.logicunited.com=0D
END:VCARD=0D

--Apple-Mail-24-342511061
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=US-ASCII;
format=flowed



--Apple-Mail-24-342511061
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

--Apple-Mail-24-342511061--

Christian van der Leeden <lists (AT) logicunited (DOT) com> writes:
Hmm ... I'm suspecting that that was a "minus infinity" under the hood.
Is your installation built with integer timestamps, or floating point?
(If you're not sure, try "pg_config --configure" and see if it mentions
--enable-integer-datetimes.) Also, is the column in question of type
timestamp, or timestamp with time zone?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

--Apple-Mail-39-359328585
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=US-ASCII;
format=flowed

Hi,

I've put the database dump here:
http://www.vanderleeden.de/test/databasedumps.tar
(about 16 MB)

It contains the ascii dump of pg_dump and the
pg_dump --format c of the database.

I've only got the dumps left of the original problem,
since during my tries to remedy the problem
(upgrade to 7.3.4 with initdb and restore)
the restore failed and I don't have the original
db saved...

The db itself (only speaking for the current 7.3.4 build),
is not configured with enabled-integer-datetimes.
Creation date is defined as:
creation_date | timestamp without time zone | not null

CU

Christian

On Thursday, August 7, 2003, at 04:23 PM, Tom Lane wrote:

Christian van der Leeden
Logic United GmbH
Tel: 089-189488-66 Mob: 0163-3747111
www.logicunited.com

--Apple-Mail-39-359328585
Content-Disposition: attachment;
filename="Christian van der Leeden.vcf"
Content-Transfer-Encoding: quoted-printable
Content-Type: text/directory;
x-unix-mode=0644;
name="Christian van der Leeden.vcf"

BEGIN:VCARD=0D
VERSION:3.0=0D
N:van der Leeden;Christian;;;=0D
FN:Christian van der Leeden=0D
ORG:Logic United GmbH;=0D
TITLE:Software Engineering=0D
EMAIL;type=3DHOME;type=3Dpref:leeden (AT) logicunited (DOT) com=0D
EMAIL;type=3DHOME:leeden (AT) samira (DOT) de=0D
TEL;type=3DWORK;type=3Dpref:+49-89-189488-66=0D
TEL;type=3DCELL:+49-163-3747111=0D
ADR;type=3DWORK;type=3Dpref:;;Brecherspitzstrasse 8;Muenchen;;81541;Germany=
=0D
URL:www.logicunited.com=0D
END:VCARD=0D

--Apple-Mail-39-359328585
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=US-ASCII;
format=flowed



--Apple-Mail-39-359328585
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

--Apple-Mail-39-359328585--

Christian van der Leeden <lists (AT) logicunited (DOT) com> writes:
Okay ... [experiments a bit...] ah-hah, I know what happened. Under the
hood, that value is a NaN. Observe:

-- just to ease experimenting
tsbug=# create cast (float8 as timestamp without time zone) without function;
CREATE CAST

tsbug=# select '1.8'::float8::timestamp;
timestamp
------------------------
2000-01-01 00:00:01.80
(1 row)

tsbug=# select 'NaN'::float8::timestamp;
timestamp
---------------------------------------------------------
4714-11--2147483625 2147483647:2147483647:2147483647 BC
(1 row)

NaNs behave funny in comparisons, which is doubtless what was fouling up
your index. btrees assume that the trichotomy law holds :-(.

I wonder how a NaN got in there? Anyway we probably ought to add some
defenses against it ... at least enough to ensure that timestamp indexes
stay sane.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org