pgsql-4789

Version:

8.3.5(7)

Bug Link:

http://postgresql.1045698.n5.nabble.com/BUG-4789-ERROR-22008-on-timestamp-import-td2129617.html

Patch Link:

http://archives.postgresql.org/pgsql-committers/2009-05/msg00008.php

Symptom:

When timestamp’s fractional-second is some number that has many nines(rounds up to 1.0), such as 00:12:57.9999999999999999999999999999, it will report a datetime overflow error.

Is there any error message?

Yes

How it is diagnosed:

We reproduced the failure!

How to reproduce?

Two ways to reproduce the failure:

1.

create 1.csv with one line 1999-08-06 00:12:57.99999999999999999999999999990

postgres=#  CREATE TABLE rude( t timestamp );

CREATE TABLE

postgres=#  \copy rude from '1.csv'

ERROR:  date/time field value out of range: "1999-08-06 00:12:57.99999999999999999999999999990"

CONTEXT:  COPY rude, line 1, column t: "1999-08-06 00:12:57.99999999999999999999999999990"

2.

postgres=# select '1999-08-06 00:12:57.99999999999999999999999999990'::timestamptz;

ERROR:  date/time field value out of range: "1999-08-06 00:12:57.99999999999999999999999999990"

Root Cause:

Brief: This fractional second string “0.99999... “ is converted to 1.0 using strtod (see demo at the bottom) since the machine doesn’t have enough precision to represent it. But the sanity check will ensure a fractional second to be < 1.0, but cannot be equal to 1.0.

Detail:

src/backend/utils/adt/datetime.c

/* str = 00:12:57.99999999999999999999999999990 */

static int

DecodeTime(char *str, int fmask, int *tmask, struct pg_tm * tm, fsec_t *fsec)

{

        char           *cp;

        *tmask = DTK_TIME_M;

        errno = 0;

        tm->tm_hour = strtoi(str, &cp, 10); // tm->tm_hour = 0

        if (errno == ERANGE)

                return DTERR_FIELD_OVERFLOW;

        if (*cp != ':')

                return DTERR_BAD_FORMAT;

        str = cp + 1;

        errno = 0;

        tm->tm_min = strtoi(str, &cp, 10); // tm->tm_min = 1

        if (errno == ERANGE)

                return DTERR_FIELD_OVERFLOW;

        if (*cp == '\0')

        {

                tm->tm_sec = 0;

                *fsec = 0;

        }

        else if (*cp != ':')

                return DTERR_BAD_FORMAT;

        else

        {

                str = cp + 1;

                errno = 0;

                tm->tm_sec = strtoi(str, &cp, 10); // tm->tm_sec = 57

                if (errno == ERANGE)

                        return DTERR_FIELD_OVERFLOW;

                if (*cp == '\0')

                        *fsec = 0;

                else if (*cp == '.')

                {

                        double                frac;

                        str = cp; //str = .99999999999999999999999999990

                        frac = strtod(str, &cp); // frac = 1.0!

                                   /* Note that this is not an overflow problem! It is just a

                                       precision problem. So strtod’s return value is not bogus,

                                       and errno will be 0. */

                        if (*cp != '\0')

                                return DTERR_BAD_FORMAT;

                        *fsec = frac;

                }

                else

                        return DTERR_BAD_FORMAT;

        }

        /* do a sanity check. *fsec = 1.0. In the

                buggy version, it would succeed the *fsec >= 1 test, thus return

                DTERR_FIELD_OVERFLOW. */

        if (tm->tm_hour < 0 || tm->tm_min < 0 || tm->tm_min > 59 ||

-                tm->tm_sec < 0 || tm->tm_sec > 60 || *fsec < 0 || *fsec >= 1)

+                      tm->tm_sec < 0 || tm->tm_sec > 60 || *fsec < 0 || *fsec > 1)

                return DTERR_FIELD_OVERFLOW;

        return 0;

}

Where the error message is printed?

Datum timestamptz_in(PG_FUNCTION_ARGS)

{

        ...

        dterr = ParseDateTime(str, workbuf, sizeof(workbuf),

                                                  field, ftype, MAXDATEFIELDS, &nf);

        if (dterr == 0)

                dterr = DecodeDateTime(field, ftype, nf, &dtype, tm, &fsec, &tz);

        if (dterr != 0)

                DateTimeParseError(dterr, str, "timestamp with time zone");

        ...

}

void DateTimeParseError(int dterr, const char *str, const char *datatype)

{

        switch (dterr)

        {

                case DTERR_FIELD_OVERFLOW: // return value of DecodeTime

                        ereport(ERROR,

                                        (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),

                                         errmsg("date/time field value out of range: \"%s\"",

                                                        str)));

                        break;

                …

        }

}

Information needed to diagnose the failure:

From the patch, all we need to know is strtod returned 1.0 by the given 0.999999999999999. Fortunately, the printed error message is sufficient to infer this information.

Sufficiency:

The printed error message itself is sufficient to diagnose the failure.

How can we handle this?

As the code does, return value check. Although this is not a libc function, it is their own library function: DecodeDateTime. We can automatically learn this through frequent logging pattern: DecodeDateTime has been called multiple times, and everytime its return value was checked and logged.


Demo:

#include <stdio.h>

#include <stdlib.h>

int main ()

{

  char foo[]="0.99999999999999999999999999990";

  double d = strtod(foo,NULL);

  printf("%lf\n",d);

  return 0;

}

Output: 1.000000