From b8a18ad4850ea5ad7884aa6ab731fd392e73b4ad Mon Sep 17 00:00:00 2001 From: Noah Misch Date: Sun, 1 Mar 2015 13:22:34 -0500 Subject: [PATCH] Add transform functions for AT TIME ZONE. This makes "ALTER TABLE tabname ALTER tscol TYPE ... USING tscol AT TIME ZONE 'UTC'" skip rewriting the table when altering from "timestamp" to "timestamptz" or vice versa. While it would be nicer still to optimize this in the absence of the USING clause given timezone==UTC, transform functions must consult IMMUTABLE facts only. --- src/backend/utils/adt/timestamp.c | 128 ++++++++++++++++++++++++++++++ src/include/catalog/catversion.h | 2 +- src/include/catalog/pg_proc.h | 12 ++- src/include/utils/timestamp.h | 2 + 4 files changed, 139 insertions(+), 5 deletions(-) diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c index 67e0cf9543..723c67087e 100644 --- a/src/backend/utils/adt/timestamp.c +++ b/src/backend/utils/adt/timestamp.c @@ -27,6 +27,7 @@ #include "funcapi.h" #include "libpq/pqformat.h" #include "miscadmin.h" +#include "nodes/makefuncs.h" #include "nodes/nodeFuncs.h" #include "parser/scansup.h" #include "utils/array.h" @@ -4874,6 +4875,87 @@ interval_part(PG_FUNCTION_ARGS) } +/* timestamp_zone_transform() + * If the zone argument of a timestamp_zone() or timestamptz_zone() call is a + * plan-time constant denoting a zone equivalent to UTC, the call will always + * return its second argument unchanged. Simplify the expression tree + * accordingly. Civil time zones almost never qualify, because jurisdictions + * that follow UTC today have not done so continuously. + */ +Datum +timestamp_zone_transform(PG_FUNCTION_ARGS) +{ + Node *func_node = (Node *) PG_GETARG_POINTER(0); + FuncExpr *expr = (FuncExpr *) func_node; + Node *ret = NULL; + Node *zone_node; + + Assert(IsA(expr, FuncExpr)); + Assert(list_length(expr->args) == 2); + + zone_node = (Node *) linitial(expr->args); + + if (IsA(zone_node, Const) &&!((Const *) zone_node)->constisnull) + { + text *zone = DatumGetTextPP(((Const *) zone_node)->constvalue); + char tzname[TZ_STRLEN_MAX + 1]; + char *lowzone; + int type, + abbrev_offset; + pg_tz *tzp; + bool noop = false; + + /* + * If the timezone is forever UTC+0, the FuncExpr function call is a + * no-op for all possible timestamps. This passage mirrors code in + * timestamp_zone(). + */ + text_to_cstring_buffer(zone, tzname, sizeof(tzname)); + lowzone = downcase_truncate_identifier(tzname, + strlen(tzname), + false); + type = DecodeTimezoneAbbrev(0, lowzone, &abbrev_offset, &tzp); + if (type == TZ || type == DTZ) + noop = (abbrev_offset == 0); + else if (type == DYNTZ) + { + /* + * An abbreviation of a single-offset timezone ought not to be + * configured as a DYNTZ, so don't bother checking. + */ + } + else + { + long tzname_offset; + + tzp = pg_tzset(tzname); + if (tzp && pg_get_timezone_offset(tzp, &tzname_offset)) + noop = (tzname_offset == 0); + } + + if (noop) + { + Node *timestamp = (Node *) lsecond(expr->args); + + /* Strip any existing RelabelType node(s) */ + while (timestamp && IsA(timestamp, RelabelType)) + timestamp = (Node *) ((RelabelType *) timestamp)->arg; + + /* + * Replace the FuncExpr with its timestamp argument, relabeled as + * though the function call had computed it. + */ + ret = (Node *) makeRelabelType((Expr *) timestamp, + exprType(func_node), + exprTypmod(func_node), + exprCollation(func_node), + COERCE_EXPLICIT_CAST); + } + } + + PG_RETURN_POINTER(ret); +} + /* timestamp_zone() * Encode timestamp type with specified time zone. * This function is just timestamp2timestamptz() except instead of @@ -4963,6 +5045,52 @@ timestamp_zone(PG_FUNCTION_ARGS) PG_RETURN_TIMESTAMPTZ(result); } +/* timestamp_izone_transform() + * If we deduce at plan time that a particular timestamp_izone() or + * timestamptz_izone() call can only compute tz=0, the call will always return + * its second argument unchanged. Simplify the expression tree accordingly. + */ +Datum +timestamp_izone_transform(PG_FUNCTION_ARGS) +{ + Node *func_node = (Node *) PG_GETARG_POINTER(0); + FuncExpr *expr = (FuncExpr *) func_node; + Node *ret = NULL; + Node *zone_node; + + Assert(IsA(expr, FuncExpr)); + Assert(list_length(expr->args) == 2); + + zone_node = (Node *) linitial(expr->args); + + if (IsA(zone_node, Const) &&!((Const *) zone_node)->constisnull) + { + Interval *zone; + + zone = DatumGetIntervalP(((Const *) zone_node)->constvalue); + if (zone->month == 0 && zone->day == 0 && zone->time == 0) + { + Node *timestamp = (Node *) lsecond(expr->args); + + /* Strip any existing RelabelType node(s) */ + while (timestamp && IsA(timestamp, RelabelType)) + timestamp = (Node *) ((RelabelType *) timestamp)->arg; + + /* + * Replace the FuncExpr with its timestamp argument, relabeled as + * though the function call had computed it. + */ + ret = (Node *) makeRelabelType((Expr *) timestamp, + exprType(func_node), + exprTypmod(func_node), + exprCollation(func_node), + COERCE_EXPLICIT_CAST); + } + } + + PG_RETURN_POINTER(ret); +} + /* timestamp_izone() * Encode timestamp type with specified time interval as time zone. */ diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 7ed743df97..508741fd44 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 201502221 +#define CATALOG_VERSION_NO 201503011 #endif diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 4268b99416..b8a3660122 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -1155,7 +1155,9 @@ DATA(insert OID = 999 ( lseg_eq PGNSP PGUID 12 1 0 0 0 f f f t t f i 2 0 16 /* OIDS 1000 - 1999 */ -DATA(insert OID = 1026 ( timezone PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 1114 "1186 1184" _null_ _null_ _null_ _null_ timestamptz_izone _null_ _null_ _null_ )); +DATA(insert OID = 3994 ( timestamp_izone_transform PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 2281 "2281" _null_ _null_ _null_ _null_ timestamp_izone_transform _null_ _null_ _null_ )); +DESCR("transform a time zone adjustment"); +DATA(insert OID = 1026 ( timezone PGNSP PGUID 12 1 0 0 timestamp_izone_transform f f f f t f i 2 0 1114 "1186 1184" _null_ _null_ _null_ _null_ timestamptz_izone _null_ _null_ _null_ )); DESCR("adjust timestamp to new time zone"); DATA(insert OID = 1031 ( aclitemin PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 1033 "2275" _null_ _null_ _null_ _null_ aclitemin _null_ _null_ _null_ )); @@ -1269,7 +1271,9 @@ DATA(insert OID = 1156 ( timestamptz_ge PGNSP PGUID 12 1 0 0 0 f f f t t f i DATA(insert OID = 1157 ( timestamptz_gt PGNSP PGUID 12 1 0 0 0 f f f t t f i 2 0 16 "1184 1184" _null_ _null_ _null_ _null_ timestamp_gt _null_ _null_ _null_ )); DATA(insert OID = 1158 ( to_timestamp PGNSP PGUID 14 1 0 0 0 f f f f t f i 1 0 1184 "701" _null_ _null_ _null_ _null_ "select (''epoch''::pg_catalog.timestamptz + $1 * ''1 second''::pg_catalog.interval)" _null_ _null_ _null_ )); DESCR("convert UNIX epoch to timestamptz"); -DATA(insert OID = 1159 ( timezone PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 1114 "25 1184" _null_ _null_ _null_ _null_ timestamptz_zone _null_ _null_ _null_ )); +DATA(insert OID = 3995 ( timestamp_zone_transform PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 2281 "2281" _null_ _null_ _null_ _null_ timestamp_zone_transform _null_ _null_ _null_ )); +DESCR("transform a time zone adjustment"); +DATA(insert OID = 1159 ( timezone PGNSP PGUID 12 1 0 0 timestamp_zone_transform f f f f t f i 2 0 1114 "25 1184" _null_ _null_ _null_ _null_ timestamptz_zone _null_ _null_ _null_ )); DESCR("adjust timestamp to new time zone"); DATA(insert OID = 1160 ( interval_in PGNSP PGUID 12 1 0 0 0 f f f f t f s 3 0 1186 "2275 26 23" _null_ _null_ _null_ _null_ interval_in _null_ _null_ _null_ )); @@ -2996,9 +3000,9 @@ DESCR("date difference preserving months and years"); DATA(insert OID = 2059 ( age PGNSP PGUID 14 1 0 0 0 f f f f t f s 1 0 1186 "1114" _null_ _null_ _null_ _null_ "select pg_catalog.age(cast(current_date as timestamp without time zone), $1)" _null_ _null_ _null_ )); DESCR("date difference from today preserving months and years"); -DATA(insert OID = 2069 ( timezone PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 1184 "25 1114" _null_ _null_ _null_ _null_ timestamp_zone _null_ _null_ _null_ )); +DATA(insert OID = 2069 ( timezone PGNSP PGUID 12 1 0 0 timestamp_zone_transform f f f f t f i 2 0 1184 "25 1114" _null_ _null_ _null_ _null_ timestamp_zone _null_ _null_ _null_ )); DESCR("adjust timestamp to new time zone"); -DATA(insert OID = 2070 ( timezone PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 1184 "1186 1114" _null_ _null_ _null_ _null_ timestamp_izone _null_ _null_ _null_ )); +DATA(insert OID = 2070 ( timezone PGNSP PGUID 12 1 0 0 timestamp_izone_transform f f f f t f i 2 0 1184 "1186 1114" _null_ _null_ _null_ _null_ timestamp_izone _null_ _null_ _null_ )); DESCR("adjust timestamp to new time zone"); DATA(insert OID = 2071 ( date_pl_interval PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 1114 "1082 1186" _null_ _null_ _null_ _null_ date_pl_interval _null_ _null_ _null_ )); DATA(insert OID = 2072 ( date_mi_interval PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 1114 "1082 1186" _null_ _null_ _null_ _null_ date_mi_interval _null_ _null_ _null_ )); diff --git a/src/include/utils/timestamp.h b/src/include/utils/timestamp.h index 70118f599c..530fef1915 100644 --- a/src/include/utils/timestamp.h +++ b/src/include/utils/timestamp.h @@ -161,7 +161,9 @@ extern Datum timestamp_trunc(PG_FUNCTION_ARGS); extern Datum interval_trunc(PG_FUNCTION_ARGS); extern Datum timestamp_part(PG_FUNCTION_ARGS); extern Datum interval_part(PG_FUNCTION_ARGS); +extern Datum timestamp_zone_transform(PG_FUNCTION_ARGS); extern Datum timestamp_zone(PG_FUNCTION_ARGS); +extern Datum timestamp_izone_transform(PG_FUNCTION_ARGS); extern Datum timestamp_izone(PG_FUNCTION_ARGS); extern Datum timestamp_timestamptz(PG_FUNCTION_ARGS); -- 2.40.0