Thread

1. inability to restore infinity values via psql or pg_restore

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2001-01-23T22:36:46Z

   Thomas F. OConnell (tfo@monsterlabs.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
   
   
   

2. Re: inability to restore infinity values via psql or pg_restore

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-01-23T22:57:14Z

   pgsql-bugs@postgresql.org writes:
   > inability to restore infinity values via psql or pg_restore
   
   I don't think this has anything to do with a dump/restore problem.
   What it is is a problem with dealing with overflow in timestamp
   calculations.
   
   In current sources:
   
   regression=# select timestamp 'infinity' - timestamp '-infinity';
                     ?column?
   ---------------------------------------------
    2147483647 days 2147483647:2147483647:00inf
   (1 row)
   
   AFAICT we don't have a concept of "infinity" for intervals, so this
   should raise an error instead of returning a bogus value.
   
   			regards, tom lane