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
