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

Commit0a87ddf

Browse files
committed
Cache the result of converting now() to a struct pg_tm.
SQL operations such as CURRENT_DATE, CURRENT_TIME, LOCALTIME, andconversion of "now" in a datetime input string have to obtain thetransaction start timestamp ("now()") as a broken-down struct pg_tm.This is a remarkably expensive conversion, and since now() does notchange intra-transaction, it doesn't really need to be done more thanonce per transaction. Introducing a simple cache provides visiblespeedups in queries that compute these values many times, for exampleinsertion of many rows that use a default value of CURRENT_DATE.Peter Smith, with a bit of kibitzing by meDiscussion:https://postgr.es/m/CAHut+Pu89TWjq530V2gY5O6SWi=OEJMQ_VHMt8bdZB_9JFna5A@mail.gmail.com
1 parente21cbb4 commit0a87ddf

File tree

2 files changed

+80
-36
lines changed

2 files changed

+80
-36
lines changed

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

Lines changed: 24 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -299,20 +299,31 @@ EncodeSpecialDate(DateADT dt, char *str)
299299
DateADT
300300
GetSQLCurrentDate(void)
301301
{
302-
TimestampTzts;
303-
structpg_tmtt,
304-
*tm=&tt;
305-
fsec_tfsec;
306-
inttz;
302+
structpg_tmtm;
307303

308-
ts=GetCurrentTransactionStartTimestamp();
304+
staticintcache_year=0;
305+
staticintcache_mon=0;
306+
staticintcache_mday=0;
307+
staticDateADTcache_date;
309308

310-
if (timestamp2tm(ts,&tz,tm,&fsec,NULL,NULL)!=0)
311-
ereport(ERROR,
312-
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
313-
errmsg("timestamp out of range")));
309+
GetCurrentDateTime(&tm);
314310

315-
returndate2j(tm->tm_year,tm->tm_mon,tm->tm_mday)-POSTGRES_EPOCH_JDATE;
311+
/*
312+
* date2j involves several integer divisions; moreover, unless our session
313+
* lives across local midnight, we don't really have to do it more than
314+
* once. So it seems worth having a separate cache here.
315+
*/
316+
if (tm.tm_year!=cache_year||
317+
tm.tm_mon!=cache_mon||
318+
tm.tm_mday!=cache_mday)
319+
{
320+
cache_date=date2j(tm.tm_year,tm.tm_mon,tm.tm_mday)-POSTGRES_EPOCH_JDATE;
321+
cache_year=tm.tm_year;
322+
cache_mon=tm.tm_mon;
323+
cache_mday=tm.tm_mday;
324+
}
325+
326+
returncache_date;
316327
}
317328

318329
/*
@@ -322,18 +333,12 @@ TimeTzADT *
322333
GetSQLCurrentTime(int32typmod)
323334
{
324335
TimeTzADT*result;
325-
TimestampTzts;
326336
structpg_tmtt,
327337
*tm=&tt;
328338
fsec_tfsec;
329339
inttz;
330340

331-
ts=GetCurrentTransactionStartTimestamp();
332-
333-
if (timestamp2tm(ts,&tz,tm,&fsec,NULL,NULL)!=0)
334-
ereport(ERROR,
335-
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
336-
errmsg("timestamp out of range")));
341+
GetCurrentTimeUsec(tm,&fsec,&tz);
337342

338343
result= (TimeTzADT*)palloc(sizeof(TimeTzADT));
339344
tm2timetz(tm,fsec,tz,result);
@@ -348,18 +353,12 @@ TimeADT
348353
GetSQLLocalTime(int32typmod)
349354
{
350355
TimeADTresult;
351-
TimestampTzts;
352356
structpg_tmtt,
353357
*tm=&tt;
354358
fsec_tfsec;
355359
inttz;
356360

357-
ts=GetCurrentTransactionStartTimestamp();
358-
359-
if (timestamp2tm(ts,&tz,tm,&fsec,NULL,NULL)!=0)
360-
ereport(ERROR,
361-
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
362-
errmsg("timestamp out of range")));
361+
GetCurrentTimeUsec(tm,&fsec,&tz);
363362

364363
tm2time(tm,fsec,&result);
365364
AdjustTimeForTypmod(&result,typmod);

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

Lines changed: 56 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -339,35 +339,80 @@ j2day(int date)
339339
/*
340340
* GetCurrentDateTime()
341341
*
342-
* Get the transaction start time ("now()") broken down as a struct pg_tm.
342+
* Get the transaction start time ("now()") broken down as a struct pg_tm,
343+
* converted according to the session timezone setting.
344+
*
345+
* This is just a convenience wrapper for GetCurrentTimeUsec, to cover the
346+
* case where caller doesn't need either fractional seconds or tz offset.
343347
*/
344348
void
345349
GetCurrentDateTime(structpg_tm*tm)
346350
{
347-
inttz;
348351
fsec_tfsec;
349352

350-
timestamp2tm(GetCurrentTransactionStartTimestamp(),&tz,tm,&fsec,
351-
NULL,NULL);
352-
/* Note: don't pass NULL tzp to timestamp2tm; affects behavior */
353+
GetCurrentTimeUsec(tm,&fsec,NULL);
353354
}
354355

355356
/*
356357
* GetCurrentTimeUsec()
357358
*
358359
* Get the transaction start time ("now()") broken down as a struct pg_tm,
359-
* including fractional seconds and timezone offset.
360+
* including fractional seconds and timezone offset. The time is converted
361+
* according to the session timezone setting.
362+
*
363+
* Callers may pass tzp = NULL if they don't need the offset, but this does
364+
* not affect the conversion behavior (unlike timestamp2tm()).
365+
*
366+
* Internally, we cache the result, since this could be called many times
367+
* in a transaction, within which now() doesn't change.
360368
*/
361369
void
362370
GetCurrentTimeUsec(structpg_tm*tm,fsec_t*fsec,int*tzp)
363371
{
364-
inttz;
372+
TimestampTzcur_ts=GetCurrentTransactionStartTimestamp();
373+
374+
/*
375+
* The cache key must include both current time and current timezone. By
376+
* representing the timezone by just a pointer, we're assuming that
377+
* distinct timezone settings could never have the same pointer value.
378+
* This is true by virtue of the hashtable used inside pg_tzset();
379+
* however, it might need another look if we ever allow entries in that
380+
* hash to be recycled.
381+
*/
382+
staticTimestampTzcache_ts=0;
383+
staticpg_tz*cache_timezone=NULL;
384+
staticstructpg_tmcache_tm;
385+
staticfsec_tcache_fsec;
386+
staticintcache_tz;
387+
388+
if (cur_ts!=cache_ts||session_timezone!=cache_timezone)
389+
{
390+
/*
391+
* Make sure cache is marked invalid in case of error after partial
392+
* update within timestamp2tm.
393+
*/
394+
cache_timezone=NULL;
395+
396+
/*
397+
* Perform the computation, storing results into cache. We do not
398+
* really expect any error here, since current time surely ought to be
399+
* within range, but check just for sanity's sake.
400+
*/
401+
if (timestamp2tm(cur_ts,&cache_tz,&cache_tm,&cache_fsec,
402+
NULL,session_timezone)!=0)
403+
ereport(ERROR,
404+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
405+
errmsg("timestamp out of range")));
406+
407+
/* OK, so mark the cache valid. */
408+
cache_ts=cur_ts;
409+
cache_timezone=session_timezone;
410+
}
365411

366-
timestamp2tm(GetCurrentTransactionStartTimestamp(),&tz,tm,fsec,
367-
NULL,NULL);
368-
/* Note: don't pass NULL tzp to timestamp2tm; affects behavior */
412+
*tm=cache_tm;
413+
*fsec=cache_fsec;
369414
if (tzp!=NULL)
370-
*tzp=tz;
415+
*tzp=cache_tz;
371416
}
372417

373418

0 commit comments

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