selfuncs.c convert_to_scalar() says:
|* The several datatypes representing absolute times are all converted
|* to Timestamp, which is actually a double, and then we just use that
|* double value. Note this will give correct results even for the "special"
|* values of Timestamp, since those are chosen to compare correctly;
|* see timestamp_cmp.
But:
https://www.postgresql.org/docs/10/release-10.html
|Remove support for floating-point timestamps and intervals (Tom Lane)
|This removes configure's --disable-integer-datetimes option. Floating-point
timestamps have few advantages and have not been the default since PostgreSQL
8.3.
|b6aa17e De-support floating-point timestamps.
|configure | 18
++++++------------
|configure.in | 12 ++++++------
|doc/src/sgml/config.sgml | 8 +++-----
|doc/src/sgml/datatype.sgml | 55
+++++++++++--------------------------------------------
|doc/src/sgml/installation.sgml | 22
----------------------
|src/include/c.h | 7 ++++---
|src/include/pg_config.h.in | 4 ----
|src/include/pg_config.h.win32 | 4 ----
|src/interfaces/ecpg/include/ecpg_config.h.in | 4 ----
|src/interfaces/ecpg/include/pgtypes_interval.h | 2 --
|src/interfaces/ecpg/test/expected/pgtypeslib-dt_test2.c | 6 ++----
|src/interfaces/ecpg/test/expected/pgtypeslib-dt_test2.stdout | 2 ++
|src/interfaces/ecpg/test/pgtypeslib/dt_test2.pgc | 6 ++----
|src/tools/msvc/Solution.pm | 9 ---------
|src/tools/msvc/config_default.pl | 1 -
|15 files changed, 36 insertions(+), 124 deletions(-)
It's true that convert_to_scalar sees doubles:
|static double
|convert_timevalue_to_scalar(Datum value, Oid typid, bool *failure)
|{
| switch (typid)
| {
| case TIMESTAMPOID:
| return DatumGetTimestamp(value);
But:
$ git grep DatumGetTimestamp src/include/
src/include/utils/timestamp.h:#define DatumGetTimestamp(X) ((Timestamp)
DatumGetInt64(X))
So I propose it should say something like:
|* The several datatypes representing absolute times are all converted
|* to Timestamp, which is actually an int64, and then we just promote that
|* to double. Note this will give correct results even for the "special"
|* values of Timestamp, since those are chosen to compare correctly;
|* see timestamp_cmp.
That seems to be only used for ineq_histogram_selectivity() interpolation of
histogram bins. It looks to me that at least isn't working for "special
values", and needs to use something other than isnan(). I added debugging code
and tested the attached like:
DROP TABLE t; CREATE TABLE t(t) AS SELECT generate_series(now(), now()+'1 day',
'5 minutes');
INSERT INTO t VALUES('-infinity');
ALTER TABLE t ALTER t SET STATISTICS 1;
ANALYZE t;
explain SELECT * FROM t WHERE t>='2010-12-29';
>From b0151e24819499607eb2894dd920d4d8ef74b57d Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Mon, 30 Dec 2019 01:37:42 -0600
Subject: [PATCH v1 1/2] Correctly handle infinite timestamps in
ineq_histogram_selectivity
---
src/backend/utils/adt/selfuncs.c | 30 ++++++++++++++++++++++--------
1 file changed, 22 insertions(+), 8 deletions(-)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 3f77c7e..73e2359 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -4284,14 +4284,19 @@ convert_one_bytea_to_scalar(unsigned char *value, int valuelen,
static double
convert_timevalue_to_scalar(Datum value, Oid typid, bool *failure)
{
+ double ret;
+
switch (typid)
{
case TIMESTAMPOID:
- return DatumGetTimestamp(value);
+ ret = DatumGetTimestamp(value);
+ break;
case TIMESTAMPTZOID:
- return DatumGetTimestampTz(value);
+ ret = DatumGetTimestampTz(value);
+ break;
case DATEOID:
- return date2timestamp_no_overflow(DatumGetDateADT(value));
+ ret = date2timestamp_no_overflow(DatumGetDateADT(value));
+ break;
case INTERVALOID:
{
Interval *interval = DatumGetIntervalP(value);
@@ -4301,22 +4306,31 @@ convert_timevalue_to_scalar(Datum value, Oid typid, bool *failure)
* average month length of 365.25/12.0 days. Not too
* accurate, but plenty good enough for our purposes.
*/
- return interval->time + interval->day * (double) USECS_PER_DAY +
+ ret = interval->time + interval->day * (double) USECS_PER_DAY +
interval->month * ((DAYS_PER_YEAR / (double) MONTHS_PER_YEAR) * USECS_PER_DAY);
}
+ break;
case TIMEOID:
- return DatumGetTimeADT(value);
+ ret = DatumGetTimeADT(value);
+ break;
case TIMETZOID:
{
TimeTzADT *timetz = DatumGetTimeTzADTP(value);
/* use GMT-equivalent time */
- return (double) (timetz->time + (timetz->zone * 1000000.0));
+ ret = (double) (timetz->time + (timetz->zone * 1000000.0));
}
+ break;
+ default:
+ *failure = true;
+ return 0;
}
- *failure = true;
- return 0;
+ if (ret==PG_INT64_MIN ||
+ ret==PG_INT64_MAX)
+ ret = nan(""); /* C99 */
+
+ return ret;
}
--
2.7.4
>From 5df640e0401f073df59faa3f118c8d195976ee9e Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Mon, 30 Dec 2019 01:43:40 -0600
Subject: [PATCH v1 2/2] Repair comment regarding double timestamps
..which were removed in v10 by b6aa17e.
---
src/backend/utils/adt/selfuncs.c | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 73e2359..15b9382 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -3763,8 +3763,8 @@ estimate_multivariate_ndistinct(PlannerInfo *root, RelOptInfo *rel,
* to be treated separately.
*
* The several datatypes representing absolute times are all converted
- * to Timestamp, which is actually a double, and then we just use that
- * double value. Note this will give correct results even for the "special"
+ * to Timestamp, which is actually an int64, which is promoted to
+ * double. Note this will give correct results even for the "special"
* values of Timestamp, since those are chosen to compare correctly;
* see timestamp_cmp.
*
--
2.7.4
