Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitb8a18ad

Browse files
committed
Add transform functions for AT TIME ZONE.
This makes "ALTER TABLE tabname ALTER tscol TYPE ... USING tscol AT TIMEZONE 'UTC'" skip rewriting the table when altering from "timestamp" to"timestamptz" or vice versa. While it would be nicer still to optimizethis in the absence of the USING clause given timezone==UTC, transformfunctions must consult IMMUTABLE facts only.
1 parent424793f commitb8a18ad

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+
chartzname[TZ_STRLEN_MAX+1];
4902+
char*lowzone;
4903+
inttype,
4904+
abbrev_offset;
4905+
pg_tz*tzp;
4906+
boolnoop= 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+
elseif (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+
longtzname_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-
#defineCATALOG_VERSION_NO201502221
56+
#defineCATALOG_VERSION_NO201503011
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 ( agePGNSP 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 ( timezonePGNSP PGUID 12 1 0 00 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 ( timezonePGNSP PGUID 12 1 0 0timestamp_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 ( timezonePGNSP PGUID 12 1 0 00 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 ( timezonePGNSP PGUID 12 1 0 0timestamp_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_intervalPGNSP 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_intervalPGNSP 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
externDatuminterval_trunc(PG_FUNCTION_ARGS);
162162
externDatumtimestamp_part(PG_FUNCTION_ARGS);
163163
externDatuminterval_part(PG_FUNCTION_ARGS);
164+
externDatumtimestamp_zone_transform(PG_FUNCTION_ARGS);
164165
externDatumtimestamp_zone(PG_FUNCTION_ARGS);
166+
externDatumtimestamp_izone_transform(PG_FUNCTION_ARGS);
165167
externDatumtimestamp_izone(PG_FUNCTION_ARGS);
166168
externDatumtimestamp_timestamptz(PG_FUNCTION_ARGS);
167169

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp