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

Commitd7674c9

Browse files
committed
Seek zone abbreviations in the IANA data before timezone_abbreviations.
If a time zone abbreviation used in datetime input is defined inthe currently active timezone, use that definition in preferenceto looking in the timezone_abbreviations list. That allows us tocorrectly handle abbreviations that have different meanings indifferent timezones. Also, it eliminates an inconsistency betweendatetime input and datetime output: the non-ISO datestyles fortimestamptz have always printed abbreviations taken from the IANAdata, not from timezone_abbreviations. Before this fix, it waspossible to demonstrate cases where casting a timestamp to textand back fails or changes the value significantly because of thatinconsistency.While this change removes the ability to override the IANA data aboutan abbreviation known in the current zone, it's not clear that there'sany real use-case for doing so. But it is clear that this makes lifea lot easier for dealing with abbreviations that have conflicts acrossdifferent time zones.Also update the pg_timezone_abbrevs view to report abbreviationsthat are recognized via the IANA data, and *not* report anytimezone_abbreviations entries that are thereby overridden.Under the hood, there are now two SRFs, one that pulls the IANAdata and one that pulls timezone_abbreviations entries. They'recombined by logic in the view. This approach was useful fordebugging (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 cancache zone abbreviations found in the IANA data. Without that,this patch would have caused a noticeable degradation of theruntime of timestamptz_in.Per report from Aleksander Alekseev and additional investigation.Discussion:https://postgr.es/m/CAJ7c6TOATjJqvhnYsui0=CO5XFMF4dvTGH+skzB--jNhqSQu5g@mail.gmail.com
1 parentbc10219 commitd7674c9

File tree

20 files changed

+521
-35
lines changed

20 files changed

+521
-35
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -10054,8 +10054,10 @@ SET XML OPTION { DOCUMENT | CONTENT };
1005410054
</term>
1005510055
<listitem>
1005610056
<para>
10057-
Sets the collection of time zone abbreviations that will be accepted
10058-
by the server for datetime input. The default is <literal>'Default'</literal>,
10057+
Sets the collection of additional time zone abbreviations that
10058+
will be accepted by the server for datetime input (beyond any
10059+
abbreviations defined by the current <varname>TimeZone</varname>
10060+
setting). The default is <literal>'Default'</literal>,
1005910061
which is a collection that works in most of the world; there are
1006010062
also <literal>'Australia'</literal> and <literal>'India'</literal>,
1006110063
and other collections can be defined for a particular installation.

‎doc/src/sgml/datatype.sgml

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2534,6 +2534,10 @@ TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
25342534
abbreviation if one is in common use in the current zone. Otherwise
25352535
it appears as a signed numeric offset in ISO 8601 basic format
25362536
(<replaceable>hh</replaceable> or <replaceable>hhmm</replaceable>).
2537+
The alphabetic abbreviations shown in these styles are taken from the
2538+
IANA time zone database entry currently selected by the
2539+
<xref linkend="guc-timezone"/> run-time parameter; they are not
2540+
affected by the <xref linkend="guc-timezone-abbreviations"/> setting.
25372541
</para>
25382542

25392543
<para>

‎doc/src/sgml/datetime.sgml

Lines changed: 38 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -80,7 +80,7 @@
8080
<step>
8181
<para>
8282
See if the token matches any known time zone abbreviation.
83-
These abbreviations aresupplied by the configurationfile
83+
These abbreviations aredetermined by the configurationsettings
8484
described in <xref linkend="datetime-config-files"/>.
8585
</para>
8686
</step>
@@ -424,9 +424,43 @@
424424
<para>
425425
Since timezone abbreviations are not well standardized,
426426
<productname>PostgreSQL</productname> provides a means to customize
427-
the set of abbreviations accepted by the server. The
428-
<xref linkend="guc-timezone-abbreviations"/> run-time parameter
429-
determines the active set of abbreviations. While this parameter
427+
the set of abbreviations accepted in datetime input.
428+
There are two sources for these abbreviations:
429+
430+
<orderedlist>
431+
<listitem>
432+
<para>
433+
The <xref linkend="guc-timezone"/> run-time parameter is usually
434+
set to the name of an entry in the IANA time zone database.
435+
If that zone has widely-used zone abbreviations, they will appear
436+
in the IANA data, and <productname>PostgreSQL</productname> will
437+
preferentially recognize those abbreviations with the meanings
438+
given in the IANA data.
439+
For example, if <varname>timezone</varname> is set
440+
to <literal>America/New_York</literal> then <literal>EST</literal>
441+
will be understood as UTC-5 and <literal>EDT</literal> will be
442+
understood as UTC-4. (These IANA abbreviations will also be used
443+
in datetime output, if <xref linkend="guc-datestyle"/> is set to a
444+
style that prefers non-numeric zone abbreviations.)
445+
</para>
446+
</listitem>
447+
448+
<listitem>
449+
<para>
450+
If an abbreviation is not found in the current IANA time zone,
451+
it is sought in the list specified by the
452+
<xref linkend="guc-timezone-abbreviations"/> run-time parameter.
453+
The <varname>timezone_abbreviations</varname> list is primarily
454+
useful for allowing datetime input to recognize abbreviations for
455+
time zones other than the current zone. (These abbreviations will
456+
not be used in datetime output.)
457+
</para>
458+
</listitem>
459+
</orderedlist>
460+
</para>
461+
462+
<para>
463+
While the <varname>timezone_abbreviations</varname> parameter
430464
can be altered by any database user, the possible values for it
431465
are under the control of the database administrator &mdash; they
432466
are in fact names of configuration files stored in

‎doc/src/sgml/system-views.sgml

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4566,7 +4566,9 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
45664566
The view <structname>pg_timezone_abbrevs</structname> provides a list
45674567
of time zone abbreviations that are currently recognized by the datetime
45684568
input routines. The contents of this view change when the
4569-
<xref linkend="guc-timezone-abbreviations"/> run-time parameter is modified.
4569+
<xref linkend="guc-timezone"/> or
4570+
<xref linkend="guc-timezone-abbreviations"/> run-time parameters are
4571+
modified.
45704572
</para>
45714573

45724574
<table>

‎src/backend/catalog/system_views.sql

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -634,7 +634,12 @@ REVOKE ALL ON pg_ident_file_mappings FROM PUBLIC;
634634
REVOKE EXECUTEON FUNCTION pg_ident_file_mappings()FROM PUBLIC;
635635

636636
CREATEVIEWpg_timezone_abbrevsAS
637-
SELECT*FROM pg_timezone_abbrevs();
637+
SELECT*FROM pg_timezone_abbrevs_zone() z
638+
UNION ALL
639+
(SELECT*FROM pg_timezone_abbrevs_abbrevs() a
640+
WHERE NOT EXISTS (SELECT1FROM pg_timezone_abbrevs_zone() z2
641+
WHEREz2.abbrev=a.abbrev))
642+
ORDER BY abbrev;
638643

639644
CREATEVIEWpg_timezone_namesAS
640645
SELECT*FROM pg_timezone_names();

‎src/backend/commands/variable.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -381,6 +381,8 @@ void
381381
assign_timezone(constchar*newval,void*extra)
382382
{
383383
session_timezone=*((pg_tz**)extra);
384+
/* datetime.c's cache of timezone abbrevs may now be obsolete */
385+
ClearTimeZoneAbbrevCache();
384386
}
385387

386388
/*

0 commit comments

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