From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-hackers@lists.postgresql.org
Subject: Re: [HACKERS] Cutting initdb's runtime (Perl question embedded)
In-reply-to: <21138.1525969099@sss.pgh.pa.us>
References: <20180509153924.32kvslqeqdyco7c5@alvherre.pgsql>
 <8873.1525881373@sss.pgh.pa.us> <21138.1525969099@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Thu, 10 May 2018 12:18:19 -0400"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <25385.1533925835.0@sss.pgh.pa.us>
Date: Fri, 10 Aug 2018 14:31:25 -0400
Message-ID: <25428.1533925885@sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <25385.1533925835.1@sss.pgh.pa.us>

I wrote:
>> ... I'll
>> take a look at whipping up something that checks /etc/localtime.

> Here's a draft patch.  It seems to do what I expect on a couple of
> different macOS releases as well as recent Fedora.

The cfbot points out that this has suffered bit-rot, so here's a rebased
version --- no substantive changes.

			regards, tom lane


------- =_aaaaaaaaaa0
Content-Type: text/x-diff;
	name="check-etc-localtime-symlink-for-zone-name-2.patch";
	charset="us-ascii"
Content-ID: <25385.1533925835.2@sss.pgh.pa.us>
Content-Description: check-etc-localtime-symlink-for-zone-name-2.patch
Content-Transfer-Encoding: quoted-printable

diff --git a/src/bin/initdb/findtimezone.c b/src/bin/initdb/findtimezone.c
index 4c3a91a..6901188 100644
*** a/src/bin/initdb/findtimezone.c
--- b/src/bin/initdb/findtimezone.c
***************
*** 15,20 ****
--- 15,21 ----
  #include <fcntl.h>
  #include <sys/stat.h>
  #include <time.h>
+ #include <unistd.h>
  =

  #include "pgtz.h"
  =

*************** pg_load_tz(const char *name)
*** 126,137 ****
   * On most systems, we rely on trying to match the observable behavior o=
f
   * the C library's localtime() function.  The database zone that matches
   * furthest into the past is the one to use.  Often there will be severa=
l
!  * zones with identical rankings (since the Olson database assigns multi=
ple
   * names to many zones).  We break ties arbitrarily by preferring shorte=
r,
   * then alphabetically earlier zone names.
   *
   * Win32's native knowledge about timezones appears to be too incomplete
!  * and too different from the Olson database for the above matching stra=
tegy
   * to be of any use. But there is just a limited number of timezones
   * available, so we can rely on a handmade mapping table instead.
   */
--- 127,145 ----
   * On most systems, we rely on trying to match the observable behavior o=
f
   * the C library's localtime() function.  The database zone that matches
   * furthest into the past is the one to use.  Often there will be severa=
l
!  * zones with identical rankings (since the IANA database assigns multip=
le
   * names to many zones).  We break ties arbitrarily by preferring shorte=
r,
   * then alphabetically earlier zone names.
   *
+  * Many modern systems use the IANA database, so if we can determine the
+  * system's idea of which zone it is using and its behavior matches our =
zone
+  * of the same name, we can skip the rather-expensive search through all=
 the
+  * zones in our database.  This short-circuit path also ensures that we =
spell
+  * the zone name the same way the system setting does, even in the prese=
nce
+  * of multiple aliases for the same zone.
+  *
   * Win32's native knowledge about timezones appears to be too incomplete
!  * and too different from the IANA database for the above matching strat=
egy
   * to be of any use. But there is just a limited number of timezones
   * available, so we can rely on a handmade mapping table instead.
   */
*************** struct tztry
*** 150,155 ****
--- 158,165 ----
  	time_t		test_times[MAX_TEST_TIMES];
  };
  =

+ static bool check_system_link_file(const char *linkname, struct tztry *t=
t,
+ 					   char *bestzonename);
  static void scan_available_timezones(char *tzdir, char *tzdirsub,
  						 struct tztry *tt,
  						 int *bestscore, char *bestzonename);
*************** score_timezone(const char *tzname, struc
*** 299,310 ****
  	return i;
  }
  =

  =

  /*
   * Try to identify a timezone name (in our terminology) that best matche=
s the
!  * observed behavior of the system timezone library.  We cannot assume t=
hat
!  * the system TZ environment setting (if indeed there is one) matches ou=
r
!  * terminology, so we ignore it and just look at what localtime() return=
s.
   */
  static const char *
  identify_system_timezone(void)
--- 309,327 ----
  	return i;
  }
  =

+ /*
+  * Test whether given zone name is a perfect match to localtime() behavi=
or
+  */
+ static bool
+ perfect_timezone_match(const char *tzname, struct tztry *tt)
+ {
+ 	return (score_timezone(tzname, tt) =3D=3D tt->n_test_times);
+ }
+ =

  =

  /*
   * Try to identify a timezone name (in our terminology) that best matche=
s the
!  * observed behavior of the system localtime() function.
   */
  static const char *
  identify_system_timezone(void)
*************** identify_system_timezone(void)
*** 339,345 ****
  	 * way of doing things, but experience has shown that system-supplied
  	 * timezone definitions are likely to have DST behavior that is right f=
or
  	 * the recent past and not so accurate further back. Scoring in this wa=
y
! 	 * allows us to recognize zones that have some commonality with the Ols=
on
  	 * database, without insisting on exact match. (Note: we probe Thursday=
s,
  	 * not Sundays, to avoid triggering DST-transition bugs in localtime
  	 * itself.)
--- 356,362 ----
  	 * way of doing things, but experience has shown that system-supplied
  	 * timezone definitions are likely to have DST behavior that is right f=
or
  	 * the recent past and not so accurate further back. Scoring in this wa=
y
! 	 * allows us to recognize zones that have some commonality with the IAN=
A
  	 * database, without insisting on exact match. (Note: we probe Thursday=
s,
  	 * not Sundays, to avoid triggering DST-transition bugs in localtime
  	 * itself.)
*************** identify_system_timezone(void)
*** 374,380 ****
  		tt.test_times[tt.n_test_times++] =3D t;
  	}
  =

! 	/* Search for the best-matching timezone file */
  	strlcpy(tmptzdir, pg_TZDIR(), sizeof(tmptzdir));
  	bestscore =3D -1;
  	resultbuf[0] =3D '\0';
--- 391,408 ----
  		tt.test_times[tt.n_test_times++] =3D t;
  	}
  =

! 	/*
! 	 * Try to avoid the brute-force search by seeing if we can recognize th=
e
! 	 * system's timezone setting directly.
! 	 *
! 	 * Currently we just check /etc/localtime; there are other conventions =
for
! 	 * this, but that seems to be the only one used on enough platforms to =
be
! 	 * worth troubling over.
! 	 */
! 	if (check_system_link_file("/etc/localtime", &tt, resultbuf))
! 		return resultbuf;
! =

! 	/* No luck, so search for the best-matching timezone file */
  	strlcpy(tmptzdir, pg_TZDIR(), sizeof(tmptzdir));
  	bestscore =3D -1;
  	resultbuf[0] =3D '\0';
*************** identify_system_timezone(void)
*** 383,389 ****
  							 &bestscore, resultbuf);
  	if (bestscore > 0)
  	{
! 		/* Ignore Olson's rather silly "Factory" zone; use GMT instead */
  		if (strcmp(resultbuf, "Factory") =3D=3D 0)
  			return NULL;
  		return resultbuf;
--- 411,417 ----
  							 &bestscore, resultbuf);
  	if (bestscore > 0)
  	{
! 		/* Ignore IANA's rather silly "Factory" zone; use GMT instead */
  		if (strcmp(resultbuf, "Factory") =3D=3D 0)
  			return NULL;
  		return resultbuf;
*************** identify_system_timezone(void)
*** 472,478 ****
  =

  	/*
  	 * Did not find the timezone.  Fallback to use a GMT zone.  Note that t=
he
! 	 * Olson timezone database names the GMT-offset zones in POSIX style: p=
lus
  	 * is west of Greenwich.  It's unfortunate that this is opposite of SQL
  	 * conventions.  Should we therefore change the names? Probably not...
  	 */
--- 500,506 ----
  =

  	/*
  	 * Did not find the timezone.  Fallback to use a GMT zone.  Note that t=
he
! 	 * IANA timezone database names the GMT-offset zones in POSIX style: pl=
us
  	 * is west of Greenwich.  It's unfortunate that this is opposite of SQL
  	 * conventions.  Should we therefore change the names? Probably not...
  	 */
*************** identify_system_timezone(void)
*** 487,492 ****
--- 515,608 ----
  }
  =

  /*
+  * Examine a system-provided symlink file to see if it tells us the time=
zone.
+  *
+  * Unfortunately, there is little standardization of how the system defa=
ult
+  * timezone is determined in the absence of a TZ environment setting.
+  * But a common strategy is to create a symlink at a well-known place.
+  * If "linkname" identifies a readable symlink, and the tail of its cont=
ents
+  * matches a zone name we know, and the actual behavior of localtime() a=
grees
+  * with what we think that zone means, then we may use that zone name.
+  *
+  * We insist on a perfect behavioral match, which might not happen if th=
e
+  * system has a different IANA database version than we do; but in that =
case
+  * it seems best to fall back to the brute-force search.
+  *
+  * linkname is the symlink file location to probe.
+  *
+  * tt tells about the system timezone behavior we need to match.
+  *
+  * If we successfully identify a zone name, store it in *bestzonename an=
d
+  * return true; else return false.  bestzonename must be a buffer of len=
gth
+  * TZ_STRLEN_MAX + 1.
+  */
+ static bool
+ check_system_link_file(const char *linkname, struct tztry *tt,
+ 					   char *bestzonename)
+ {
+ #ifdef HAVE_READLINK
+ 	char		link_target[MAXPGPATH];
+ 	int			len;
+ 	const char *cur_name;
+ =

+ 	/*
+ 	 * Try to read the symlink.  If not there, not a symlink, etc etc, just
+ 	 * quietly fail; the precise reason needn't concern us.
+ 	 */
+ 	len =3D readlink(linkname, link_target, sizeof(link_target));
+ 	if (len < 0 || len >=3D sizeof(link_target))
+ 		return false;
+ 	link_target[len] =3D '\0';
+ =

+ #ifdef DEBUG_IDENTIFY_TIMEZONE
+ 	fprintf(stderr, "symbolic link \"%s\" contains \"%s\"\n",
+ 			linkname, link_target);
+ #endif
+ =

+ 	/*
+ 	 * The symlink is probably of the form "/path/to/zones/zone/name", or
+ 	 * possibly it is a relative path.  Nobody puts their zone DB directly =
in
+ 	 * the root directory, so we can definitely skip the first component; b=
ut
+ 	 * after that it's trial-and-error to identify which path component beg=
ins
+ 	 * the zone name.
+ 	 */
+ 	cur_name =3D link_target;
+ 	while (*cur_name)
+ 	{
+ 		/* Advance to next segment of path */
+ 		cur_name =3D strchr(cur_name + 1, '/');
+ 		if (cur_name =3D=3D NULL)
+ 			break;
+ 		/* If there are consecutive slashes, skip all, as the kernel would */
+ 		do
+ 		{
+ 			cur_name++;
+ 		} while (*cur_name =3D=3D '/');
+ =

+ 		/*
+ 		 * Test remainder of path to see if it is a matching zone name.
+ 		 * Relative paths might contain ".."; we needn't bother testing if the
+ 		 * first component is that.  Also defend against overlength names.
+ 		 */
+ 		if (*cur_name && *cur_name !=3D '.' &&
+ 			strlen(cur_name) <=3D TZ_STRLEN_MAX &&
+ 			perfect_timezone_match(cur_name, tt))
+ 		{
+ 			/* Success! */
+ 			strcpy(bestzonename, cur_name);
+ 			return true;
+ 		}
+ 	}
+ =

+ 	/* Couldn't extract a matching zone name */
+ 	return false;
+ #else
+ 	/* No symlinks?  Forget it */
+ 	return false;
+ #endif
+ }
+ =

+ /*
   * Recursively scan the timezone database looking for the best match to
   * the system timezone behavior.
   *
*************** static const struct
*** 586,592 ****
  	 * HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time
  	 * Zones on Windows 10 and Windows 7.
  	 *
! 	 * The zones have been matched to Olson timezones by looking at the cit=
ies
  	 * listed in the win32 display name (in the comment here) in most cases=
.
  	 */
  	{
--- 702,708 ----
  	 * HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time
  	 * Zones on Windows 10 and Windows 7.
  	 *
! 	 * The zones have been matched to IANA timezones by looking at the citi=
es
  	 * listed in the win32 display name (in the comment here) in most cases=
.
  	 */
  	{
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index 32746c7..cb8c745 100644
*** a/src/bin/initdb/initdb.c
--- b/src/bin/initdb/initdb.c
*************** static char *pgdata_native;
*** 174,179 ****
--- 174,180 ----
  static int	n_connections =3D 10;
  static int	n_buffers =3D 50;
  static const char *dynamic_shared_memory_type =3D NULL;
+ static const char *default_timezone =3D NULL;
  =

  /*
   * Warning messages for authentication methods
*************** test_config_settings(void)
*** 1058,1063 ****
--- 1059,1069 ----
  		printf("%dMB\n", (n_buffers * (BLCKSZ / 1024)) / 1024);
  	else
  		printf("%dkB\n", n_buffers * (BLCKSZ / 1024));
+ =

+ 	printf(_("selecting default timezone ... "));
+ 	fflush(stdout);
+ 	default_timezone =3D select_default_timezone(share_path);
+ 	printf("%s\n", default_timezone ? default_timezone : "GMT");
  }
  =

  /*
*************** setup_config(void)
*** 1086,1092 ****
  	char	  **conflines;
  	char		repltok[MAXPGPATH];
  	char		path[MAXPGPATH];
- 	const char *default_timezone;
  	char	   *autoconflines[3];
  =

  	fputs(_("creating configuration files ... "), stdout);
--- 1092,1097 ----
*************** setup_config(void)
*** 1168,1174 ****
  							  "#default_text_search_config =3D 'pg_catalog.simple'",
  							  repltok);
  =

- 	default_timezone =3D select_default_timezone(share_path);
  	if (default_timezone)
  	{
  		snprintf(repltok, sizeof(repltok), "timezone =3D '%s'",
--- 1173,1178 ----

------- =_aaaaaaaaaa0--