Skip to content

Commit b8a18ad

Browse files
nmischnmisch
committed
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.
1 parent 424793f commit b8a18ad

File tree

4 files changed

+139
-5
lines changed

4 files changed

+139
-5
lines changed

‎src/backend/utils/adt/timestamp.c

Lines changed: 128 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -27,6 +27,7 @@
2727
#include "funcapi.h"
2828
#include "libpq/pqformat.h"
2929
#include "miscadmin.h"
30+
#include "nodes/makefuncs.h"
3031
#include "nodes/nodeFuncs.h"
3132
#include "parser/scansup.h"
3233
#include "utils/array.h"
@@ -4874,6 +4875,87 @@ interval_part(PG_FUNCTION_ARGS)
48744875
}
48754876

48764877

4878+
/* timestamp_zone_transform()
4879+
* If the zone argument of a timestamp_zone() or timestamptz_zone() call is a
4880+
* plan-time constant denoting a zone equivalent to UTC, the call will always
4881+
* return its second argument unchanged. Simplify the expression tree
4882+
* accordingly. Civil time zones almost never qualify, because jurisdictions
4883+
* that follow UTC today have not done so continuously.
4884+
*/
4885+
Datum
4886+
timestamp_zone_transform(PG_FUNCTION_ARGS)
4887+
{
4888+
Node *func_node = (Node *) PG_GETARG_POINTER(0);
4889+
FuncExpr *expr = (FuncExpr *) func_node;
4890+
Node *ret = NULL;
4891+
Node *zone_node;
4892+
4893+
Assert(IsA(expr, FuncExpr));
4894+
Assert(list_length(expr->args) == 2);
4895+
4896+
zone_node = (Node *) linitial(expr->args);
4897+
4898+
if (IsA(zone_node, Const) &&!((Const *) zone_node)->constisnull)
4899+
{
4900+
text *zone = DatumGetTextPP(((Const *) zone_node)->constvalue);
4901+
char tzname[TZ_STRLEN_MAX + 1];
4902+
char *lowzone;
4903+
int type,
4904+
abbrev_offset;
4905+
pg_tz *tzp;
4906+
bool noop = false;
4907+
4908+
/*
4909+
* If the timezone is forever UTC+0, the FuncExpr function call is a
4910+
* no-op for all possible timestamps. This passage mirrors code in
4911+
* timestamp_zone().
4912+
*/
4913+
text_to_cstring_buffer(zone, tzname, sizeof(tzname));
4914+
lowzone = downcase_truncate_identifier(tzname,
4915+
strlen(tzname),
4916+
false);
4917+
type = DecodeTimezoneAbbrev(0, lowzone, &abbrev_offset, &tzp);
4918+
if (type == TZ || type == DTZ)
4919+
noop = (abbrev_offset == 0);
4920+
else if (type == DYNTZ)
4921+
{
4922+
/*
4923+
* An abbreviation of a single-offset timezone ought not to be
4924+
* configured as a DYNTZ, so don't bother checking.
4925+
*/
4926+
}
4927+
else
4928+
{
4929+
long tzname_offset;
4930+
4931+
tzp = pg_tzset(tzname);
4932+
if (tzp && pg_get_timezone_offset(tzp, &tzname_offset))
4933+
noop = (tzname_offset == 0);
4934+
}
4935+
4936+
if (noop)
4937+
{
4938+
Node *timestamp = (Node *) lsecond(expr->args);
4939+
4940+
/* Strip any existing RelabelType node(s) */
4941+
while (timestamp && IsA(timestamp, RelabelType))
4942+
timestamp = (Node *) ((RelabelType *) timestamp)->arg;
4943+
4944+
/*
4945+
* Replace the FuncExpr with its timestamp argument, relabeled as
4946+
* though the function call had computed it.
4947+
*/
4948+
ret = (Node *) makeRelabelType((Expr *) timestamp,
4949+
exprType(func_node),
4950+
exprTypmod(func_node),
4951+
exprCollation(func_node),
4952+
COERCE_EXPLICIT_CAST);
4953+
}
4954+
}
4955+
4956+
PG_RETURN_POINTER(ret);
4957+
}
4958+
48774959
/* timestamp_zone()
48784960
* Encode timestamp type with specified time zone.
48794961
* This function is just timestamp2timestamptz() except instead of
@@ -4963,6 +5045,52 @@ timestamp_zone(PG_FUNCTION_ARGS)
49635045
PG_RETURN_TIMESTAMPTZ(result);
49645046
}
49655047

5048+
/* timestamp_izone_transform()
5049+
* If we deduce at plan time that a particular timestamp_izone() or
5050+
* timestamptz_izone() call can only compute tz=0, the call will always return
5051+
* its second argument unchanged. Simplify the expression tree accordingly.
5052+
*/
5053+
Datum
5054+
timestamp_izone_transform(PG_FUNCTION_ARGS)
5055+
{
5056+
Node *func_node = (Node *) PG_GETARG_POINTER(0);
5057+
FuncExpr *expr = (FuncExpr *) func_node;
5058+
Node *ret = NULL;
5059+
Node *zone_node;
5060+
5061+
Assert(IsA(expr, FuncExpr));
5062+
Assert(list_length(expr->args) == 2);
5063+
5064+
zone_node = (Node *) linitial(expr->args);
5065+
5066+
if (IsA(zone_node, Const) &&!((Const *) zone_node)->constisnull)
5067+
{
5068+
Interval *zone;
5069+
5070+
zone = DatumGetIntervalP(((Const *) zone_node)->constvalue);
5071+
if (zone->month == 0 && zone->day == 0 && zone->time == 0)
5072+
{
5073+
Node *timestamp = (Node *) lsecond(expr->args);
5074+
5075+
/* Strip any existing RelabelType node(s) */
5076+
while (timestamp && IsA(timestamp, RelabelType))
5077+
timestamp = (Node *) ((RelabelType *) timestamp)->arg;
5078+
5079+
/*
5080+
* Replace the FuncExpr with its timestamp argument, relabeled as
5081+
* though the function call had computed it.
5082+
*/
5083+
ret = (Node *) makeRelabelType((Expr *) timestamp,
5084+
exprType(func_node),
5085+
exprTypmod(func_node),
5086+
exprCollation(func_node),
5087+
COERCE_EXPLICIT_CAST);
5088+
}
5089+
}
5090+
5091+
PG_RETURN_POINTER(ret);
5092+
}
5093+
49665094
/* timestamp_izone()
49675095
* Encode timestamp type with specified time interval as time zone.
49685096
*/

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 201502221
56+
#define CATALOG_VERSION_NO 201503011
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 8 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -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
11551155

11561156
/* OIDS 1000 - 1999 */
11571157

1158-
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_ ));
1158+
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_ ));
1159+
DESCR("transform a time zone adjustment");
1160+
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_ ));
11591161
DESCR("adjust timestamp to new time zone");
11601162

11611163
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
12691271
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_ ));
12701272
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_ ));
12711273
DESCR("convert UNIX epoch to timestamptz");
1272-
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_ ));
1274+
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_ ));
1275+
DESCR("transform a time zone adjustment");
1276+
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_ ));
12731277
DESCR("adjust timestamp to new time zone");
12741278

12751279
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");
29963000
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_ ));
29973001
DESCR("date difference from today preserving months and years");
29983002

2999-
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_ ));
3003+
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_ ));
30003004
DESCR("adjust timestamp to new time zone");
3001-
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_ ));
3005+
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_ ));
30023006
DESCR("adjust timestamp to new time zone");
30033007
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_ ));
30043008
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_ ));

‎src/include/utils/timestamp.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -161,7 +161,9 @@ extern Datum timestamp_trunc(PG_FUNCTION_ARGS);
161161
extern Datum interval_trunc(PG_FUNCTION_ARGS);
162162
extern Datum timestamp_part(PG_FUNCTION_ARGS);
163163
extern Datum interval_part(PG_FUNCTION_ARGS);
164+
extern Datum timestamp_zone_transform(PG_FUNCTION_ARGS);
164165
extern Datum timestamp_zone(PG_FUNCTION_ARGS);
166+
extern Datum timestamp_izone_transform(PG_FUNCTION_ARGS);
165167
extern Datum timestamp_izone(PG_FUNCTION_ARGS);
166168
extern Datum timestamp_timestamptz(PG_FUNCTION_ARGS);
167169

0 commit comments

Comments
 (0)