Skip site navigation (1) Skip section navigation (2)

inability to restore infinity values via psql or pg_restore

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: inability to restore infinity values via psql or pg_restore
Date: 2001-01-23 22:36:46
Message-ID: 200101232236.f0NMakb47106@hub.org (view raw or flat)
Thread:
Lists: pgsql-bugs
Thomas F. OConnell (tfo(at)monsterlabs(dot)com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
inability to restore infinity values via psql or pg_restore

Long Description
i am currently attempting to use infinite interval values in various tables in my database. unfortunately, any use of the copy command involving rows with infinite values fail.

this bug is a problem because it suggests that daily dumps of the database cannot be restored using psql -f or pg_restore (regardless of dump format) if there are any tables that contain infinite values.

Sample Code
here is the exact sequence of steps required to produce my error:

postgres]$ createdb foo
postgres]$ psql foo
foo=# create table foo ( foo interval );
foo=# insert into foo( foo ) values( timestamp 'infinity' - timestamp '-infinity' );
foo=# \q
postgres]$ pg_dump -t foo foo >foo.sql
postgres]$ psql -c 'drop table foo' foo
postgres]$ psql -f foo.sql foo

---------------------------------------------------------------------------

output i got:

CREATE
UPDATE 1
psql:foo.sql:21: ERROR:  copy: line 1, Bad interval external representation '-2147483648
+--2147483648:-2147483648:  inf'
psql:foo.sql:21: PQendcopy: resetting connection
BEGIN
CREATE
INSERT 0 0
UPDATE 0
DROP
COMMIT

---------------------------------------------------------------------------

output i expected:

UPDATE 1
BEGIN
CREATE
INSERT 0 0
UPDATE 0
DROP
COMMIT

---------------------------------------------------------------------------

i compiled with all defaults except the install directory:
./configure  --prefix=/opt/pgsql

we use the startup script from the documentation.
it starts the database like this from rc.3:

PGACCOUNT="postgres"
POSTMASTER="postmaster"
FACILITY="local5"
PGLOGFILE="/tmp/postgres.log"
PGOPTS="-i -d 0"
. /etc/rc.d/init.d/functions
. /etc/sysconfig/network
su - ${PGACCOUNT} -c "(${POSTMASTER} ${PGOPTS} 2>&1 | logger -p
${FACILITY}.notice) &" > /dev/null 2>&1 &

the bug can be reproduced in both

PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66

and

PostgreSQL 7.1beta2 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66

---------------------------------------------------------------------------

platform info:

RedHat Linux 2.2.17 i686 unknown
gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release)
256 MB RAM

No file was uploaded with this report


Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2001-01-23 22:57:14
Subject: Re: inability to restore infinity values via psql or pg_restore
Previous:From: tfoDate: 2001-01-23 20:42:58
Subject: inability to restore infinity values via psql or pg_restore

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group