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

Commitc22ecc6

Browse files
committed
Disable transforms that replaced AT TIME ZONE with RelabelType.
These resulted in wrong answers if the relabeled argument could be matchedto an index column, as shown in bug #14504 from Evgeniy Kozlov. We mightbe able to resurrect these optimizations by adjusting the planner'streatment of RelabelType, or by adjusting btree's rules for selectingcomparison functions, but either solution will take careful analysisand does not sound like a fit candidate for backpatching.I left the catalog infrastructure in place and just reduced the transformfunctions to always-return-NULL. This would be necessary anyway in theback branches, and it doesn't seem important to be more invasive in HEAD.Bug introduced by commitb8a18ad. Back-patch to 9.5 where that came in.Report:https://postgr.es/m/20170118144828.1432.52823@wrigleys.postgresql.orgDiscussion:https://postgr.es/m/18771.1484759439@sss.pgh.pa.us
1 parente509e7f commitc22ecc6

File tree

3 files changed

+38
-113
lines changed

3 files changed

+38
-113
lines changed

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

Lines changed: 10 additions & 113 deletions
Original file line numberDiff line numberDiff line change
@@ -5202,84 +5202,15 @@ interval_part(PG_FUNCTION_ARGS)
52025202

52035203

52045204
/* timestamp_zone_transform()
5205-
* If the zone argument of a timestamp_zone() or timestamptz_zone() call is a
5206-
* plan-time constant denoting a zone equivalent to UTC, the call will always
5207-
* return its second argument unchanged. Simplify the expression tree
5208-
* accordingly. Civil time zones almost never qualify, because jurisdictions
5209-
* that follow UTC today have not done so continuously.
5205+
* The original optimization here caused problems by relabeling Vars that
5206+
* could be matched to index entries. It might be possible to resurrect it
5207+
* at some point by teaching the planner to be less cavalier with RelabelType
5208+
* nodes, but that will take careful analysis.
52105209
*/
52115210
Datum
52125211
timestamp_zone_transform(PG_FUNCTION_ARGS)
52135212
{
5214-
Node*func_node= (Node*)PG_GETARG_POINTER(0);
5215-
FuncExpr*expr= (FuncExpr*)func_node;
5216-
Node*ret=NULL;
5217-
Node*zone_node;
5218-
5219-
Assert(IsA(expr,FuncExpr));
5220-
Assert(list_length(expr->args)==2);
5221-
5222-
zone_node= (Node*)linitial(expr->args);
5223-
5224-
if (IsA(zone_node,Const)&&!((Const*)zone_node)->constisnull)
5225-
{
5226-
text*zone=DatumGetTextPP(((Const*)zone_node)->constvalue);
5227-
chartzname[TZ_STRLEN_MAX+1];
5228-
char*lowzone;
5229-
inttype,
5230-
abbrev_offset;
5231-
pg_tz*tzp;
5232-
boolnoop= false;
5233-
5234-
/*
5235-
* If the timezone is forever UTC+0, the FuncExpr function call is a
5236-
* no-op for all possible timestamps. This passage mirrors code in
5237-
* timestamp_zone().
5238-
*/
5239-
text_to_cstring_buffer(zone,tzname,sizeof(tzname));
5240-
lowzone=downcase_truncate_identifier(tzname,
5241-
strlen(tzname),
5242-
false);
5243-
type=DecodeTimezoneAbbrev(0,lowzone,&abbrev_offset,&tzp);
5244-
if (type==TZ||type==DTZ)
5245-
noop= (abbrev_offset==0);
5246-
elseif (type==DYNTZ)
5247-
{
5248-
/*
5249-
* An abbreviation of a single-offset timezone ought not to be
5250-
* configured as a DYNTZ, so don't bother checking.
5251-
*/
5252-
}
5253-
else
5254-
{
5255-
longtzname_offset;
5256-
5257-
tzp=pg_tzset(tzname);
5258-
if (tzp&&pg_get_timezone_offset(tzp,&tzname_offset))
5259-
noop= (tzname_offset==0);
5260-
}
5261-
5262-
if (noop)
5263-
{
5264-
Node*timestamp= (Node*)lsecond(expr->args);
5265-
5266-
/* Strip any existing RelabelType node(s) */
5267-
while (timestamp&&IsA(timestamp,RelabelType))
5268-
timestamp= (Node*) ((RelabelType*)timestamp)->arg;
5269-
5270-
/*
5271-
* Replace the FuncExpr with its timestamp argument, relabeled as
5272-
* though the function call had computed it.
5273-
*/
5274-
ret= (Node*)makeRelabelType((Expr*)timestamp,
5275-
exprType(func_node),
5276-
exprTypmod(func_node),
5277-
exprCollation(func_node),
5278-
COERCE_EXPLICIT_CAST);
5279-
}
5280-
}
5281-
5282-
PG_RETURN_POINTER(ret);
5213+
PG_RETURN_POINTER(NULL);
52835214
}
52845215

52855216
/*timestamp_zone()
@@ -5376,49 +5307,15 @@ timestamp_zone(PG_FUNCTION_ARGS)
53765307
}
53775308

53785309
/* timestamp_izone_transform()
5379-
* If we deduce at plan time that a particular timestamp_izone() or
5380-
* timestamptz_izone() call can only compute tz=0, the call will always return
5381-
* its second argument unchanged. Simplify the expression tree accordingly.
5310+
* The original optimization here caused problems by relabeling Vars that
5311+
* could be matched to index entries. It might be possible to resurrect it
5312+
* at some point by teaching the planner to be less cavalier with RelabelType
5313+
* nodes, but that will take careful analysis.
53825314
*/
53835315
Datum
53845316
timestamp_izone_transform(PG_FUNCTION_ARGS)
53855317
{
5386-
Node*func_node= (Node*)PG_GETARG_POINTER(0);
5387-
FuncExpr*expr= (FuncExpr*)func_node;
5388-
Node*ret=NULL;
5389-
Node*zone_node;
5390-
5391-
Assert(IsA(expr,FuncExpr));
5392-
Assert(list_length(expr->args)==2);
5393-
5394-
zone_node= (Node*)linitial(expr->args);
5395-
5396-
if (IsA(zone_node,Const)&&!((Const*)zone_node)->constisnull)
5397-
{
5398-
Interval*zone;
5399-
5400-
zone=DatumGetIntervalP(((Const*)zone_node)->constvalue);
5401-
if (zone->month==0&&zone->day==0&&zone->time==0)
5402-
{
5403-
Node*timestamp= (Node*)lsecond(expr->args);
5404-
5405-
/* Strip any existing RelabelType node(s) */
5406-
while (timestamp&&IsA(timestamp,RelabelType))
5407-
timestamp= (Node*) ((RelabelType*)timestamp)->arg;
5408-
5409-
/*
5410-
* Replace the FuncExpr with its timestamp argument, relabeled as
5411-
* though the function call had computed it.
5412-
*/
5413-
ret= (Node*)makeRelabelType((Expr*)timestamp,
5414-
exprType(func_node),
5415-
exprTypmod(func_node),
5416-
exprCollation(func_node),
5417-
COERCE_EXPLICIT_CAST);
5418-
}
5419-
}
5420-
5421-
PG_RETURN_POINTER(ret);
5318+
PG_RETURN_POINTER(NULL);
54225319
}
54235320

54245321
/* timestamp_izone()

‎src/test/regress/expected/timestamptz.out

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2638,3 +2638,22 @@ select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs;
26382638
t
26392639
(1 row)
26402640

2641+
--
2642+
-- Test that AT TIME ZONE isn't misoptimized when using an index (bug #14504)
2643+
--
2644+
create temp table tmptz (f1 timestamptz primary key);
2645+
insert into tmptz values ('2017-01-18 00:00+00');
2646+
explain (costs off)
2647+
select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
2648+
QUERY PLAN
2649+
-------------------------------------------------------------------------------------------------
2650+
Seq Scan on tmptz
2651+
Filter: (timezone('utc'::text, f1) = 'Wed Jan 18 00:00:00 2017'::timestamp without time zone)
2652+
(2 rows)
2653+
2654+
select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
2655+
f1
2656+
------------------------------
2657+
Tue Jan 17 16:00:00 2017 PST
2658+
(1 row)
2659+

‎src/test/regress/sql/timestamptz.sql

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -484,3 +484,12 @@ set timezone_abbreviations = 'Australia';
484484
selectcount(distinct utc_offset)>=24as okfrom pg_timezone_abbrevs;
485485
set timezone_abbreviations='India';
486486
selectcount(distinct utc_offset)>=24as okfrom pg_timezone_abbrevs;
487+
488+
--
489+
-- Test that AT TIME ZONE isn't misoptimized when using an index (bug #14504)
490+
--
491+
create temp table tmptz (f1timestamptzprimary key);
492+
insert into tmptzvalues ('2017-01-18 00:00+00');
493+
explain (costs off)
494+
select*from tmptzwhere f1 attime zone'utc'='2017-01-18 00:00';
495+
select*from tmptzwhere f1 attime zone'utc'='2017-01-18 00:00';

0 commit comments

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