Thread: Loss of significant digits on pg_dump

Loss of significant digits on pg_dump

From
pgsql-bugs@postgresql.org
Date:
Patricia Holben (pholben@greatbridge.com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Loss of significant digits on pg_dump

Long Description
This has been noticed on 7.0.3 and all betas/releases of 7.1.  When data is stored as a timestamp(see examples) and
thenis dumped and reloaded, display and math operations present different results before vice after the dump. . 

Sample Code
A more complete example can be generated by running the regression tests, dumping the db, dropping the db, creating the
db,loading the dumpfile,use psql to go into the database, set datestyle = 'Postgres,Noneuropean'; select d1 from
timestamp_tbl.
-----------------
short example:

test=# create table timestamp_tbl (d1 timestamp);
CREATE
test=# insert into timestamp_tbl values ('Mon Feb 10 17:32:01.000001 1997 PST');
INSERT 1305206 1
test=# select d1 from timestamp_tbl;
            d1
---------------------------
 1997-02-10 20:32:01.00-05
(1row)

test=# set datestyle = 'Postgres,NonEuropean';

SET VARIABLE
test=# select d1 from timestamp_tbl;
               d1
---------------------------------
 Mon Feb 10 20:32:01.00 1997 EST
(1 row)

test=# insert into timestamp_tbl values ('Mon Feb 10 17:32:01.999999 1997 PST');
INSERT 1305207 1
test=# select d1 from timestamp_tbl;
               d1
---------------------------------
 Mon Feb 10 20:32:01.00 1997 EST
 Mon Feb 10 20:32:02.00 1997 EST
(2rows)

----   now dump the db, drop, recreate, reload the data

test=# select d1 from timestamp_tbl;
           d1
------------------------
 1997-02-10 20:32:01-05
 1997-02-10 20:32:02-05
(2rows)

test=# set datestyle = 'Postgres,NonEuropean';
SET VARIABLE
test=# select d1 from timestamp_tbl;
              d1
------------------------------
 Mon Feb 10 20:32:01 1997 EST
 Mon Feb 10 20:32:02 1997 EST
(2 rows)

-- now we insert 2 rows using identical inserts as above
test=# insert into timestamp_tbl values ('Mon Feb 10 17:32:01.000001 1997 PST');
INSERT 1797129 1
test=# insert into timestamp_tbl values ('Mon Feb 10 17:32:01.999999 1997 PST');
INSERT 1797130 1

-- now we expect the following math option to work the same on the pre-dump and after-dump entries
test=# SELECT '' AS "53", date_part( 'year', d1) AS year, date_part( 'month', d1) AS month,
test-# date_part( 'day', d1) AS day, date_part( 'hour', d1) AS hour,
test-# date_part( 'minute', d1) AS minute, date_part( 'second', d1) AS second
test-# FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
 53 | year | month | day | hour | minute |  second
----+------+-------+-----+------+--------+----------
    | 1997 |     2 |  10 |   20 |     32 |        1
    | 1997 |     2 |  10 |   20 |     32 |        2
    | 1997 |     2 |  10 |   20 |     32 | 1.000001
    | 1997 |     2 |  10 |   20 |     32 | 1.999999
(4 rows)


No file was uploaded with this report