Thread: BUG #17866: behavior does not match documentation

The following bug has been logged on the website:

Bug reference:      17866
Logged by:          Евгений Жужнев
Email address:      eugeny@zhuzhnev.com
PostgreSQL version: 15.2
Operating system:   Oracle Linux 9
Description:

sudo -u postgres psql pgc
psql (15.2)
Type "help" for help.

pgc=# SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
    extract    
---------------
 442800.000000
(1 row)
--
But in documentation we can see:
SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
Result: 442800
https://www.postgresql.org/docs/current/functions-datetime.html

PG Bug reporting form <noreply@postgresql.org> writes:
> pgc=# SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
>     extract    
> ---------------
>  442800.000000
> (1 row)

> But in documentation we can see:
> SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
> Result: 442800

Hmm.  That's not wrong exactly, but it's fair to question whether it
satisfies the POLA.  This behavior change happened with commit a2da77cdb
(Change return type of EXTRACT to numeric), and it was intentional
according to the commit log:
    
    - Return values when extracting fields with possibly fractional
      values, such as second and epoch, now have the full scale that the
      value has internally (so, for example, '1.000000' instead of just
      '1').

But exactly nothing was mentioned of that in user-facing docs.
I wonder if we should rethink that and have these operations strip
insignificant trailing zeroes.  It looks like that could be done as
practically a one-liner change, since int64_div_fast_to_numeric isn't
yet used anywhere except in these datetime extraction functions.

(The test cases that change behavior are either reverting to their
pre-a2da77cdb output, or were newly added in that commit.)

Thoughts?

            regards, tom lane

diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index a83feea396..a1cc47b5f7 100644
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -4127,9 +4127,9 @@ get_min_scale(NumericVar *var)
     int            last_digit_pos;

     /*
-     * Ordinarily, the input value will be "stripped" so that the last
-     * NumericDigit is nonzero.  But we don't want to get into an infinite
-     * loop if it isn't, so explicitly find the last nonzero digit.
+     * Find the last nonzero digit, if any.  (If the input has been unpacked
+     * from storage format, there won't be trailing zero digits; but in
+     * general we can't assume that.)
      */
     last_digit_pos = var->ndigits - 1;
     while (last_digit_pos >= 0 &&
@@ -4243,15 +4243,11 @@ int64_div_fast_to_numeric(int64 val1, int log10val2)
 {
     Numeric        res;
     NumericVar    result;
-    int            rscale;
     int            w;
     int            m;

     init_var(&result);

-    /* result scale */
-    rscale = log10val2 < 0 ? 0 : log10val2;
-
     /* how much to decrease the weight by */
     w = log10val2 / DEC_DIGITS;
     /* how much is left to divide by */
@@ -4314,7 +4310,9 @@ int64_div_fast_to_numeric(int64 val1, int log10val2)
         int64_to_numericvar(val1, &result);

     result.weight -= w;
-    result.dscale = rscale;
+
+    /* Set dscale to include only nonzero fractional digits */
+    result.dscale = get_min_scale(&result);

     res = make_result(&result);

diff --git a/src/test/regress/expected/interval.out b/src/test/regress/expected/interval.out
index 28b71d9681..d791c6772e 100644
--- a/src/test/regress/expected/interval.out
+++ b/src/test/regress/expected/interval.out
@@ -1692,18 +1692,18 @@ SELECT f1,
     EXTRACT(MILLENNIUM FROM f1) AS MILLENNIUM,
     EXTRACT(EPOCH FROM f1) AS EPOCH
     FROM INTERVAL_TBL;
-              f1               | microsecond | millisecond |   second   | minute | hour | day | month | quarter | year
|decade | century | millennium |       epoch        

--------------------------------+-------------+-------------+------------+--------+------+-----+-------+---------+------+--------+---------+------------+-------------------
- @ 1 min                       |           0 |       0.000 |   0.000000 |      1 |    0 |   0 |     0 |       1 |    0
|     0 |       0 |          0 |         60.000000 
- @ 5 hours                     |           0 |       0.000 |   0.000000 |      0 |    5 |   0 |     0 |       1 |    0
|     0 |       0 |          0 |      18000.000000 
- @ 10 days                     |           0 |       0.000 |   0.000000 |      0 |    0 |  10 |     0 |       1 |    0
|     0 |       0 |          0 |     864000.000000 
- @ 34 years                    |           0 |       0.000 |   0.000000 |      0 |    0 |   0 |     0 |       1 |   34
|     3 |       0 |          0 | 1072958400.000000 
- @ 3 mons                      |           0 |       0.000 |   0.000000 |      0 |    0 |   0 |     3 |       2 |    0
|     0 |       0 |          0 |    7776000.000000 
- @ 14 secs ago                 |   -14000000 |  -14000.000 | -14.000000 |      0 |    0 |   0 |     0 |       1 |    0
|     0 |       0 |          0 |        -14.000000 
- @ 1 day 2 hours 3 mins 4 secs |     4000000 |    4000.000 |   4.000000 |      3 |    2 |   1 |     0 |       1 |    0
|     0 |       0 |          0 |      93784.000000 
- @ 6 years                     |           0 |       0.000 |   0.000000 |      0 |    0 |   0 |     0 |       1 |    6
|     0 |       0 |          0 |  189345600.000000 
- @ 5 mons                      |           0 |       0.000 |   0.000000 |      0 |    0 |   0 |     5 |       2 |    0
|     0 |       0 |          0 |   12960000.000000 
- @ 5 mons 12 hours             |           0 |       0.000 |   0.000000 |      0 |   12 |   0 |     5 |       2 |    0
|     0 |       0 |          0 |   13003200.000000 
+              f1               | microsecond | millisecond | second | minute | hour | day | month | quarter | year |
decade| century | millennium |   epoch     

+-------------------------------+-------------+-------------+--------+--------+------+-----+-------+---------+------+--------+---------+------------+------------
+ @ 1 min                       |           0 |           0 |      0 |      1 |    0 |   0 |     0 |       1 |    0 |
  0 |       0 |          0 |         60 
+ @ 5 hours                     |           0 |           0 |      0 |      0 |    5 |   0 |     0 |       1 |    0 |
  0 |       0 |          0 |      18000 
+ @ 10 days                     |           0 |           0 |      0 |      0 |    0 |  10 |     0 |       1 |    0 |
  0 |       0 |          0 |     864000 
+ @ 34 years                    |           0 |           0 |      0 |      0 |    0 |   0 |     0 |       1 |   34 |
  3 |       0 |          0 | 1072958400 
+ @ 3 mons                      |           0 |           0 |      0 |      0 |    0 |   0 |     3 |       2 |    0 |
  0 |       0 |          0 |    7776000 
+ @ 14 secs ago                 |   -14000000 |      -14000 |    -14 |      0 |    0 |   0 |     0 |       1 |    0 |
  0 |       0 |          0 |        -14 
+ @ 1 day 2 hours 3 mins 4 secs |     4000000 |        4000 |      4 |      3 |    2 |   1 |     0 |       1 |    0 |
  0 |       0 |          0 |      93784 
+ @ 6 years                     |           0 |           0 |      0 |      0 |    0 |   0 |     0 |       1 |    6 |
  0 |       0 |          0 |  189345600 
+ @ 5 mons                      |           0 |           0 |      0 |      0 |    0 |   0 |     5 |       2 |    0 |
  0 |       0 |          0 |   12960000 
+ @ 5 mons 12 hours             |           0 |           0 |      0 |      0 |   12 |   0 |     5 |       2 |    0 |
  0 |       0 |          0 |   13003200 
 (10 rows)

 SELECT EXTRACT(FORTNIGHT FROM INTERVAL '2 days');  -- error
@@ -1782,8 +1782,8 @@ SELECT f1,

 -- internal overflow test case
 SELECT extract(epoch from interval '1000000000 days');
-        extract
------------------------
- 86400000000000.000000
+    extract
+----------------
+ 86400000000000
 (1 row)

diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index c64bcb7c12..390dae701f 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -1118,73 +1118,73 @@ SELECT d1 as "timestamp",
    round(extract(julian from d1)) AS julian,
    extract(epoch from d1) AS epoch
    FROM TIMESTAMP_TBL;
-          timestamp          | microseconds | milliseconds |  seconds  |  julian   |        epoch
------------------------------+--------------+--------------+-----------+-----------+---------------------
- -infinity                   |              |              |           | -Infinity |           -Infinity
- infinity                    |              |              |           |  Infinity |            Infinity
- Thu Jan 01 00:00:00 1970    |            0 |        0.000 |  0.000000 |   2440588 |            0.000000
- Mon Feb 10 17:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855595921.000000
- Mon Feb 10 17:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855595921.000000
- Mon Feb 10 17:32:02 1997    |      2000000 |     2000.000 |  2.000000 |   2450491 |    855595922.000000
- Mon Feb 10 17:32:01.4 1997  |      1400000 |     1400.000 |  1.400000 |   2450491 |    855595921.400000
- Mon Feb 10 17:32:01.5 1997  |      1500000 |     1500.000 |  1.500000 |   2450491 |    855595921.500000
- Mon Feb 10 17:32:01.6 1997  |      1600000 |     1600.000 |  1.600000 |   2450491 |    855595921.600000
- Thu Jan 02 00:00:00 1997    |            0 |        0.000 |  0.000000 |   2450451 |    852163200.000000
- Thu Jan 02 03:04:05 1997    |      5000000 |     5000.000 |  5.000000 |   2450451 |    852174245.000000
- Mon Feb 10 17:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855595921.000000
- Mon Feb 10 17:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855595921.000000
- Mon Feb 10 17:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855595921.000000
- Mon Feb 10 17:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855595921.000000
- Tue Jun 10 17:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450611 |    865963921.000000
- Sat Sep 22 18:19:20 2001    |     20000000 |    20000.000 | 20.000000 |   2452176 |   1001182760.000000
- Wed Mar 15 08:14:01 2000    |      1000000 |     1000.000 |  1.000000 |   2451619 |    953108041.000000
- Wed Mar 15 13:14:02 2000    |      2000000 |     2000.000 |  2.000000 |   2451620 |    953126042.000000
- Wed Mar 15 12:14:03 2000    |      3000000 |     3000.000 |  3.000000 |   2451620 |    953122443.000000
- Wed Mar 15 03:14:04 2000    |      4000000 |     4000.000 |  4.000000 |   2451619 |    953090044.000000
- Wed Mar 15 02:14:05 2000    |      5000000 |     5000.000 |  5.000000 |   2451619 |    953086445.000000
- Mon Feb 10 17:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855595921.000000
- Mon Feb 10 17:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855595921.000000
- Mon Feb 10 17:32:00 1997    |            0 |        0.000 |  0.000000 |   2450491 |    855595920.000000
- Mon Feb 10 17:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855595921.000000
- Mon Feb 10 17:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855595921.000000
- Mon Feb 10 17:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855595921.000000
- Mon Feb 10 17:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855595921.000000
- Mon Feb 10 17:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855595921.000000
- Mon Feb 10 17:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855595921.000000
- Mon Feb 10 17:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855595921.000000
- Mon Feb 10 17:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855595921.000000
- Mon Feb 10 17:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855595921.000000
- Tue Jun 10 18:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450611 |    865967521.000000
- Mon Feb 10 17:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855595921.000000
- Tue Feb 11 17:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450492 |    855682321.000000
- Wed Feb 12 17:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450493 |    855768721.000000
- Thu Feb 13 17:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450494 |    855855121.000000
- Fri Feb 14 17:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450495 |    855941521.000000
- Sat Feb 15 17:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450496 |    856027921.000000
- Sun Feb 16 17:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450497 |    856114321.000000
- Tue Feb 16 17:32:01 0097 BC |      1000000 |     1000.000 |  1.000000 |   1686043 | -65192711279.000000
- Sat Feb 16 17:32:01 0097    |      1000000 |     1000.000 |  1.000000 |   1756537 | -59102029679.000000
- Thu Feb 16 17:32:01 0597    |      1000000 |     1000.000 |  1.000000 |   1939158 | -43323575279.000000
- Tue Feb 16 17:32:01 1097    |      1000000 |     1000.000 |  1.000000 |   2121779 | -27545120879.000000
- Sat Feb 16 17:32:01 1697    |      1000000 |     1000.000 |  1.000000 |   2340925 |  -8610906479.000000
- Thu Feb 16 17:32:01 1797    |      1000000 |     1000.000 |  1.000000 |   2377449 |  -5455232879.000000
- Tue Feb 16 17:32:01 1897    |      1000000 |     1000.000 |  1.000000 |   2413973 |  -2299559279.000000
- Sun Feb 16 17:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450497 |    856114321.000000
- Sat Feb 16 17:32:01 2097    |      1000000 |     1000.000 |  1.000000 |   2487022 |   4011874321.000000
- Wed Feb 28 17:32:01 1996    |      1000000 |     1000.000 |  1.000000 |   2450143 |    825528721.000000
- Thu Feb 29 17:32:01 1996    |      1000000 |     1000.000 |  1.000000 |   2450144 |    825615121.000000
- Fri Mar 01 17:32:01 1996    |      1000000 |     1000.000 |  1.000000 |   2450145 |    825701521.000000
- Mon Dec 30 17:32:01 1996    |      1000000 |     1000.000 |  1.000000 |   2450449 |    851967121.000000
- Tue Dec 31 17:32:01 1996    |      1000000 |     1000.000 |  1.000000 |   2450450 |    852053521.000000
- Wed Jan 01 17:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450451 |    852139921.000000
- Fri Feb 28 17:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450509 |    857151121.000000
- Sat Mar 01 17:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450510 |    857237521.000000
- Tue Dec 30 17:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450814 |    883503121.000000
- Wed Dec 31 17:32:01 1997    |      1000000 |     1000.000 |  1.000000 |   2450815 |    883589521.000000
- Fri Dec 31 17:32:01 1999    |      1000000 |     1000.000 |  1.000000 |   2451545 |    946661521.000000
- Sat Jan 01 17:32:01 2000    |      1000000 |     1000.000 |  1.000000 |   2451546 |    946747921.000000
- Sun Dec 31 17:32:01 2000    |      1000000 |     1000.000 |  1.000000 |   2451911 |    978283921.000000
- Mon Jan 01 17:32:01 2001    |      1000000 |     1000.000 |  1.000000 |   2451912 |    978370321.000000
+          timestamp          | microseconds | milliseconds | seconds |  julian   |    epoch
+-----------------------------+--------------+--------------+---------+-----------+--------------
+ -infinity                   |              |              |         | -Infinity |    -Infinity
+ infinity                    |              |              |         |  Infinity |     Infinity
+ Thu Jan 01 00:00:00 1970    |            0 |            0 |       0 |   2440588 |            0
+ Mon Feb 10 17:32:01 1997    |      1000000 |         1000 |       1 |   2450491 |    855595921
+ Mon Feb 10 17:32:01 1997    |      1000000 |         1000 |       1 |   2450491 |    855595921
+ Mon Feb 10 17:32:02 1997    |      2000000 |         2000 |       2 |   2450491 |    855595922
+ Mon Feb 10 17:32:01.4 1997  |      1400000 |         1400 |     1.4 |   2450491 |  855595921.4
+ Mon Feb 10 17:32:01.5 1997  |      1500000 |         1500 |     1.5 |   2450491 |  855595921.5
+ Mon Feb 10 17:32:01.6 1997  |      1600000 |         1600 |     1.6 |   2450491 |  855595921.6
+ Thu Jan 02 00:00:00 1997    |            0 |            0 |       0 |   2450451 |    852163200
+ Thu Jan 02 03:04:05 1997    |      5000000 |         5000 |       5 |   2450451 |    852174245
+ Mon Feb 10 17:32:01 1997    |      1000000 |         1000 |       1 |   2450491 |    855595921
+ Mon Feb 10 17:32:01 1997    |      1000000 |         1000 |       1 |   2450491 |    855595921
+ Mon Feb 10 17:32:01 1997    |      1000000 |         1000 |       1 |   2450491 |    855595921
+ Mon Feb 10 17:32:01 1997    |      1000000 |         1000 |       1 |   2450491 |    855595921
+ Tue Jun 10 17:32:01 1997    |      1000000 |         1000 |       1 |   2450611 |    865963921
+ Sat Sep 22 18:19:20 2001    |     20000000 |        20000 |      20 |   2452176 |   1001182760
+ Wed Mar 15 08:14:01 2000    |      1000000 |         1000 |       1 |   2451619 |    953108041
+ Wed Mar 15 13:14:02 2000    |      2000000 |         2000 |       2 |   2451620 |    953126042
+ Wed Mar 15 12:14:03 2000    |      3000000 |         3000 |       3 |   2451620 |    953122443
+ Wed Mar 15 03:14:04 2000    |      4000000 |         4000 |       4 |   2451619 |    953090044
+ Wed Mar 15 02:14:05 2000    |      5000000 |         5000 |       5 |   2451619 |    953086445
+ Mon Feb 10 17:32:01 1997    |      1000000 |         1000 |       1 |   2450491 |    855595921
+ Mon Feb 10 17:32:01 1997    |      1000000 |         1000 |       1 |   2450491 |    855595921
+ Mon Feb 10 17:32:00 1997    |            0 |            0 |       0 |   2450491 |    855595920
+ Mon Feb 10 17:32:01 1997    |      1000000 |         1000 |       1 |   2450491 |    855595921
+ Mon Feb 10 17:32:01 1997    |      1000000 |         1000 |       1 |   2450491 |    855595921
+ Mon Feb 10 17:32:01 1997    |      1000000 |         1000 |       1 |   2450491 |    855595921
+ Mon Feb 10 17:32:01 1997    |      1000000 |         1000 |       1 |   2450491 |    855595921
+ Mon Feb 10 17:32:01 1997    |      1000000 |         1000 |       1 |   2450491 |    855595921
+ Mon Feb 10 17:32:01 1997    |      1000000 |         1000 |       1 |   2450491 |    855595921
+ Mon Feb 10 17:32:01 1997    |      1000000 |         1000 |       1 |   2450491 |    855595921
+ Mon Feb 10 17:32:01 1997    |      1000000 |         1000 |       1 |   2450491 |    855595921
+ Mon Feb 10 17:32:01 1997    |      1000000 |         1000 |       1 |   2450491 |    855595921
+ Tue Jun 10 18:32:01 1997    |      1000000 |         1000 |       1 |   2450611 |    865967521
+ Mon Feb 10 17:32:01 1997    |      1000000 |         1000 |       1 |   2450491 |    855595921
+ Tue Feb 11 17:32:01 1997    |      1000000 |         1000 |       1 |   2450492 |    855682321
+ Wed Feb 12 17:32:01 1997    |      1000000 |         1000 |       1 |   2450493 |    855768721
+ Thu Feb 13 17:32:01 1997    |      1000000 |         1000 |       1 |   2450494 |    855855121
+ Fri Feb 14 17:32:01 1997    |      1000000 |         1000 |       1 |   2450495 |    855941521
+ Sat Feb 15 17:32:01 1997    |      1000000 |         1000 |       1 |   2450496 |    856027921
+ Sun Feb 16 17:32:01 1997    |      1000000 |         1000 |       1 |   2450497 |    856114321
+ Tue Feb 16 17:32:01 0097 BC |      1000000 |         1000 |       1 |   1686043 | -65192711279
+ Sat Feb 16 17:32:01 0097    |      1000000 |         1000 |       1 |   1756537 | -59102029679
+ Thu Feb 16 17:32:01 0597    |      1000000 |         1000 |       1 |   1939158 | -43323575279
+ Tue Feb 16 17:32:01 1097    |      1000000 |         1000 |       1 |   2121779 | -27545120879
+ Sat Feb 16 17:32:01 1697    |      1000000 |         1000 |       1 |   2340925 |  -8610906479
+ Thu Feb 16 17:32:01 1797    |      1000000 |         1000 |       1 |   2377449 |  -5455232879
+ Tue Feb 16 17:32:01 1897    |      1000000 |         1000 |       1 |   2413973 |  -2299559279
+ Sun Feb 16 17:32:01 1997    |      1000000 |         1000 |       1 |   2450497 |    856114321
+ Sat Feb 16 17:32:01 2097    |      1000000 |         1000 |       1 |   2487022 |   4011874321
+ Wed Feb 28 17:32:01 1996    |      1000000 |         1000 |       1 |   2450143 |    825528721
+ Thu Feb 29 17:32:01 1996    |      1000000 |         1000 |       1 |   2450144 |    825615121
+ Fri Mar 01 17:32:01 1996    |      1000000 |         1000 |       1 |   2450145 |    825701521
+ Mon Dec 30 17:32:01 1996    |      1000000 |         1000 |       1 |   2450449 |    851967121
+ Tue Dec 31 17:32:01 1996    |      1000000 |         1000 |       1 |   2450450 |    852053521
+ Wed Jan 01 17:32:01 1997    |      1000000 |         1000 |       1 |   2450451 |    852139921
+ Fri Feb 28 17:32:01 1997    |      1000000 |         1000 |       1 |   2450509 |    857151121
+ Sat Mar 01 17:32:01 1997    |      1000000 |         1000 |       1 |   2450510 |    857237521
+ Tue Dec 30 17:32:01 1997    |      1000000 |         1000 |       1 |   2450814 |    883503121
+ Wed Dec 31 17:32:01 1997    |      1000000 |         1000 |       1 |   2450815 |    883589521
+ Fri Dec 31 17:32:01 1999    |      1000000 |         1000 |       1 |   2451545 |    946661521
+ Sat Jan 01 17:32:01 2000    |      1000000 |         1000 |       1 |   2451546 |    946747921
+ Sun Dec 31 17:32:01 2000    |      1000000 |         1000 |       1 |   2451911 |    978283921
+ Mon Jan 01 17:32:01 2001    |      1000000 |         1000 |       1 |   2451912 |    978370321
 (65 rows)

 -- value near upper bound uses special case in code
@@ -1202,9 +1202,9 @@ SELECT extract(epoch from '294270-01-01 00:00:00'::timestamp);

 -- another internal overflow test case
 SELECT extract(epoch from '5000-01-01 00:00:00'::timestamp);
-      extract
---------------------
- 95617584000.000000
+   extract
+-------------
+ 95617584000
 (1 row)

 -- test edge-case overflow in timestamp subtraction
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index 0dd2fe2c82..9f284e5538 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -1241,74 +1241,74 @@ SELECT d1 as "timestamp",
    round(extract(julian from d1)) AS julian,
    extract(epoch from d1) AS epoch
    FROM TIMESTAMPTZ_TBL;
-            timestamp            | microseconds | milliseconds |  seconds  |  julian   |        epoch
----------------------------------+--------------+--------------+-----------+-----------+---------------------
- -infinity                       |              |              |           | -Infinity |           -Infinity
- infinity                        |              |              |           |  Infinity |            Infinity
- Wed Dec 31 16:00:00 1969 PST    |            0 |        0.000 |  0.000000 |   2440588 |            0.000000
- Mon Feb 10 17:32:01 1997 PST    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855624721.000000
- Mon Feb 10 17:32:01 1997 PST    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855624721.000000
- Mon Feb 10 17:32:02 1997 PST    |      2000000 |     2000.000 |  2.000000 |   2450491 |    855624722.000000
- Mon Feb 10 17:32:01.4 1997 PST  |      1400000 |     1400.000 |  1.400000 |   2450491 |    855624721.400000
- Mon Feb 10 17:32:01.5 1997 PST  |      1500000 |     1500.000 |  1.500000 |   2450491 |    855624721.500000
- Mon Feb 10 17:32:01.6 1997 PST  |      1600000 |     1600.000 |  1.600000 |   2450491 |    855624721.600000
- Thu Jan 02 00:00:00 1997 PST    |            0 |        0.000 |  0.000000 |   2450451 |    852192000.000000
- Thu Jan 02 03:04:05 1997 PST    |      5000000 |     5000.000 |  5.000000 |   2450451 |    852203045.000000
- Mon Feb 10 17:32:01 1997 PST    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855624721.000000
- Mon Feb 10 17:32:01 1997 PST    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855624721.000000
- Mon Feb 10 17:32:01 1997 PST    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855624721.000000
- Mon Feb 10 17:32:01 1997 PST    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855624721.000000
- Tue Jun 10 17:32:01 1997 PDT    |      1000000 |     1000.000 |  1.000000 |   2450611 |    865989121.000000
- Sat Sep 22 18:19:20 2001 PDT    |     20000000 |    20000.000 | 20.000000 |   2452176 |   1001207960.000000
- Wed Mar 15 08:14:01 2000 PST    |      1000000 |     1000.000 |  1.000000 |   2451619 |    953136841.000000
- Wed Mar 15 04:14:02 2000 PST    |      2000000 |     2000.000 |  2.000000 |   2451619 |    953122442.000000
- Wed Mar 15 02:14:03 2000 PST    |      3000000 |     3000.000 |  3.000000 |   2451619 |    953115243.000000
- Wed Mar 15 03:14:04 2000 PST    |      4000000 |     4000.000 |  4.000000 |   2451619 |    953118844.000000
- Wed Mar 15 01:14:05 2000 PST    |      5000000 |     5000.000 |  5.000000 |   2451619 |    953111645.000000
- Mon Feb 10 17:32:01 1997 PST    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855624721.000000
- Mon Feb 10 17:32:01 1997 PST    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855624721.000000
- Mon Feb 10 17:32:00 1997 PST    |            0 |        0.000 |  0.000000 |   2450491 |    855624720.000000
- Mon Feb 10 17:32:01 1997 PST    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855624721.000000
- Mon Feb 10 17:32:01 1997 PST    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855624721.000000
- Mon Feb 10 17:32:01 1997 PST    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855624721.000000
- Mon Feb 10 17:32:01 1997 PST    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855624721.000000
- Mon Feb 10 17:32:01 1997 PST    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855624721.000000
- Mon Feb 10 09:32:01 1997 PST    |      1000000 |     1000.000 |  1.000000 |   2450490 |    855595921.000000
- Mon Feb 10 09:32:01 1997 PST    |      1000000 |     1000.000 |  1.000000 |   2450490 |    855595921.000000
- Mon Feb 10 09:32:01 1997 PST    |      1000000 |     1000.000 |  1.000000 |   2450490 |    855595921.000000
- Mon Feb 10 14:32:01 1997 PST    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855613921.000000
- Thu Jul 10 14:32:01 1997 PDT    |      1000000 |     1000.000 |  1.000000 |   2450641 |    868570321.000000
- Tue Jun 10 18:32:01 1997 PDT    |      1000000 |     1000.000 |  1.000000 |   2450611 |    865992721.000000
- Mon Feb 10 17:32:01 1997 PST    |      1000000 |     1000.000 |  1.000000 |   2450491 |    855624721.000000
- Tue Feb 11 17:32:01 1997 PST    |      1000000 |     1000.000 |  1.000000 |   2450492 |    855711121.000000
- Wed Feb 12 17:32:01 1997 PST    |      1000000 |     1000.000 |  1.000000 |   2450493 |    855797521.000000
- Thu Feb 13 17:32:01 1997 PST    |      1000000 |     1000.000 |  1.000000 |   2450494 |    855883921.000000
- Fri Feb 14 17:32:01 1997 PST    |      1000000 |     1000.000 |  1.000000 |   2450495 |    855970321.000000
- Sat Feb 15 17:32:01 1997 PST    |      1000000 |     1000.000 |  1.000000 |   2450496 |    856056721.000000
- Sun Feb 16 17:32:01 1997 PST    |      1000000 |     1000.000 |  1.000000 |   2450497 |    856143121.000000
- Tue Feb 16 17:32:01 0097 PST BC |      1000000 |     1000.000 |  1.000000 |   1686043 | -65192682479.000000
- Sat Feb 16 17:32:01 0097 PST    |      1000000 |     1000.000 |  1.000000 |   1756537 | -59102000879.000000
- Thu Feb 16 17:32:01 0597 PST    |      1000000 |     1000.000 |  1.000000 |   1939158 | -43323546479.000000
- Tue Feb 16 17:32:01 1097 PST    |      1000000 |     1000.000 |  1.000000 |   2121779 | -27545092079.000000
- Sat Feb 16 17:32:01 1697 PST    |      1000000 |     1000.000 |  1.000000 |   2340925 |  -8610877679.000000
- Thu Feb 16 17:32:01 1797 PST    |      1000000 |     1000.000 |  1.000000 |   2377449 |  -5455204079.000000
- Tue Feb 16 17:32:01 1897 PST    |      1000000 |     1000.000 |  1.000000 |   2413973 |  -2299530479.000000
- Sun Feb 16 17:32:01 1997 PST    |      1000000 |     1000.000 |  1.000000 |   2450497 |    856143121.000000
- Sat Feb 16 17:32:01 2097 PST    |      1000000 |     1000.000 |  1.000000 |   2487022 |   4011903121.000000
- Wed Feb 28 17:32:01 1996 PST    |      1000000 |     1000.000 |  1.000000 |   2450143 |    825557521.000000
- Thu Feb 29 17:32:01 1996 PST    |      1000000 |     1000.000 |  1.000000 |   2450144 |    825643921.000000
- Fri Mar 01 17:32:01 1996 PST    |      1000000 |     1000.000 |  1.000000 |   2450145 |    825730321.000000
- Mon Dec 30 17:32:01 1996 PST    |      1000000 |     1000.000 |  1.000000 |   2450449 |    851995921.000000
- Tue Dec 31 17:32:01 1996 PST    |      1000000 |     1000.000 |  1.000000 |   2450450 |    852082321.000000
- Wed Jan 01 17:32:01 1997 PST    |      1000000 |     1000.000 |  1.000000 |   2450451 |    852168721.000000
- Fri Feb 28 17:32:01 1997 PST    |      1000000 |     1000.000 |  1.000000 |   2450509 |    857179921.000000
- Sat Mar 01 17:32:01 1997 PST    |      1000000 |     1000.000 |  1.000000 |   2450510 |    857266321.000000
- Tue Dec 30 17:32:01 1997 PST    |      1000000 |     1000.000 |  1.000000 |   2450814 |    883531921.000000
- Wed Dec 31 17:32:01 1997 PST    |      1000000 |     1000.000 |  1.000000 |   2450815 |    883618321.000000
- Fri Dec 31 17:32:01 1999 PST    |      1000000 |     1000.000 |  1.000000 |   2451545 |    946690321.000000
- Sat Jan 01 17:32:01 2000 PST    |      1000000 |     1000.000 |  1.000000 |   2451546 |    946776721.000000
- Sun Dec 31 17:32:01 2000 PST    |      1000000 |     1000.000 |  1.000000 |   2451911 |    978312721.000000
- Mon Jan 01 17:32:01 2001 PST    |      1000000 |     1000.000 |  1.000000 |   2451912 |    978399121.000000
+            timestamp            | microseconds | milliseconds | seconds |  julian   |    epoch
+---------------------------------+--------------+--------------+---------+-----------+--------------
+ -infinity                       |              |              |         | -Infinity |    -Infinity
+ infinity                        |              |              |         |  Infinity |     Infinity
+ Wed Dec 31 16:00:00 1969 PST    |            0 |            0 |       0 |   2440588 |            0
+ Mon Feb 10 17:32:01 1997 PST    |      1000000 |         1000 |       1 |   2450491 |    855624721
+ Mon Feb 10 17:32:01 1997 PST    |      1000000 |         1000 |       1 |   2450491 |    855624721
+ Mon Feb 10 17:32:02 1997 PST    |      2000000 |         2000 |       2 |   2450491 |    855624722
+ Mon Feb 10 17:32:01.4 1997 PST  |      1400000 |         1400 |     1.4 |   2450491 |  855624721.4
+ Mon Feb 10 17:32:01.5 1997 PST  |      1500000 |         1500 |     1.5 |   2450491 |  855624721.5
+ Mon Feb 10 17:32:01.6 1997 PST  |      1600000 |         1600 |     1.6 |   2450491 |  855624721.6
+ Thu Jan 02 00:00:00 1997 PST    |            0 |            0 |       0 |   2450451 |    852192000
+ Thu Jan 02 03:04:05 1997 PST    |      5000000 |         5000 |       5 |   2450451 |    852203045
+ Mon Feb 10 17:32:01 1997 PST    |      1000000 |         1000 |       1 |   2450491 |    855624721
+ Mon Feb 10 17:32:01 1997 PST    |      1000000 |         1000 |       1 |   2450491 |    855624721
+ Mon Feb 10 17:32:01 1997 PST    |      1000000 |         1000 |       1 |   2450491 |    855624721
+ Mon Feb 10 17:32:01 1997 PST    |      1000000 |         1000 |       1 |   2450491 |    855624721
+ Tue Jun 10 17:32:01 1997 PDT    |      1000000 |         1000 |       1 |   2450611 |    865989121
+ Sat Sep 22 18:19:20 2001 PDT    |     20000000 |        20000 |      20 |   2452176 |   1001207960
+ Wed Mar 15 08:14:01 2000 PST    |      1000000 |         1000 |       1 |   2451619 |    953136841
+ Wed Mar 15 04:14:02 2000 PST    |      2000000 |         2000 |       2 |   2451619 |    953122442
+ Wed Mar 15 02:14:03 2000 PST    |      3000000 |         3000 |       3 |   2451619 |    953115243
+ Wed Mar 15 03:14:04 2000 PST    |      4000000 |         4000 |       4 |   2451619 |    953118844
+ Wed Mar 15 01:14:05 2000 PST    |      5000000 |         5000 |       5 |   2451619 |    953111645
+ Mon Feb 10 17:32:01 1997 PST    |      1000000 |         1000 |       1 |   2450491 |    855624721
+ Mon Feb 10 17:32:01 1997 PST    |      1000000 |         1000 |       1 |   2450491 |    855624721
+ Mon Feb 10 17:32:00 1997 PST    |            0 |            0 |       0 |   2450491 |    855624720
+ Mon Feb 10 17:32:01 1997 PST    |      1000000 |         1000 |       1 |   2450491 |    855624721
+ Mon Feb 10 17:32:01 1997 PST    |      1000000 |         1000 |       1 |   2450491 |    855624721
+ Mon Feb 10 17:32:01 1997 PST    |      1000000 |         1000 |       1 |   2450491 |    855624721
+ Mon Feb 10 17:32:01 1997 PST    |      1000000 |         1000 |       1 |   2450491 |    855624721
+ Mon Feb 10 17:32:01 1997 PST    |      1000000 |         1000 |       1 |   2450491 |    855624721
+ Mon Feb 10 09:32:01 1997 PST    |      1000000 |         1000 |       1 |   2450490 |    855595921
+ Mon Feb 10 09:32:01 1997 PST    |      1000000 |         1000 |       1 |   2450490 |    855595921
+ Mon Feb 10 09:32:01 1997 PST    |      1000000 |         1000 |       1 |   2450490 |    855595921
+ Mon Feb 10 14:32:01 1997 PST    |      1000000 |         1000 |       1 |   2450491 |    855613921
+ Thu Jul 10 14:32:01 1997 PDT    |      1000000 |         1000 |       1 |   2450641 |    868570321
+ Tue Jun 10 18:32:01 1997 PDT    |      1000000 |         1000 |       1 |   2450611 |    865992721
+ Mon Feb 10 17:32:01 1997 PST    |      1000000 |         1000 |       1 |   2450491 |    855624721
+ Tue Feb 11 17:32:01 1997 PST    |      1000000 |         1000 |       1 |   2450492 |    855711121
+ Wed Feb 12 17:32:01 1997 PST    |      1000000 |         1000 |       1 |   2450493 |    855797521
+ Thu Feb 13 17:32:01 1997 PST    |      1000000 |         1000 |       1 |   2450494 |    855883921
+ Fri Feb 14 17:32:01 1997 PST    |      1000000 |         1000 |       1 |   2450495 |    855970321
+ Sat Feb 15 17:32:01 1997 PST    |      1000000 |         1000 |       1 |   2450496 |    856056721
+ Sun Feb 16 17:32:01 1997 PST    |      1000000 |         1000 |       1 |   2450497 |    856143121
+ Tue Feb 16 17:32:01 0097 PST BC |      1000000 |         1000 |       1 |   1686043 | -65192682479
+ Sat Feb 16 17:32:01 0097 PST    |      1000000 |         1000 |       1 |   1756537 | -59102000879
+ Thu Feb 16 17:32:01 0597 PST    |      1000000 |         1000 |       1 |   1939158 | -43323546479
+ Tue Feb 16 17:32:01 1097 PST    |      1000000 |         1000 |       1 |   2121779 | -27545092079
+ Sat Feb 16 17:32:01 1697 PST    |      1000000 |         1000 |       1 |   2340925 |  -8610877679
+ Thu Feb 16 17:32:01 1797 PST    |      1000000 |         1000 |       1 |   2377449 |  -5455204079
+ Tue Feb 16 17:32:01 1897 PST    |      1000000 |         1000 |       1 |   2413973 |  -2299530479
+ Sun Feb 16 17:32:01 1997 PST    |      1000000 |         1000 |       1 |   2450497 |    856143121
+ Sat Feb 16 17:32:01 2097 PST    |      1000000 |         1000 |       1 |   2487022 |   4011903121
+ Wed Feb 28 17:32:01 1996 PST    |      1000000 |         1000 |       1 |   2450143 |    825557521
+ Thu Feb 29 17:32:01 1996 PST    |      1000000 |         1000 |       1 |   2450144 |    825643921
+ Fri Mar 01 17:32:01 1996 PST    |      1000000 |         1000 |       1 |   2450145 |    825730321
+ Mon Dec 30 17:32:01 1996 PST    |      1000000 |         1000 |       1 |   2450449 |    851995921
+ Tue Dec 31 17:32:01 1996 PST    |      1000000 |         1000 |       1 |   2450450 |    852082321
+ Wed Jan 01 17:32:01 1997 PST    |      1000000 |         1000 |       1 |   2450451 |    852168721
+ Fri Feb 28 17:32:01 1997 PST    |      1000000 |         1000 |       1 |   2450509 |    857179921
+ Sat Mar 01 17:32:01 1997 PST    |      1000000 |         1000 |       1 |   2450510 |    857266321
+ Tue Dec 30 17:32:01 1997 PST    |      1000000 |         1000 |       1 |   2450814 |    883531921
+ Wed Dec 31 17:32:01 1997 PST    |      1000000 |         1000 |       1 |   2450815 |    883618321
+ Fri Dec 31 17:32:01 1999 PST    |      1000000 |         1000 |       1 |   2451545 |    946690321
+ Sat Jan 01 17:32:01 2000 PST    |      1000000 |         1000 |       1 |   2451546 |    946776721
+ Sun Dec 31 17:32:01 2000 PST    |      1000000 |         1000 |       1 |   2451911 |    978312721
+ Mon Jan 01 17:32:01 2001 PST    |      1000000 |         1000 |       1 |   2451912 |    978399121
 (66 rows)

 -- value near upper bound uses special case in code
@@ -1326,9 +1326,9 @@ SELECT extract(epoch from '294270-01-01 00:00:00+00'::timestamptz);

 -- another internal overflow test case
 SELECT extract(epoch from '5000-01-01 00:00:00+00'::timestamptz);
-      extract
---------------------
- 95617584000.000000
+   extract
+-------------
+ 95617584000
 (1 row)

 -- test edge-case overflow in timestamp subtraction

> On 23 Mar 2023, at 19:31, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> This behavior change happened with commit a2da77cdb
> (Change return type of EXTRACT to numeric), and it was intentional
> according to the commit log:
>
>    - Return values when extracting fields with possibly fractional
>      values, such as second and epoch, now have the full scale that the
>      value has internally (so, for example, '1.000000' instead of just
>      '1').
>
> But exactly nothing was mentioned of that in user-facing docs.

Skimming the thread it's also not really discussed much AFAICT.  It's first
brought up in [0] as:

    When extracting seconds or microseconds, I made it always produce 6 or
    3 decimal places, even if they are zero.  I don't know if we want that
    or what behavior we want.  That's what all the changes in the
    regression tests are about.  Everything else passes unchanged.

There are no follow-ups to that though.

> I wonder if we should rethink that and have these operations strip
> insignificant trailing zeroes.

Any app relying on insignificant trailing zeroes seems broken, but the inverse
can be argued as well.  It's however quite easy to argue for the stripped
output being more readable though.

--
Daniel Gustafsson

[0] https://www.postgresql.org/message-id/a3be61d9-f44b-7fce-3dc8-d700fdfb6f48%402ndquadrant.com

Daniel Gustafsson <daniel@yesql.se> writes:
> On 23 Mar 2023, at 19:31, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I wonder if we should rethink that and have these operations strip
>> insignificant trailing zeroes.

> Any app relying on insignificant trailing zeroes seems broken, but the inverse
> can be argued as well.  It's however quite easy to argue for the stripped
> output being more readable though.

It's more readable for sure, and it duplicates what you got in pre-v14
versions, at least textually.  I'm not sure I'd propose back-patching
this, but it feels like a good idea for HEAD.

The argument for the current behavior probably goes like "we're exposing
the actual precision of the value".  But I don't believe that we are;
we're exposing the maximum possible precision.  We have no way to know
how precise the original timestamp or interval input was.  So I don't
think there's much basis for claiming that the result is good to six
fractional digits.

            regards, tom lane

> On 23 Mar 2023, at 22:54, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Daniel Gustafsson <daniel@yesql.se> writes:
>> On 23 Mar 2023, at 19:31, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> I wonder if we should rethink that and have these operations strip
>>> insignificant trailing zeroes.
>
>> Any app relying on insignificant trailing zeroes seems broken, but the inverse
>> can be argued as well.  It's however quite easy to argue for the stripped
>> output being more readable though.
>
> It's more readable for sure, and it duplicates what you got in pre-v14
> versions, at least textually.  I'm not sure I'd propose back-patching
> this, but it feels like a good idea for HEAD.

Agreed, I don't think this is material for backpatching.

--
Daniel Gustafsson

Daniel Gustafsson <daniel@yesql.se> writes:
> On 23 Mar 2023, at 22:54, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> It's more readable for sure, and it duplicates what you got in pre-v14
>> versions, at least textually.  I'm not sure I'd propose back-patching
>> this, but it feels like a good idea for HEAD.

> Agreed, I don't think this is material for backpatching.

Since we didn't get anything done about that for v16, I concluded
that adjusting the docs is a better idea.  If anyone takes this
idea up in v17 or beyond, they can always revert fbbd7edca at that
time.

            regards, tom lane

> On 10 Apr 2023, at 19:11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Daniel Gustafsson <daniel@yesql.se> writes:
>> On 23 Mar 2023, at 22:54, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> It's more readable for sure, and it duplicates what you got in pre-v14
>>> versions, at least textually.  I'm not sure I'd propose back-patching
>>> this, but it feels like a good idea for HEAD.
>
>> Agreed, I don't think this is material for backpatching.
>
> Since we didn't get anything done about that for v16, I concluded
> that adjusting the docs is a better idea.  If anyone takes this
> idea up in v17 or beyond, they can always revert fbbd7edca at that
> time.

The more I think about it the more I lean towards not changing anything
(besides the docs which you did).  It's been in two releases already and hardly
any complaints, so it seems to be have been quite well accepted.

--
Daniel Gustafsson