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

Commit3c59263

Browse files
committed
Avoid some table rewrites for ALTER TABLE .. SET DATA TYPE timestamp.
When the timezone is UTC, timestamptz and timestamp are binary coerciblein both directions. Seeb8a18ad andc22ecc6 for the previous attempt inthis problem space. Skip the table rewrite; for now, continue toneedlessly rewrite any index on an affected column.Reviewed by Simon Riggs and Tom Lane.Discussion:https://postgr.es/m/20190226061450.GA1665944@rfd.leadboat.com
1 parent82a5649 commit3c59263

File tree

5 files changed

+65
-7
lines changed

5 files changed

+65
-7
lines changed

‎src/backend/commands/tablecmds.c

Lines changed: 27 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -96,6 +96,7 @@
9696
#include"utils/ruleutils.h"
9797
#include"utils/snapmgr.h"
9898
#include"utils/syscache.h"
99+
#include"utils/timestamp.h"
99100
#include"utils/typcache.h"
100101

101102

@@ -9678,11 +9679,15 @@ ATPrepAlterColumnType(List **wqueue,
96789679
* When the data type of a column is changed, a rewrite might not be required
96799680
* if the new type is sufficiently identical to the old one, and the USING
96809681
* clause isn't trying to insert some other value. It's safe to skip the
9681-
* rewrite if the old type is binary coercible to the new type, or if the
9682-
* new type is an unconstrained domain over the old type. In the case of a
9683-
* constrained domain, we could get by with scanning the table and checking
9684-
* the constraint rather than actually rewriting it, but we don't currently
9685-
* try to do that.
9682+
* rewrite in these cases:
9683+
*
9684+
* - the old type is binary coercible to the new type
9685+
* - the new type is an unconstrained domain over the old type
9686+
* - {NEW,OLD} or {OLD,NEW} is {timestamptz,timestamp} and the timezone is UTC
9687+
*
9688+
* In the case of a constrained domain, we could get by with scanning the
9689+
* table and checking the constraint rather than actually rewriting it, but we
9690+
* don't currently try to do that.
96869691
*/
96879692
staticbool
96889693
ATColumnChangeRequiresRewrite(Node*expr,AttrNumbervarattno)
@@ -9704,6 +9709,23 @@ ATColumnChangeRequiresRewrite(Node *expr, AttrNumber varattno)
97049709
return true;
97059710
expr= (Node*)d->arg;
97069711
}
9712+
elseif (IsA(expr,FuncExpr))
9713+
{
9714+
FuncExpr*f= (FuncExpr*)expr;
9715+
9716+
switch (f->funcid)
9717+
{
9718+
caseF_TIMESTAMPTZ_TIMESTAMP:
9719+
caseF_TIMESTAMP_TIMESTAMPTZ:
9720+
if (TimestampTimestampTzRequiresRewrite())
9721+
return true;
9722+
else
9723+
expr=linitial(f->args);
9724+
break;
9725+
default:
9726+
return true;
9727+
}
9728+
}
97079729
else
97089730
return true;
97099731
}

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

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5168,6 +5168,23 @@ timestamp_izone(PG_FUNCTION_ARGS)
51685168
PG_RETURN_TIMESTAMPTZ(result);
51695169
}/* timestamp_izone() */
51705170

5171+
/* TimestampTimestampTzRequiresRewrite()
5172+
*
5173+
* Returns false if the TimeZone GUC setting causes timestamp_timestamptz and
5174+
* timestamptz_timestamp to be no-ops, where the return value has the same
5175+
* bits as the argument. Since project convention is to assume a GUC changes
5176+
* no more often than STABLE functions change, the answer is valid that long.
5177+
*/
5178+
bool
5179+
TimestampTimestampTzRequiresRewrite(void)
5180+
{
5181+
longoffset;
5182+
5183+
if (pg_get_timezone_offset(session_timezone,&offset)&&offset==0)
5184+
PG_RETURN_BOOL(false);
5185+
PG_RETURN_BOOL(true);
5186+
}
5187+
51715188
/* timestamp_timestamptz()
51725189
* Convert local timestamp to timestamp at GMT
51735190
*/

‎src/include/utils/timestamp.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -104,4 +104,6 @@ extern intdate2isoweek(int year, int mon, int mday);
104104
externintdate2isoyear(intyear,intmon,intmday);
105105
externintdate2isoyearday(intyear,intmon,intmday);
106106

107+
externboolTimestampTimestampTzRequiresRewrite(void);
108+
107109
#endif/* TIMESTAMP_H */

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

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -435,7 +435,7 @@ END;
435435
$$;
436436
create event trigger no_rewrite_allowed on table_rewrite
437437
execute procedure test_evtrig_no_rewrite();
438-
create table rewriteme (id serial primary key, foo float);
438+
create table rewriteme (id serial primary key, foo float, bar timestamptz);
439439
insert into rewriteme
440440
select x * 1.001 from generate_series(1, 500) as t(x);
441441
alter table rewriteme alter column foo type numeric;
@@ -458,6 +458,15 @@ alter table rewriteme
458458
NOTICE: Table 'rewriteme' is being rewritten (reason = 4)
459459
-- shouldn't trigger a table_rewrite event
460460
alter table rewriteme alter column foo type numeric(12,4);
461+
begin;
462+
set timezone to 'UTC';
463+
alter table rewriteme alter column bar type timestamp;
464+
set timezone to '0';
465+
alter table rewriteme alter column bar type timestamptz;
466+
set timezone to 'Europe/London';
467+
alter table rewriteme alter column bar type timestamp; -- does rewrite
468+
NOTICE: Table 'rewriteme' is being rewritten (reason = 4)
469+
rollback;
461470
-- typed tables are rewritten when their type changes. Don't emit table
462471
-- name, because firing order is not stable.
463472
CREATE OR REPLACE FUNCTION test_evtrig_no_rewrite() RETURNS event_trigger

‎src/test/regress/sql/event_trigger.sql

Lines changed: 9 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -329,7 +329,7 @@ $$;
329329
create event trigger no_rewrite_allowedon table_rewrite
330330
execute procedure test_evtrig_no_rewrite();
331331

332-
createtablerewriteme (idserialprimary key, foo float);
332+
createtablerewriteme (idserialprimary key, foo float, bartimestamptz);
333333
insert into rewriteme
334334
select x*1.001from generate_series(1,500)as t(x);
335335
altertable rewriteme alter column foo typenumeric;
@@ -352,6 +352,14 @@ alter table rewriteme
352352

353353
-- shouldn't trigger a table_rewrite event
354354
altertable rewriteme alter column foo typenumeric(12,4);
355+
begin;
356+
set timezone to'UTC';
357+
altertable rewriteme alter column bar typetimestamp;
358+
set timezone to'0';
359+
altertable rewriteme alter column bar typetimestamptz;
360+
set timezone to'Europe/London';
361+
altertable rewriteme alter column bar typetimestamp;-- does rewrite
362+
rollback;
355363

356364
-- typed tables are rewritten when their type changes. Don't emit table
357365
-- name, because firing order is not stable.

0 commit comments

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