Обсуждение: Patch to fix horology regression failure

Поиск
Список
Период
Сортировка

Patch to fix horology regression failure

От
John Cochran
Дата:
Greetings,

Attached is a patch that limits the range tested by horology to
what is capable using integer-datatime timestamps. It does attempt
to exercise the maximum allowable timestamp range.
Also is a small error check when converting a timestamp from external
to internal format that prevents out of range timestamps from being
entered.

Files patched:
    Index: src/backend/utils/adt/timestamp.c
        Added range check to prevent out of range timestamps
        from being used.

    Index: src/test/regress/sql/horology.sql
    Index: src/test/regress/expected/horology-no-DST-before-1970.out
    Index: src/test/regress/expected/horology-solaris-1947.out
        Limited range of timestamps being checked to
        Jan 1, 4713 BC  to Dec 31, 294276

In creating this patch, I have seen some definite problems with integer
timestamps and how they react when used near their limits. For example,
the following statement gives the correct result:

    SELECT timestamp without time zone 'Jan 1, 4713 BC'
           + interval '109203489 days' AS "Dec 31, 294276";

However, this statement which is the logical inverse of the above
gives incorrect results:

    SELECT timestamp without time zone '12/31/294276'
             - timestamp without time zone 'Jan 1, 4713 BC' AS "109203489 Days";


Index: src/backend/utils/adt/timestamp.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.78
diff -c -r1.78 timestamp.c
*** src/backend/utils/adt/timestamp.c    2003/02/22 05:57:45    1.78
--- src/backend/utils/adt/timestamp.c    2003/02/26 02:47:28
***************
*** 964,969 ****
--- 964,972 ----
      time = time2t(tm->tm_hour, tm->tm_min, tm->tm_sec, fsec);
  #ifdef HAVE_INT64_TIMESTAMP
      *result = ((date * INT64CONST(86400000000)) + time);
+     if ((*result < 0 && date >= 0) || (*result >= 0 && date < 0))
+         elog(ERROR, "TIMESTAMP out of range '%04d-%02d-%02d'",
+             tm->tm_year, tm->tm_mon, tm->tm_mday);
  #else
      *result = ((date * 86400) + time);
  #endif
Index: src/test/regress/expected/horology-no-DST-before-1970.out
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/test/regress/expected/horology-no-DST-before-1970.out,v
retrieving revision 1.28
diff -c -r1.28 horology-no-DST-before-1970.out
*** src/test/regress/expected/horology-no-DST-before-1970.out    2003/02/19 03:48:10    1.28
--- src/test/regress/expected/horology-no-DST-before-1970.out    2003/02/26 02:47:30
***************
*** 328,355 ****
   Fri Dec 31 23:59:59 1999
  (1 row)

! SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '1000000000 days' AS "Nov 27, 2733194";
!        Nov 27, 2733194
! -----------------------------
!  Sun Nov 27 00:00:00 2733194
! (1 row)
!
! SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '2000000000 days' AS "Nov 30, 5471101";
!        Nov 30, 5471101
! -----------------------------
!  Sat Nov 30 00:00:00 5471101
! (1 row)
!
! SELECT timestamp without time zone 'Nov 25, 4714 BC' + interval '2147483492 days' AS "Dec 31, 5874897";
!        Dec 31, 5874897
! -----------------------------
!  Tue Dec 31 00:00:00 5874897
! (1 row)
!
! SELECT timestamp without time zone '12/31/5874897' - timestamp without time zone 'Nov 24, 4714 BC' AS "2147483493
Days";
!   2147483493 Days
! -------------------
!  @ 2147483493 days
  (1 row)

  -- Shorthand values
--- 328,355 ----
   Fri Dec 31 23:59:59 1999
  (1 row)

! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '106000000 days' AS "Feb 23, 285506";
!        Feb 23, 285506
! ----------------------------
!  Fri Feb 23 00:00:00 285506
!   (1 row)
!
! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '107000000 days' AS "Jan 20, 288244";
!        Jan 20, 288244
! ----------------------------
!  Sat Jan 20 00:00:00 288244
!   (1 row)
!
! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '109203489 days' AS "Dec 31, 294276";
!        Dec 31, 294276
! ----------------------------
!  Sun Dec 31 00:00:00 294276
!   (1 row)
!
! SELECT timestamp without time zone '12/31/294276' - timestamp without time zone '12/23/1999' AS "106751991 Days";
!   106751991 Days
! ------------------
!  @ 106751991 days
  (1 row)

  -- Shorthand values
Index: src/test/regress/expected/horology-solaris-1947.out
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/test/regress/expected/horology-solaris-1947.out,v
retrieving revision 1.22
diff -c -r1.22 horology-solaris-1947.out
*** src/test/regress/expected/horology-solaris-1947.out    2003/02/19 03:48:10    1.22
--- src/test/regress/expected/horology-solaris-1947.out    2003/02/26 02:47:38
***************
*** 328,355 ****
   Fri Dec 31 23:59:59 1999
  (1 row)

! SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '1000000000 days' AS "Nov 27, 2733194";
!        Nov 27, 2733194
! -----------------------------
!  Sun Nov 27 00:00:00 2733194
! (1 row)
!
! SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '2000000000 days' AS "Nov 30, 5471101";
!        Nov 30, 5471101
! -----------------------------
!  Sat Nov 30 00:00:00 5471101
! (1 row)
!
! SELECT timestamp without time zone 'Nov 25, 4714 BC' + interval '2147483492 days' AS "Dec 31, 5874897";
!        Dec 31, 5874897
! -----------------------------
!  Tue Dec 31 00:00:00 5874897
! (1 row)
!
! SELECT timestamp without time zone '12/31/5874897' - timestamp without time zone 'Nov 24, 4714 BC' AS "2147483493
Days";
!   2147483493 Days
! -------------------
!  @ 2147483493 days
  (1 row)

  -- Shorthand values
--- 328,355 ----
   Fri Dec 31 23:59:59 1999
  (1 row)

! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '106000000 days' AS "Feb 23, 285506";
!        Feb 23, 285506
! ----------------------------
!  Fri Feb 23 00:00:00 285506
!   (1 row)
!
! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '107000000 days' AS "Jan 20, 288244";
!        Jan 20, 288244
! ----------------------------
!  Sat Jan 20 00:00:00 288244
!   (1 row)
!
! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '109203489 days' AS "Dec 31, 294276";
!        Dec 31, 294276
! ----------------------------
!  Sun Dec 31 00:00:00 294276
!   (1 row)
!
! SELECT timestamp without time zone '12/31/294276' - timestamp without time zone '12/23/1999' AS "106751991 Days";
!   106751991 Days
! ------------------
!  @ 106751991 days
  (1 row)

  -- Shorthand values
Index: src/test/regress/expected/horology.out
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/test/regress/expected/horology.out,v
retrieving revision 1.41
diff -c -r1.41 horology.out
*** src/test/regress/expected/horology.out    2003/02/19 03:48:11    1.41
--- src/test/regress/expected/horology.out    2003/02/26 02:47:49
***************
*** 328,355 ****
   Fri Dec 31 23:59:59 1999
  (1 row)

! SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '1000000000 days' AS "Nov 27, 2733194";
!        Nov 27, 2733194
! -----------------------------
!  Sun Nov 27 00:00:00 2733194
! (1 row)
!
! SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '2000000000 days' AS "Nov 30, 5471101";
!        Nov 30, 5471101
! -----------------------------
!  Sat Nov 30 00:00:00 5471101
! (1 row)
!
! SELECT timestamp without time zone 'Nov 25, 4714 BC' + interval '2147483492 days' AS "Dec 31, 5874897";
!        Dec 31, 5874897
! -----------------------------
!  Tue Dec 31 00:00:00 5874897
! (1 row)
!
! SELECT timestamp without time zone '12/31/5874897' - timestamp without time zone 'Nov 24, 4714 BC' AS "2147483493
Days";
!   2147483493 Days
! -------------------
!  @ 2147483493 days
  (1 row)

  -- Shorthand values
--- 328,355 ----
   Fri Dec 31 23:59:59 1999
  (1 row)

! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '106000000 days' AS "Feb 23, 285506";
!        Feb 23, 285506
! ----------------------------
!  Fri Feb 23 00:00:00 285506
!   (1 row)
!
! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '107000000 days' AS "Jan 20, 288244";
!        Jan 20, 288244
! ----------------------------
!  Sat Jan 20 00:00:00 288244
!   (1 row)
!
! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '109203489 days' AS "Dec 31, 294276";
!        Dec 31, 294276
! ----------------------------
!  Sun Dec 31 00:00:00 294276
!   (1 row)
!
! SELECT timestamp without time zone '12/31/294276' - timestamp without time zone '12/23/1999' AS "106751991 Days";
!   106751991 Days
! ------------------
!  @ 106751991 days
  (1 row)

  -- Shorthand values
Index: src/test/regress/sql/horology.sql
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/test/regress/sql/horology.sql,v
retrieving revision 1.26
diff -c -r1.26 horology.sql
*** src/test/regress/sql/horology.sql    2003/02/19 03:48:11    1.26
--- src/test/regress/sql/horology.sql    2003/02/26 02:47:49
***************
*** 76,85 ****
  SELECT timestamp without time zone '1999-03-01' - interval '1 second' AS "Feb 28";
  SELECT timestamp without time zone '2000-03-01' - interval '1 second' AS "Feb 29";
  SELECT timestamp without time zone '1999-12-01' + interval '1 month - 1 second' AS "Dec 31";
! SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '1000000000 days' AS "Nov 27, 2733194";
! SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '2000000000 days' AS "Nov 30, 5471101";
! SELECT timestamp without time zone 'Nov 25, 4714 BC' + interval '2147483492 days' AS "Dec 31, 5874897";
! SELECT timestamp without time zone '12/31/5874897' - timestamp without time zone 'Nov 24, 4714 BC' AS "2147483493
Days";

  -- Shorthand values
  -- Not directly usable for regression testing since these are not constants.
--- 76,85 ----
  SELECT timestamp without time zone '1999-03-01' - interval '1 second' AS "Feb 28";
  SELECT timestamp without time zone '2000-03-01' - interval '1 second' AS "Feb 29";
  SELECT timestamp without time zone '1999-12-01' + interval '1 month - 1 second' AS "Dec 31";
! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '106000000 days' AS "Feb 23, 285506";
! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '107000000 days' AS "Jan 20, 288244";
! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '109203489 days' AS "Dec 31, 294276";
! SELECT timestamp without time zone '12/31/294276' - timestamp without time zone '12/23/1999' AS "106751991 Days";

  -- Shorthand values
  -- Not directly usable for regression testing since these are not constants.

Re: Patch to fix horology regression failure

От
Bruce Momjian
Дата:
Your patch has been added to the PostgreSQL unapplied patches list at:

    http://momjian.postgresql.org/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------


John Cochran wrote:
> Greetings,
>
> Attached is a patch that limits the range tested by horology to
> what is capable using integer-datatime timestamps. It does attempt
> to exercise the maximum allowable timestamp range.
> Also is a small error check when converting a timestamp from external
> to internal format that prevents out of range timestamps from being
> entered.
>
> Files patched:
>     Index: src/backend/utils/adt/timestamp.c
>         Added range check to prevent out of range timestamps
>         from being used.
>
>     Index: src/test/regress/sql/horology.sql
>     Index: src/test/regress/expected/horology-no-DST-before-1970.out
>     Index: src/test/regress/expected/horology-solaris-1947.out
>         Limited range of timestamps being checked to
>         Jan 1, 4713 BC  to Dec 31, 294276
>
> In creating this patch, I have seen some definite problems with integer
> timestamps and how they react when used near their limits. For example,
> the following statement gives the correct result:
>
>     SELECT timestamp without time zone 'Jan 1, 4713 BC'
>            + interval '109203489 days' AS "Dec 31, 294276";
>
> However, this statement which is the logical inverse of the above
> gives incorrect results:
>
>     SELECT timestamp without time zone '12/31/294276'
>              - timestamp without time zone 'Jan 1, 4713 BC' AS "109203489 Days";
>
>
> Index: src/backend/utils/adt/timestamp.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/timestamp.c,v
> retrieving revision 1.78
> diff -c -r1.78 timestamp.c
> *** src/backend/utils/adt/timestamp.c    2003/02/22 05:57:45    1.78
> --- src/backend/utils/adt/timestamp.c    2003/02/26 02:47:28
> ***************
> *** 964,969 ****
> --- 964,972 ----
>       time = time2t(tm->tm_hour, tm->tm_min, tm->tm_sec, fsec);
>   #ifdef HAVE_INT64_TIMESTAMP
>       *result = ((date * INT64CONST(86400000000)) + time);
> +     if ((*result < 0 && date >= 0) || (*result >= 0 && date < 0))
> +         elog(ERROR, "TIMESTAMP out of range '%04d-%02d-%02d'",
> +             tm->tm_year, tm->tm_mon, tm->tm_mday);
>   #else
>       *result = ((date * 86400) + time);
>   #endif
> Index: src/test/regress/expected/horology-no-DST-before-1970.out
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/test/regress/expected/horology-no-DST-before-1970.out,v
> retrieving revision 1.28
> diff -c -r1.28 horology-no-DST-before-1970.out
> *** src/test/regress/expected/horology-no-DST-before-1970.out    2003/02/19 03:48:10    1.28
> --- src/test/regress/expected/horology-no-DST-before-1970.out    2003/02/26 02:47:30
> ***************
> *** 328,355 ****
>    Fri Dec 31 23:59:59 1999
>   (1 row)
>
> ! SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '1000000000 days' AS "Nov 27, 2733194";
> !        Nov 27, 2733194
> ! -----------------------------
> !  Sun Nov 27 00:00:00 2733194
> ! (1 row)
> !
> ! SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '2000000000 days' AS "Nov 30, 5471101";
> !        Nov 30, 5471101
> ! -----------------------------
> !  Sat Nov 30 00:00:00 5471101
> ! (1 row)
> !
> ! SELECT timestamp without time zone 'Nov 25, 4714 BC' + interval '2147483492 days' AS "Dec 31, 5874897";
> !        Dec 31, 5874897
> ! -----------------------------
> !  Tue Dec 31 00:00:00 5874897
> ! (1 row)
> !
> ! SELECT timestamp without time zone '12/31/5874897' - timestamp without time zone 'Nov 24, 4714 BC' AS "2147483493
Days";
> !   2147483493 Days
> ! -------------------
> !  @ 2147483493 days
>   (1 row)
>
>   -- Shorthand values
> --- 328,355 ----
>    Fri Dec 31 23:59:59 1999
>   (1 row)
>
> ! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '106000000 days' AS "Feb 23, 285506";
> !        Feb 23, 285506
> ! ----------------------------
> !  Fri Feb 23 00:00:00 285506
> !   (1 row)
> !
> ! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '107000000 days' AS "Jan 20, 288244";
> !        Jan 20, 288244
> ! ----------------------------
> !  Sat Jan 20 00:00:00 288244
> !   (1 row)
> !
> ! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '109203489 days' AS "Dec 31, 294276";
> !        Dec 31, 294276
> ! ----------------------------
> !  Sun Dec 31 00:00:00 294276
> !   (1 row)
> !
> ! SELECT timestamp without time zone '12/31/294276' - timestamp without time zone '12/23/1999' AS "106751991 Days";
> !   106751991 Days
> ! ------------------
> !  @ 106751991 days
>   (1 row)
>
>   -- Shorthand values
> Index: src/test/regress/expected/horology-solaris-1947.out
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/test/regress/expected/horology-solaris-1947.out,v
> retrieving revision 1.22
> diff -c -r1.22 horology-solaris-1947.out
> *** src/test/regress/expected/horology-solaris-1947.out    2003/02/19 03:48:10    1.22
> --- src/test/regress/expected/horology-solaris-1947.out    2003/02/26 02:47:38
> ***************
> *** 328,355 ****
>    Fri Dec 31 23:59:59 1999
>   (1 row)
>
> ! SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '1000000000 days' AS "Nov 27, 2733194";
> !        Nov 27, 2733194
> ! -----------------------------
> !  Sun Nov 27 00:00:00 2733194
> ! (1 row)
> !
> ! SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '2000000000 days' AS "Nov 30, 5471101";
> !        Nov 30, 5471101
> ! -----------------------------
> !  Sat Nov 30 00:00:00 5471101
> ! (1 row)
> !
> ! SELECT timestamp without time zone 'Nov 25, 4714 BC' + interval '2147483492 days' AS "Dec 31, 5874897";
> !        Dec 31, 5874897
> ! -----------------------------
> !  Tue Dec 31 00:00:00 5874897
> ! (1 row)
> !
> ! SELECT timestamp without time zone '12/31/5874897' - timestamp without time zone 'Nov 24, 4714 BC' AS "2147483493
Days";
> !   2147483493 Days
> ! -------------------
> !  @ 2147483493 days
>   (1 row)
>
>   -- Shorthand values
> --- 328,355 ----
>    Fri Dec 31 23:59:59 1999
>   (1 row)
>
> ! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '106000000 days' AS "Feb 23, 285506";
> !        Feb 23, 285506
> ! ----------------------------
> !  Fri Feb 23 00:00:00 285506
> !   (1 row)
> !
> ! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '107000000 days' AS "Jan 20, 288244";
> !        Jan 20, 288244
> ! ----------------------------
> !  Sat Jan 20 00:00:00 288244
> !   (1 row)
> !
> ! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '109203489 days' AS "Dec 31, 294276";
> !        Dec 31, 294276
> ! ----------------------------
> !  Sun Dec 31 00:00:00 294276
> !   (1 row)
> !
> ! SELECT timestamp without time zone '12/31/294276' - timestamp without time zone '12/23/1999' AS "106751991 Days";
> !   106751991 Days
> ! ------------------
> !  @ 106751991 days
>   (1 row)
>
>   -- Shorthand values
> Index: src/test/regress/expected/horology.out
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/test/regress/expected/horology.out,v
> retrieving revision 1.41
> diff -c -r1.41 horology.out
> *** src/test/regress/expected/horology.out    2003/02/19 03:48:11    1.41
> --- src/test/regress/expected/horology.out    2003/02/26 02:47:49
> ***************
> *** 328,355 ****
>    Fri Dec 31 23:59:59 1999
>   (1 row)
>
> ! SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '1000000000 days' AS "Nov 27, 2733194";
> !        Nov 27, 2733194
> ! -----------------------------
> !  Sun Nov 27 00:00:00 2733194
> ! (1 row)
> !
> ! SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '2000000000 days' AS "Nov 30, 5471101";
> !        Nov 30, 5471101
> ! -----------------------------
> !  Sat Nov 30 00:00:00 5471101
> ! (1 row)
> !
> ! SELECT timestamp without time zone 'Nov 25, 4714 BC' + interval '2147483492 days' AS "Dec 31, 5874897";
> !        Dec 31, 5874897
> ! -----------------------------
> !  Tue Dec 31 00:00:00 5874897
> ! (1 row)
> !
> ! SELECT timestamp without time zone '12/31/5874897' - timestamp without time zone 'Nov 24, 4714 BC' AS "2147483493
Days";
> !   2147483493 Days
> ! -------------------
> !  @ 2147483493 days
>   (1 row)
>
>   -- Shorthand values
> --- 328,355 ----
>    Fri Dec 31 23:59:59 1999
>   (1 row)
>
> ! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '106000000 days' AS "Feb 23, 285506";
> !        Feb 23, 285506
> ! ----------------------------
> !  Fri Feb 23 00:00:00 285506
> !   (1 row)
> !
> ! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '107000000 days' AS "Jan 20, 288244";
> !        Jan 20, 288244
> ! ----------------------------
> !  Sat Jan 20 00:00:00 288244
> !   (1 row)
> !
> ! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '109203489 days' AS "Dec 31, 294276";
> !        Dec 31, 294276
> ! ----------------------------
> !  Sun Dec 31 00:00:00 294276
> !   (1 row)
> !
> ! SELECT timestamp without time zone '12/31/294276' - timestamp without time zone '12/23/1999' AS "106751991 Days";
> !   106751991 Days
> ! ------------------
> !  @ 106751991 days
>   (1 row)
>
>   -- Shorthand values
> Index: src/test/regress/sql/horology.sql
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/test/regress/sql/horology.sql,v
> retrieving revision 1.26
> diff -c -r1.26 horology.sql
> *** src/test/regress/sql/horology.sql    2003/02/19 03:48:11    1.26
> --- src/test/regress/sql/horology.sql    2003/02/26 02:47:49
> ***************
> *** 76,85 ****
>   SELECT timestamp without time zone '1999-03-01' - interval '1 second' AS "Feb 28";
>   SELECT timestamp without time zone '2000-03-01' - interval '1 second' AS "Feb 29";
>   SELECT timestamp without time zone '1999-12-01' + interval '1 month - 1 second' AS "Dec 31";
> ! SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '1000000000 days' AS "Nov 27, 2733194";
> ! SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '2000000000 days' AS "Nov 30, 5471101";
> ! SELECT timestamp without time zone 'Nov 25, 4714 BC' + interval '2147483492 days' AS "Dec 31, 5874897";
> ! SELECT timestamp without time zone '12/31/5874897' - timestamp without time zone 'Nov 24, 4714 BC' AS "2147483493
Days";
>
>   -- Shorthand values
>   -- Not directly usable for regression testing since these are not constants.
> --- 76,85 ----
>   SELECT timestamp without time zone '1999-03-01' - interval '1 second' AS "Feb 28";
>   SELECT timestamp without time zone '2000-03-01' - interval '1 second' AS "Feb 29";
>   SELECT timestamp without time zone '1999-12-01' + interval '1 month - 1 second' AS "Dec 31";
> ! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '106000000 days' AS "Feb 23, 285506";
> ! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '107000000 days' AS "Jan 20, 288244";
> ! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '109203489 days' AS "Dec 31, 294276";
> ! SELECT timestamp without time zone '12/31/294276' - timestamp without time zone '12/23/1999' AS "106751991 Days";
>
>   -- Shorthand values
>   -- Not directly usable for regression testing since these are not constants.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Patch to fix horology regression failure

От
Bruce Momjian
Дата:
Patch applied.  Thanks.

---------------------------------------------------------------------------



John Cochran wrote:
> Greetings,
>
> Attached is a patch that limits the range tested by horology to
> what is capable using integer-datatime timestamps. It does attempt
> to exercise the maximum allowable timestamp range.
> Also is a small error check when converting a timestamp from external
> to internal format that prevents out of range timestamps from being
> entered.
>
> Files patched:
>     Index: src/backend/utils/adt/timestamp.c
>         Added range check to prevent out of range timestamps
>         from being used.
>
>     Index: src/test/regress/sql/horology.sql
>     Index: src/test/regress/expected/horology-no-DST-before-1970.out
>     Index: src/test/regress/expected/horology-solaris-1947.out
>         Limited range of timestamps being checked to
>         Jan 1, 4713 BC  to Dec 31, 294276
>
> In creating this patch, I have seen some definite problems with integer
> timestamps and how they react when used near their limits. For example,
> the following statement gives the correct result:
>
>     SELECT timestamp without time zone 'Jan 1, 4713 BC'
>            + interval '109203489 days' AS "Dec 31, 294276";
>
> However, this statement which is the logical inverse of the above
> gives incorrect results:
>
>     SELECT timestamp without time zone '12/31/294276'
>              - timestamp without time zone 'Jan 1, 4713 BC' AS "109203489 Days";
>
>
> Index: src/backend/utils/adt/timestamp.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/timestamp.c,v
> retrieving revision 1.78
> diff -c -r1.78 timestamp.c
> *** src/backend/utils/adt/timestamp.c    2003/02/22 05:57:45    1.78
> --- src/backend/utils/adt/timestamp.c    2003/02/26 02:47:28
> ***************
> *** 964,969 ****
> --- 964,972 ----
>       time = time2t(tm->tm_hour, tm->tm_min, tm->tm_sec, fsec);
>   #ifdef HAVE_INT64_TIMESTAMP
>       *result = ((date * INT64CONST(86400000000)) + time);
> +     if ((*result < 0 && date >= 0) || (*result >= 0 && date < 0))
> +         elog(ERROR, "TIMESTAMP out of range '%04d-%02d-%02d'",
> +             tm->tm_year, tm->tm_mon, tm->tm_mday);
>   #else
>       *result = ((date * 86400) + time);
>   #endif
> Index: src/test/regress/expected/horology-no-DST-before-1970.out
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/test/regress/expected/horology-no-DST-before-1970.out,v
> retrieving revision 1.28
> diff -c -r1.28 horology-no-DST-before-1970.out
> *** src/test/regress/expected/horology-no-DST-before-1970.out    2003/02/19 03:48:10    1.28
> --- src/test/regress/expected/horology-no-DST-before-1970.out    2003/02/26 02:47:30
> ***************
> *** 328,355 ****
>    Fri Dec 31 23:59:59 1999
>   (1 row)
>
> ! SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '1000000000 days' AS "Nov 27, 2733194";
> !        Nov 27, 2733194
> ! -----------------------------
> !  Sun Nov 27 00:00:00 2733194
> ! (1 row)
> !
> ! SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '2000000000 days' AS "Nov 30, 5471101";
> !        Nov 30, 5471101
> ! -----------------------------
> !  Sat Nov 30 00:00:00 5471101
> ! (1 row)
> !
> ! SELECT timestamp without time zone 'Nov 25, 4714 BC' + interval '2147483492 days' AS "Dec 31, 5874897";
> !        Dec 31, 5874897
> ! -----------------------------
> !  Tue Dec 31 00:00:00 5874897
> ! (1 row)
> !
> ! SELECT timestamp without time zone '12/31/5874897' - timestamp without time zone 'Nov 24, 4714 BC' AS "2147483493
Days";
> !   2147483493 Days
> ! -------------------
> !  @ 2147483493 days
>   (1 row)
>
>   -- Shorthand values
> --- 328,355 ----
>    Fri Dec 31 23:59:59 1999
>   (1 row)
>
> ! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '106000000 days' AS "Feb 23, 285506";
> !        Feb 23, 285506
> ! ----------------------------
> !  Fri Feb 23 00:00:00 285506
> !   (1 row)
> !
> ! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '107000000 days' AS "Jan 20, 288244";
> !        Jan 20, 288244
> ! ----------------------------
> !  Sat Jan 20 00:00:00 288244
> !   (1 row)
> !
> ! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '109203489 days' AS "Dec 31, 294276";
> !        Dec 31, 294276
> ! ----------------------------
> !  Sun Dec 31 00:00:00 294276
> !   (1 row)
> !
> ! SELECT timestamp without time zone '12/31/294276' - timestamp without time zone '12/23/1999' AS "106751991 Days";
> !   106751991 Days
> ! ------------------
> !  @ 106751991 days
>   (1 row)
>
>   -- Shorthand values
> Index: src/test/regress/expected/horology-solaris-1947.out
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/test/regress/expected/horology-solaris-1947.out,v
> retrieving revision 1.22
> diff -c -r1.22 horology-solaris-1947.out
> *** src/test/regress/expected/horology-solaris-1947.out    2003/02/19 03:48:10    1.22
> --- src/test/regress/expected/horology-solaris-1947.out    2003/02/26 02:47:38
> ***************
> *** 328,355 ****
>    Fri Dec 31 23:59:59 1999
>   (1 row)
>
> ! SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '1000000000 days' AS "Nov 27, 2733194";
> !        Nov 27, 2733194
> ! -----------------------------
> !  Sun Nov 27 00:00:00 2733194
> ! (1 row)
> !
> ! SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '2000000000 days' AS "Nov 30, 5471101";
> !        Nov 30, 5471101
> ! -----------------------------
> !  Sat Nov 30 00:00:00 5471101
> ! (1 row)
> !
> ! SELECT timestamp without time zone 'Nov 25, 4714 BC' + interval '2147483492 days' AS "Dec 31, 5874897";
> !        Dec 31, 5874897
> ! -----------------------------
> !  Tue Dec 31 00:00:00 5874897
> ! (1 row)
> !
> ! SELECT timestamp without time zone '12/31/5874897' - timestamp without time zone 'Nov 24, 4714 BC' AS "2147483493
Days";
> !   2147483493 Days
> ! -------------------
> !  @ 2147483493 days
>   (1 row)
>
>   -- Shorthand values
> --- 328,355 ----
>    Fri Dec 31 23:59:59 1999
>   (1 row)
>
> ! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '106000000 days' AS "Feb 23, 285506";
> !        Feb 23, 285506
> ! ----------------------------
> !  Fri Feb 23 00:00:00 285506
> !   (1 row)
> !
> ! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '107000000 days' AS "Jan 20, 288244";
> !        Jan 20, 288244
> ! ----------------------------
> !  Sat Jan 20 00:00:00 288244
> !   (1 row)
> !
> ! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '109203489 days' AS "Dec 31, 294276";
> !        Dec 31, 294276
> ! ----------------------------
> !  Sun Dec 31 00:00:00 294276
> !   (1 row)
> !
> ! SELECT timestamp without time zone '12/31/294276' - timestamp without time zone '12/23/1999' AS "106751991 Days";
> !   106751991 Days
> ! ------------------
> !  @ 106751991 days
>   (1 row)
>
>   -- Shorthand values
> Index: src/test/regress/expected/horology.out
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/test/regress/expected/horology.out,v
> retrieving revision 1.41
> diff -c -r1.41 horology.out
> *** src/test/regress/expected/horology.out    2003/02/19 03:48:11    1.41
> --- src/test/regress/expected/horology.out    2003/02/26 02:47:49
> ***************
> *** 328,355 ****
>    Fri Dec 31 23:59:59 1999
>   (1 row)
>
> ! SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '1000000000 days' AS "Nov 27, 2733194";
> !        Nov 27, 2733194
> ! -----------------------------
> !  Sun Nov 27 00:00:00 2733194
> ! (1 row)
> !
> ! SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '2000000000 days' AS "Nov 30, 5471101";
> !        Nov 30, 5471101
> ! -----------------------------
> !  Sat Nov 30 00:00:00 5471101
> ! (1 row)
> !
> ! SELECT timestamp without time zone 'Nov 25, 4714 BC' + interval '2147483492 days' AS "Dec 31, 5874897";
> !        Dec 31, 5874897
> ! -----------------------------
> !  Tue Dec 31 00:00:00 5874897
> ! (1 row)
> !
> ! SELECT timestamp without time zone '12/31/5874897' - timestamp without time zone 'Nov 24, 4714 BC' AS "2147483493
Days";
> !   2147483493 Days
> ! -------------------
> !  @ 2147483493 days
>   (1 row)
>
>   -- Shorthand values
> --- 328,355 ----
>    Fri Dec 31 23:59:59 1999
>   (1 row)
>
> ! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '106000000 days' AS "Feb 23, 285506";
> !        Feb 23, 285506
> ! ----------------------------
> !  Fri Feb 23 00:00:00 285506
> !   (1 row)
> !
> ! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '107000000 days' AS "Jan 20, 288244";
> !        Jan 20, 288244
> ! ----------------------------
> !  Sat Jan 20 00:00:00 288244
> !   (1 row)
> !
> ! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '109203489 days' AS "Dec 31, 294276";
> !        Dec 31, 294276
> ! ----------------------------
> !  Sun Dec 31 00:00:00 294276
> !   (1 row)
> !
> ! SELECT timestamp without time zone '12/31/294276' - timestamp without time zone '12/23/1999' AS "106751991 Days";
> !   106751991 Days
> ! ------------------
> !  @ 106751991 days
>   (1 row)
>
>   -- Shorthand values
> Index: src/test/regress/sql/horology.sql
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/test/regress/sql/horology.sql,v
> retrieving revision 1.26
> diff -c -r1.26 horology.sql
> *** src/test/regress/sql/horology.sql    2003/02/19 03:48:11    1.26
> --- src/test/regress/sql/horology.sql    2003/02/26 02:47:49
> ***************
> *** 76,85 ****
>   SELECT timestamp without time zone '1999-03-01' - interval '1 second' AS "Feb 28";
>   SELECT timestamp without time zone '2000-03-01' - interval '1 second' AS "Feb 29";
>   SELECT timestamp without time zone '1999-12-01' + interval '1 month - 1 second' AS "Dec 31";
> ! SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '1000000000 days' AS "Nov 27, 2733194";
> ! SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '2000000000 days' AS "Nov 30, 5471101";
> ! SELECT timestamp without time zone 'Nov 25, 4714 BC' + interval '2147483492 days' AS "Dec 31, 5874897";
> ! SELECT timestamp without time zone '12/31/5874897' - timestamp without time zone 'Nov 24, 4714 BC' AS "2147483493
Days";
>
>   -- Shorthand values
>   -- Not directly usable for regression testing since these are not constants.
> --- 76,85 ----
>   SELECT timestamp without time zone '1999-03-01' - interval '1 second' AS "Feb 28";
>   SELECT timestamp without time zone '2000-03-01' - interval '1 second' AS "Feb 29";
>   SELECT timestamp without time zone '1999-12-01' + interval '1 month - 1 second' AS "Dec 31";
> ! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '106000000 days' AS "Feb 23, 285506";
> ! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '107000000 days' AS "Jan 20, 288244";
> ! SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '109203489 days' AS "Dec 31, 294276";
> ! SELECT timestamp without time zone '12/31/294276' - timestamp without time zone '12/23/1999' AS "106751991 Days";
>
>   -- Shorthand values
>   -- Not directly usable for regression testing since these are not constants.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Сайт использует файлы cookie для корректной работы и повышения удобства. Нажимая кнопку «Принять» или продолжая пользоваться сайтом, вы соглашаетесь на их использование в соответствии с Политикой в отношении обработки cookie ООО «ППГ», в том числе на передачу данных из файлов cookie сторонним статистическим и рекламным службам. Вы можете управлять настройками cookie через параметры вашего браузера