Seek zone abbreviations in the IANA data before timezone_abbreviations.

If a time zone abbreviation used in datetime input is defined in
the currently active timezone, use that definition in preference
to looking in the timezone_abbreviations list.  That allows us to
correctly handle abbreviations that have different meanings in
different timezones.  Also, it eliminates an inconsistency between
datetime input and datetime output: the non-ISO datestyles for
timestamptz have always printed abbreviations taken from the IANA
data, not from timezone_abbreviations.  Before this fix, it was
possible to demonstrate cases where casting a timestamp to text
and back fails or changes the value significantly because of that
inconsistency.

While this change removes the ability to override the IANA data about
an abbreviation known in the current zone, it's not clear that there's
any real use-case for doing so.  But it is clear that this makes life
a lot easier for dealing with abbreviations that have conflicts across
different time zones.

Also update the pg_timezone_abbrevs view to report abbreviations
that are recognized via the IANA data, and *not* report any
timezone_abbreviations entries that are thereby overridden.
Under the hood, there are now two SRFs, one that pulls the IANA
data and one that pulls timezone_abbreviations entries.  They're
combined by logic in the view.  This approach was useful for
debugging (since the functions can be called on their own).
While I don't intend to document the functions explicitly,
they might be useful to call directly.

Also improve DecodeTimezoneAbbrev's caching logic so that it can
cache zone abbreviations found in the IANA data.  Without that,
this patch would have caused a noticeable degradation of the
runtime of timestamptz_in.

Per report from Aleksander Alekseev and additional investigation.

Discussion: https://postgr.es/m/CAJ7c6TOATjJqvhnYsui0=CO5XFMF4dvTGH+skzB--jNhqSQu5g@mail.gmail.com

4 files changed, 49 insertions, 7 deletions

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 3f41a17b1fe..a8866292d46 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -10054,8 +10054,10 @@ SET XML OPTION { DOCUMENT | CONTENT };
       </term>
       <listitem>
        <para>
-        Sets the collection of time zone abbreviations that will be accepted
-        by the server for datetime input.  The default is <literal>'Default'</literal>,
+        Sets the collection of additional time zone abbreviations that
+        will be accepted by the server for datetime input (beyond any
+        abbreviations defined by the current <varname>TimeZone</varname>
+        setting).  The default is <literal>'Default'</literal>,
         which is a collection that works in most of the world; there are
         also <literal>'Australia'</literal> and <literal>'India'</literal>,
         and other collections can be defined for a particular installation.
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 3e6751d64cc..1d9127e94e4 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -2534,6 +2534,10 @@ TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
      abbreviation if one is in common use in the current zone.  Otherwise
      it appears as a signed numeric offset in ISO 8601 basic format
      (<replaceable>hh</replaceable> or <replaceable>hhmm</replaceable>).
+     The alphabetic abbreviations shown in these styles are taken from the
+     IANA time zone database entry currently selected by the
+     <xref linkend="guc-timezone"/> run-time parameter; they are not
+     affected by the <xref linkend="guc-timezone-abbreviations"/> setting.
     </para>
 
     <para>
diff --git a/doc/src/sgml/datetime.sgml b/doc/src/sgml/datetime.sgml
index e7035c78065..3e24170acbf 100644
--- a/doc/src/sgml/datetime.sgml
+++ b/doc/src/sgml/datetime.sgml
@@ -80,7 +80,7 @@
       <step>
        <para>
         See if the token matches any known time zone abbreviation.
-        These abbreviations are supplied by the configuration file
+        These abbreviations are determined by the configuration settings
         described in <xref linkend="datetime-config-files"/>.
        </para>
       </step>
@@ -424,9 +424,43 @@
    <para>
     Since timezone abbreviations are not well standardized,
     <productname>PostgreSQL</productname> provides a means to customize
-    the set of abbreviations accepted by the server.  The
-    <xref linkend="guc-timezone-abbreviations"/> run-time parameter
-    determines the active set of abbreviations.  While this parameter
+    the set of abbreviations accepted in datetime input.
+    There are two sources for these abbreviations:
+
+    <orderedlist>
+     <listitem>
+      <para>
+       The <xref linkend="guc-timezone"/> run-time parameter is usually
+       set to the name of an entry in the IANA time zone database.
+       If that zone has widely-used zone abbreviations, they will appear
+       in the IANA data, and <productname>PostgreSQL</productname> will
+       preferentially recognize those abbreviations with the meanings
+       given in the IANA data.
+       For example, if <varname>timezone</varname> is set
+       to <literal>America/New_York</literal> then <literal>EST</literal>
+       will be understood as UTC-5 and <literal>EDT</literal> will be
+       understood as UTC-4.  (These IANA abbreviations will also be used
+       in datetime output, if <xref linkend="guc-datestyle"/> is set to a
+       style that prefers non-numeric zone abbreviations.)
+      </para>
+     </listitem>
+
+     <listitem>
+      <para>
+       If an abbreviation is not found in the current IANA time zone,
+       it is sought in the list specified by the
+       <xref linkend="guc-timezone-abbreviations"/> run-time parameter.
+       The <varname>timezone_abbreviations</varname> list is primarily
+       useful for allowing datetime input to recognize abbreviations for
+       time zones other than the current zone.  (These abbreviations will
+       not be used in datetime output.)
+      </para>
+     </listitem>
+    </orderedlist>
+   </para>
+
+   <para>
+    While the <varname>timezone_abbreviations</varname> parameter
     can be altered by any database user, the possible values for it
     are under the control of the database administrator &mdash; they
     are in fact names of configuration files stored in
diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index a586156614d..8e2b0a7927b 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -4566,7 +4566,9 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
    The view <structname>pg_timezone_abbrevs</structname> provides a list
    of time zone abbreviations that are currently recognized by the datetime
    input routines.  The contents of this view change when the
-   <xref linkend="guc-timezone-abbreviations"/> run-time parameter is modified.
+   <xref linkend="guc-timezone"/> or
+   <xref linkend="guc-timezone-abbreviations"/> run-time parameters are
+   modified.
   </para>
 
   <table>