Обсуждение: Bug in CAST() with time data types

Your name        :    Alvaro Herrera
Your email address    :    alvherre@protecne.cl


System Configuration
---------------------
  Architecture (example: Intel Pentium)      : AMD Athlon

  Operating System (example: Linux 2.0.26 ELF)     : Linux 2.2.17

  PostgreSQL version (example: PostgreSQL-7.0):   PostgreSQL-7.0.2

  Compiler used (example:  gcc 2.8.0)        : RPM for Mandrake Linux


Please enter a FULL description of your problem:
------------------------------------------------

When inserting timestamps or interval types whose precision is one full
minute, Postgres substracts one minute from the data and add 60 seconds
(00:01:00 becomes 00:00:60). Later, when reading this data Postgres says
"Bad timestamp external representation '2000-12-14 18:25:60.00-03'" and
refuses to understand it.

I gave a look at the list of fixed bugs for 7.0.3, but it isn't listed.

(sorry, Mandrake hasn't provided upgraded packages yet, and I don't want to
 mess around with the installation).



Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

rwatch=> CREATE TABLE test (val1 TIMESTAMP, val2 INTERVAL);
CREATE
rwatch=> INSERT INTO test VALUES (CAST ('2000-12-14 18:25:00.00-03' AS
            TIMESTAMP), CAST ('00:01:00' AS INTERVAL));
INSERT 4400170 1
rwatch=> SELECT * FROM test;
           val1            |   val2
---------------------------+----------
 2000-12-14 18:25:60.00-03 | 00:00:60
        (1 row)

(cutting and pasting the text from the select above):

rwatch=> INSERT INTO test VALUES (CAST ('2000-12-14 18:25:60.00-03' AS
            TIMESTAMP));
ERROR:  Bad timestamp external representation '2000-12-14 18:25:60.00-03'



If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------

Sorry, I don't even know where to look. It is probably a bug in the way
CAST() treats it's arguments, when dealing with time data types.


--
Alvaro Herrera (<alvherre[@]protecne.cl>)

>   Compiler used (example:  gcc 2.8.0)           : RPM for Mandrake Linux
...
> When inserting timestamps or interval types whose precision is one full
> minute, Postgres substracts one minute from the data and add 60 seconds
> (00:01:00 becomes 00:00:60). Later, when reading this data Postgres says
> "Bad timestamp external representation '2000-12-14 18:25:60.00-03'" and
> refuses to understand it.
...
> Sorry, I don't even know where to look. It is probably a bug in the way
> CAST() treats it's arguments, when dealing with time data types.

No, unfortunately it is a bug in the way Mandrake builds their RPMs :(

The default compiler flags for RPM building under Mandrake include both
"-O3" (shrunk to "-O2" explicitly by the PostgreSQL RPM build) and
"-ffast-math". The GNU gcc folks strongly recommend against mixing these
two flags!

afaict the *only* symptom of this misuse of the gcc compiler for
PostgreSQL is the symptom that you see, with incorrect rounding in the
date/time types.

I've posted a corrected .rpmrc file on ftp.postgresql.org in the
/pub/binary area if you want to repair this by rebuilding this from
.src.rpm. And if you know a way to get in touch with the Mandrake RPM
builders that would be helpful; I have not had the time to subscribe to
the cooker mailing list to try to work out this issue.

In the meantime pick up RPMs from the PostgreSQL ftp site, which have
this problem corrected by building against the above-mentioned .rpmrc
file.

I've cross-posted this to the RPM contact address for Mandrake (hi!) and
would be happy to provide more information.

                        - Thomas