Fix some odd behaviors when using a SQL-style simple GMT offset timezone.

Formerly, when using a SQL-spec timezone setting with a fixed GMT offset
(called a "brute force" timezone in the code), the session_timezone
variable was not updated to match the nominal timezone; rather, all code
was expected to ignore session_timezone if HasCTZSet was true.  This is
of course obviously fragile, though a search of the code finds only
timeofday() failing to honor the rule.  A bigger problem was that
DetermineTimeZoneOffset() supposed that if its pg_tz parameter was
pointer-equal to session_timezone, then HasCTZSet should override the
parameter.  This would cause datetime input containing an explicit zone
name to be treated as referencing the brute-force zone instead, if the
zone name happened to match the session timezone that had prevailed
before installing the brute-force zone setting (as reported in bug #8572).
The same malady could affect AT TIME ZONE operators.

To fix, set up session_timezone so that it matches the brute-force zone
specification, which we can do using the POSIX timezone definition syntax
"<abbrev>offset", and get rid of the bogus lookaside check in
DetermineTimeZoneOffset().  Aside from fixing the erroneous behavior in
datetime parsing and AT TIME ZONE, this will cause the timeofday() function
to print its result in the user-requested time zone rather than some
previously-set zone.  It might also affect results in third-party
extensions, if there are any that make use of session_timezone without
considering HasCTZSet, but in all cases the new behavior should be saner
than before.

Back-patch to all supported branches.

1 files changed, 40 insertions, 0 deletions

diff --git a/src/timezone/pgtz.c b/src/timezone/pgtz.c
index 203bda88c45..1130de9a961 100644
--- a/src/timezone/pgtz.c
+++ b/src/timezone/pgtz.c
@@ -288,6 +288,46 @@ pg_tzset(const char *name)
 	return &tzp->tz;
 }
 
+/*
+ * Load a fixed-GMT-offset timezone.
+ * This is used for SQL-spec SET TIME ZONE INTERVAL 'foo' cases.
+ * It's otherwise equivalent to pg_tzset().
+ *
+ * The GMT offset is specified in seconds, positive values meaning west of
+ * Greenwich (ie, POSIX not ISO sign convention).  However, we use ISO
+ * sign convention in the displayable abbreviation for the zone.
+ */
+pg_tz *
+pg_tzset_offset(long gmtoffset)
+{
+	long		absoffset = (gmtoffset < 0) ? -gmtoffset : gmtoffset;
+	char		offsetstr[64];
+	char		tzname[128];
+
+	snprintf(offsetstr, sizeof(offsetstr),
+			 "%02ld", absoffset / SECSPERHOUR);
+	absoffset %= SECSPERHOUR;
+	if (absoffset != 0)
+	{
+		snprintf(offsetstr + strlen(offsetstr),
+				 sizeof(offsetstr) - strlen(offsetstr),
+				 ":%02ld", absoffset / SECSPERMIN);
+		absoffset %= SECSPERMIN;
+		if (absoffset != 0)
+			snprintf(offsetstr + strlen(offsetstr),
+					 sizeof(offsetstr) - strlen(offsetstr),
+					 ":%02ld", absoffset);
+	}
+	if (gmtoffset > 0)
+		snprintf(tzname, sizeof(tzname), "<-%s>+%s",
+				 offsetstr, offsetstr);
+	else
+		snprintf(tzname, sizeof(tzname), "<+%s>-%s",
+				 offsetstr, offsetstr);
+
+	return pg_tzset(tzname);
+}
+
 
 /*
  * Initialize timezone library