UCT (Re: pgsql: Update time zone data files to tzdata release 2019a.)

classic Classic list List threaded Threaded
35 messages Options
12
Reply | Threaded
Open this post in threaded view
|

UCT (Re: pgsql: Update time zone data files to tzdata release 2019a.)

Christoph Berg-2
Re: Tom Lane 2019-04-26 <[hidden email]>
> Update time zone data files to tzdata release 2019a.
>
> DST law changes in Palestine and Metlakatla.
> Historical corrections for Israel.
>
> Etc/UCT is now a backward-compatibility link to Etc/UTC, instead
> of being a separate zone that generates the abbreviation "UCT",
> which nowadays is typically a typo.  Postgres will still accept
> "UCT" as an input zone name, but it won't output it.

There is something wrong here. On Debian Buster/unstable, using
system tzdata (2019a-1), if /etc/timezone is "Etc/UTC":

11.3's initdb adds timezone = 'UCT' to postgresql.conf
12beta1's initdb add timezone = 'Etc/UCT' to postgresql.conf

Is that expected behavior? Docker users are complaining that "UCT"
messes up their testsuites. https://github.com/docker-library/postgres/issues/577

Christoph


Reply | Threaded
Open this post in threaded view
|

Re: UCT (Re: pgsql: Update time zone data files to tzdata release 2019a.)

Andrew Gierth
>>>>> "Christoph" == Christoph Berg <[hidden email]> writes:

 >> Etc/UCT is now a backward-compatibility link to Etc/UTC, instead of
 >> being a separate zone that generates the abbreviation "UCT", which
 >> nowadays is typically a typo. Postgres will still accept "UCT" as an
 >> input zone name, but it won't output it.

 Christoph> There is something wrong here. On Debian Buster/unstable,
 Christoph> using system tzdata (2019a-1), if /etc/timezone is
 Christoph> "Etc/UTC":

 Christoph> 11.3's initdb adds timezone = 'UCT' to postgresql.conf
 Christoph> 12beta1's initdb add timezone = 'Etc/UCT' to postgresql.conf

 Christoph> Is that expected behavior?

It's clearly not what users expect and it's clearly the wrong thing to
do, though it's the expected behavior of the current code:

 * On most systems, we rely on trying to match the observable behavior of
 * the C library's localtime() function.  The database zone that matches
 * furthest into the past is the one to use.  Often there will be several
 * zones with identical rankings (since the IANA database assigns multiple
 * names to many zones).  We break ties arbitrarily by preferring shorter,
 * then alphabetically earlier zone names.

I believe I pointed out a long, long time ago that this tie-breaking
strategy was insane, and that the rule should be to prefer canonical
names and use something else only in the case of a strictly better
match.

If TZ is set or if /etc/localtime is a symlink rather than a hardlink or
copy of the zone file, then PG can get the zone name directly rather
than having to do the comparisons, so the above comment doesn't apply;
that gives you a workaround.

--
Andrew (irc:RhodiumToad)


Reply | Threaded
Open this post in threaded view
|

Re: UCT (Re: pgsql: Update time zone data files to tzdata release 2019a.)

Tom Lane-2
In reply to this post by Christoph Berg-2
Christoph Berg <[hidden email]> writes:
> There is something wrong here. On Debian Buster/unstable, using
> system tzdata (2019a-1), if /etc/timezone is "Etc/UTC":

> 11.3's initdb adds timezone = 'UCT' to postgresql.conf
> 12beta1's initdb add timezone = 'Etc/UCT' to postgresql.conf

Hm, I don't have a Debian machine at hand, but I'm unable to
reproduce this using macOS or RHEL.  I tried things like

$ TZ=UTC initdb
...
selecting default timezone ... UTC
...

Is your build using --with-system-tzdata?  If so, which tzdb
release is the system on, and is it a completely stock copy
of that release?

Given the tie-breaking behavior in findtimezone.c,

 * ... Often there will be several
 * zones with identical rankings (since the IANA database assigns multiple
 * names to many zones).  We break ties arbitrarily by preferring shorter,
 * then alphabetically earlier zone names.

it's not so surprising that UCT might be chosen, but I don't
understand how Etc/UCT would be.

BTW, does Debian set up /etc/timezone as a symlink, by any chance,
rather than a copy or hard link?  If it's a symlink, we could improve
matters by teaching identify_system_timezone() to inspect it.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: UCT (Re: pgsql: Update time zone data files to tzdata release 2019a.)

Tom Lane-2
In reply to this post by Andrew Gierth
Andrew Gierth <[hidden email]> writes:
> I believe I pointed out a long, long time ago that this tie-breaking
> strategy was insane, and that the rule should be to prefer canonical
> names and use something else only in the case of a strictly better
> match.

This is assuming that the tzdb data has a concept of a canonical name
for a zone, which unfortunately it does not.  UTC, UCT, Etc/UTC,
and about four other strings are equivalent names for the same zone
so far as one can tell from the installed data.

We could imagine layering some additional data on top of tzdb,
but I don't much want to go there from a maintenance standpoint.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: UCT (Re: pgsql: Update time zone data files to tzdata release 2019a.)

Andres Freund
In reply to this post by Tom Lane-2
Hi,

On 2019-06-04 11:27:31 -0400, Tom Lane wrote:
> Hm, I don't have a Debian machine at hand, but I'm unable to
> reproduce this using macOS or RHEL.  I tried things like
>
> $ TZ=UTC initdb
> ...
> selecting default timezone ... UTC
> ...

On debian unstable that's what I get too, both with system and PG
tzdata.


> BTW, does Debian set up /etc/timezone as a symlink, by any chance,
> rather than a copy or hard link?  If it's a symlink, we could improve
> matters by teaching identify_system_timezone() to inspect it.

On my system it's a copy (link count 1, not a symlink). Or did you mean
/etc/localtime? Because that's indeed a symlink.

If I set the system-wide default, using dpkg-reconfigure -plow tzdata,
to UTC I *do* get Etc/UTC.

root@alap4:/home/andres/src/postgresql# cat /etc/timezone
Etc/UTC
root@alap4:/home/andres/src/postgresql# ls -l /etc/timezone
-rw-r--r-- 1 root root 8 Jun  4 15:44 /etc/timezone

selecting default timezone ... Etc/UTC

This is independent of being built with system or non-system tzdata.

Enabling debugging shows:

selecting default timezone ... symbolic link "/etc/localtime" contains "/usr/share/zoneinfo/Etc/UCT"
TZ "Etc/UCT" gets max score 5200
Etc/UCT

Greetings,

Andres Freund


Reply | Threaded
Open this post in threaded view
|

Re: UCT (Re: pgsql: Update time zone data files to tzdata release 2019a.)

Andrew Gierth
In reply to this post by Tom Lane-2
>>>>> "Tom" == Tom Lane <[hidden email]> writes:

 > Andrew Gierth <[hidden email]> writes:
 >> I believe I pointed out a long, long time ago that this tie-breaking
 >> strategy was insane, and that the rule should be to prefer canonical
 >> names and use something else only in the case of a strictly better
 >> match.

 Tom> This is assuming that the tzdb data has a concept of a canonical
 Tom> name for a zone, which unfortunately it does not. UTC, UCT,
 Tom> Etc/UTC, and about four other strings are equivalent names for the
 Tom> same zone so far as one can tell from the installed data.

The simplest definition is that the names listed in zone.tab or
zone1970.tab if you prefer that one are canonical, and Etc/UTC and the
Etc/GMT[offset] names could be regarded as canonical too. Everything
else is either an alias or a backward-compatibility hack.

--
Andrew (irc:RhodiumToad)


Reply | Threaded
Open this post in threaded view
|

Re: UCT (Re: pgsql: Update time zone data files to tzdata release 2019a.)

Andres Freund
In reply to this post by Tom Lane-2
Hi,

On 2019-06-04 11:27:31 -0400, Tom Lane wrote:
> $ TZ=UTC initdb
> ...
> selecting default timezone ... UTC
> ...

Btw, if the input is Etc/UTZ, do you also get UTC or Etc/UTZ? Because it
seems that debian only configures Etc/UTZ on a system-wide basis
now. Which seems not insane, given that's it's a backward compat thing
now.

- Andres


Reply | Threaded
Open this post in threaded view
|

Re: UCT (Re: pgsql: Update time zone data files to tzdata release 2019a.)

Andrew Gierth
In reply to this post by Christoph Berg-2
>>>>> "Christoph" == Christoph Berg <[hidden email]> writes:

 Christoph> There is something wrong here. On Debian Buster/unstable,
 Christoph> using system tzdata (2019a-1), if /etc/timezone is
 Christoph> "Etc/UTC":

 Christoph> 11.3's initdb adds timezone = 'UCT' to postgresql.conf
 Christoph> 12beta1's initdb add timezone = 'Etc/UCT' to postgresql.conf

fwiw on FreeBSD with no /etc/localtime and no TZ in the environment (and
hence running on UTC), I get "UCT" on both 11.3 and HEAD.

--
Andrew (irc:RhodiumToad)


Reply | Threaded
Open this post in threaded view
|

Re: UCT (Re: pgsql: Update time zone data files to tzdata release 2019a.)

Andres Freund
In reply to this post by Andres Freund
Hi,

On 2019-06-04 08:53:30 -0700, Andres Freund wrote:

> If I set the system-wide default, using dpkg-reconfigure -plow tzdata,
> to UTC I *do* get Etc/UTC.
>
> root@alap4:/home/andres/src/postgresql# cat /etc/timezone
> Etc/UTC
> root@alap4:/home/andres/src/postgresql# ls -l /etc/timezone
> -rw-r--r-- 1 root root 8 Jun  4 15:44 /etc/timezone
>
> selecting default timezone ... Etc/UTC
>
> This is independent of being built with system or non-system tzdata.
>
> Enabling debugging shows:

Sorry, I was not awake enough while reading the thread (and UCT looks so
similar to UTC).

I do indeed see the behaviour of choosing UCT in 11, but not in
12. Independent of system/non-system tzdata. With system tzdata, I get
the following debug output (after filtering lots of lines wiht out |grep
-v 'scores 0'|grep -v 'uses leap seconds')

TZ "Zulu" gets max score 5200
TZ "UCT" gets max score 5200
TZ "Universal" gets max score 5200
TZ "UTC" gets max score 5200
TZ "Etc/Zulu" gets max score 5200
TZ "Etc/UCT" gets max score 5200
TZ "Etc/Universal" gets max score 5200
TZ "Etc/UTC" gets max score 5200
TZ "localtime" gets max score 5200
TZ "posix/Zulu" gets max score 5200
TZ "posix/UCT" gets max score 5200
TZ "posix/Universal" gets max score 5200
TZ "posix/UTC" gets max score 5200
TZ "posix/Etc/Zulu" gets max score 5200
TZ "posix/Etc/UCT" gets max score 5200
TZ "posix/Etc/Universal" gets max score 5200
TZ "posix/Etc/UTC" gets max score 5200
ok

whereas master only does:

selecting default timezone ... symbolic link "/etc/localtime" contains "/usr/share/zoneinfo/Etc/UTC"
TZ "Etc/UTC" gets max score 5200
Etc/UTC

The reason for the behaviour difference between v12 and 11 is that 12
does:

        /*
         * Try to avoid the brute-force search by seeing if we can recognize the
         * 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;

which prevents having to iterate through all of these files, and ending
up with a lot of equivalently scored timezones.

Greetings,

Andres Freund


Reply | Threaded
Open this post in threaded view
|

Re: UCT (Re: pgsql: Update time zone data files to tzdata release 2019a.)

Andres Freund
In reply to this post by Andrew Gierth
Hi,

On 2019-06-04 17:20:42 +0100, Andrew Gierth wrote:
> fwiw on FreeBSD with no /etc/localtime and no TZ in the environment (and
> hence running on UTC), I get "UCT" on both 11.3 and HEAD.

That makes sense. As far as I can tell the reason that 12 sometimes ends
up with the proper timezone is that we shortcut the search by:

        /*
         * Try to avoid the brute-force search by seeing if we can recognize the
         * 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;

which is actually a behaviour changing, rather than just an
optimization, when there's a lot of equivalently scoring timezones.

Greetings,

Andres Freund


Reply | Threaded
Open this post in threaded view
|

Re: UCT (Re: pgsql: Update time zone data files to tzdata release 2019a.)

Christoph Berg-2
In reply to this post by Tom Lane-2
Re: Tom Lane 2019-06-04 <[hidden email]>
> > There is something wrong here. On Debian Buster/unstable, using
> > system tzdata (2019a-1), if /etc/timezone is "Etc/UTC":
>
> Is your build using --with-system-tzdata?  If so, which tzdb
> release is the system on, and is it a completely stock copy
> of that release?

It's using system tzdata (2019a-1).

There's one single patch on top of that:

https://sources.debian.org/src/tzdata/2019a-1/debian/patches/

> BTW, does Debian set up /etc/timezone as a symlink, by any chance,
> rather than a copy or hard link?  If it's a symlink, we could improve
> matters by teaching identify_system_timezone() to inspect it.

In the meantime I realized that I was only testing /etc/timezone
(which is a plain file with just the zone name), while not touching
/etc/localtime at all. In this environment, it's a symlink:

lrwxrwxrwx 1 root root 27 Mär 28 14:49 /etc/localtime -> /usr/share/zoneinfo/Etc/UTC

... but the name still gets canonicalized to Etc/UCT or UCT.

Christoph


Reply | Threaded
Open this post in threaded view
|

Re: UCT (Re: pgsql: Update time zone data files to tzdata release 2019a.)

Tom Lane-2
In reply to this post by Andres Freund
[ sorry for slow response, I'm on vacation ]

Andres Freund <[hidden email]> writes:
> That makes sense. As far as I can tell the reason that 12 sometimes ends
> up with the proper timezone is that we shortcut the search by:

> /*
> * Try to avoid the brute-force search by seeing if we can recognize the
> * 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;

> which is actually a behaviour changing, rather than just an
> optimization, when there's a lot of equivalently scoring timezones.

Sure, that is intentionally a behavior change in this situation.
The theory is that if "Etc/UCT" is what the user put in /etc/localtime,
then that's the spelling she wants.  See 23bd3cec6.

But it seems to me that this code is *not* determining the result in
Christoph's case, because if it were, it'd be settling on Etc/UTC,
according to his followup report that

>> lrwxrwxrwx 1 root root 27 Mär 28 14:49 /etc/localtime -> /usr/share/zoneinfo/Etc/UTC

I'm not too familiar with what actually determines glibc's behavior
on Debian, but I'm suspicious that there's an inconsistency between
/etc/localtime and /etc/timezone.  We won't adopt the spelling we
see in /etc/localtime unless it agrees with the observed behavior of
localtime(3).

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: UCT (Re: pgsql: Update time zone data files to tzdata release 2019a.)

Andres Freund
Hi,

On 2019-06-06 12:51:30 -0400, Tom Lane wrote:
> [ sorry for slow response, I'm on vacation ]

Good.


> Andres Freund <[hidden email]> writes:
> > That makes sense. As far as I can tell the reason that 12 sometimes ends
> > up with the proper timezone is that we shortcut the search by:
>
> > /*
> > * Try to avoid the brute-force search by seeing if we can recognize the
> > * 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;
>
> > which is actually a behaviour changing, rather than just an
> > optimization, when there's a lot of equivalently scoring timezones.
>
> Sure, that is intentionally a behavior change in this situation.
> The theory is that if "Etc/UCT" is what the user put in /etc/localtime,
> then that's the spelling she wants.  See 23bd3cec6.

Right, I'm not complaining about that. I'm just noting that that
explains the cross-version divergence.

Note that on 11 I *do* end up with some *other* timezone with the newer
timezone data:

$cat /etc/timezone;ls -l /etc/localtime
Etc/UTC
lrwxrwxrwx 1 root root 27 Jun  6 17:02 /etc/localtime -> /usr/share/zoneinfo/Etc/UTC

$ rm -rf /tmp/tztest;~/build/postgres/11-assert/install/bin/initdb /tmp/tztest 2>&1|grep -v 'scores 0'|grep -v 'uses leap seconds';grep timezone /tmp/tztest/postgresql.conf
...
TZ "Zulu" gets max score 5200
TZ "UCT" gets max score 5200
TZ "Universal" gets max score 5200
TZ "UTC" gets max score 5200
TZ "Etc/Zulu" gets max score 5200
TZ "Etc/UCT" gets max score 5200
TZ "Etc/Universal" gets max score 5200
TZ "Etc/UTC" gets max score 5200
TZ "localtime" gets max score 5200
TZ "posix/Zulu" gets max score 5200
TZ "posix/UCT" gets max score 5200
TZ "posix/Universal" gets max score 5200
TZ "posix/UTC" gets max score 5200
TZ "posix/Etc/Zulu" gets max score 5200
TZ "posix/Etc/UCT" gets max score 5200
TZ "posix/Etc/Universal" gets max score 5200
TZ "posix/Etc/UTC" gets max score 5200
ok
...

log_timezone = 'UCT'
timezone = 'UCT'
#timezone_abbreviations = 'Default'     # Select the set of available time zone
                                        # share/timezonesets/.

As you can see the switch from Etc/UTC to UCT does happen here
(presumably in any branch before 12). Which did not happen before the
import of 2019a / when using a system tzdata that's before
that. There you get:

TZ "Zulu" gets max score 5200
TZ "Universal" gets max score 5200
TZ "UTC" gets max score 5200
TZ "Etc/Zulu" gets max score 5200
TZ "Etc/Universal" gets max score 5200
TZ "Etc/UTC" gets max score 5200
ok

and end up with UTC as the selection.

I do think that < 12 clearly regressed here, although it's only exposing
previous behaviour further.

Greetings,

Andres Freund


Reply | Threaded
Open this post in threaded view
|

Re: UCT (Re: pgsql: Update time zone data files to tzdata release 2019a.)

Tom Lane-2
Andres Freund <[hidden email]> writes:
> On 2019-06-06 12:51:30 -0400, Tom Lane wrote:
>> Sure, that is intentionally a behavior change in this situation.
>> The theory is that if "Etc/UCT" is what the user put in /etc/localtime,
>> then that's the spelling she wants.  See 23bd3cec6.

> Right, I'm not complaining about that. I'm just noting that that
> explains the cross-version divergence.

It explains some cross-version divergence for sure.  What I'm still not
clear about is whether Christoph's report is entirely that, or whether
there's some additional factor we don't understand yet.

> As you can see the switch from Etc/UTC to UCT does happen here
> (presumably in any branch before 12). Which did not happen before the
> import of 2019a / when using a system tzdata that's before
> that.

Right.  Before 2019a, UCT would not have been a match to a system
setting of UTC because the zone abbreviation reported by localtime()
was different.  Now it's the same abbreviation.

Maybe we should consider back-patching 23bd3cec6.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: UCT (Re: pgsql: Update time zone data files to tzdata release 2019a.)

Tom Lane-2
In reply to this post by Christoph Berg-2
Christoph Berg <[hidden email]> writes:
> In the meantime I realized that I was only testing /etc/timezone
> (which is a plain file with just the zone name), while not touching
> /etc/localtime at all. In this environment, it's a symlink:
> lrwxrwxrwx 1 root root 27 Mär 28 14:49 /etc/localtime -> /usr/share/zoneinfo/Etc/UTC
> ... but the name still gets canonicalized to Etc/UCT or UCT.

Now that I'm home again, I tried to replicate this behavior.  I don't
have Debian Buster installed, but I do have an up-to-date Stretch
install, and I can't get it to do this.  What I see is that

1. HEAD will follow the spelling appearing in /etc/localtime, if that's
a symlink.  It will not pay any attention to /etc/timezone --- but as
far as I can tell, glibc doesn't either.  (For instance, if I remove
/etc/localtime, then date(1) starts reporting UTC, independently of
what /etc/timezone might say.)

2. Pre-v12, or if we can't get a valid zone name out of /etc/localtime,
the identify_system_timezone() search settles on "UCT" as being the
shortest and alphabetically first of the various equivalent names for
the zone.

The only way I can get it to pick "Etc/UCT" is if that's what I put
into /etc/localtime.  (In which case I maintain that that's not a bug,
or at least not our bug.)

So I'm still mystified by Christoph's report, and am forced to suspect
pilot error -- specifically, /etc/localtime not containing what he said.

Anyway, moving on to the question of what should we do about this,
I don't really have anything better to offer than back-patching 23bd3cec6.
I'm fairly hesitant to do that given the small amount of testing it's
gotten ... but given that it's been in the tree since September, maybe
we can feel like we'd have noticed any really bad problems.  I don't have
any use for Andrew's suggestion of looking into zone1970.tab: in the
first place I'm unconvinced that the tzdb guys intend that file to offer
canonical zone names, and in the second place I doubt we can rely on the
file to be present (it's not installed by zic itself), and in the third
place it definitely won't fix this particular issue because it has no
entries for UTC/UCT/GMT etc, only for geographical locations.

Thoughts?

                        regards, tom lane


PS: As a side note, I do notice an interesting difference between the
timezone database files as they appear on Debian versus what I see on
RHEL or in a PG-generated timezone tree.  Debian seems to use symlinks
for multiple equivalent zones:

$ ls -l /usr/share/zoneinfo/U??
-rw-r--r-- 1 root root 127 Mar 27 16:34 /usr/share/zoneinfo/UCT
lrwxrwxrwx 1 root root   3 Mar 27 16:34 /usr/share/zoneinfo/UTC -> UCT
$ ls -l /usr/share/zoneinfo/Etc/U??
lrwxrwxrwx 1 root root 6 Mar 27 16:34 /usr/share/zoneinfo/Etc/UCT -> ../UCT
lrwxrwxrwx 1 root root 6 Mar 27 16:34 /usr/share/zoneinfo/Etc/UTC -> ../UCT

but elsewhere these are hard links:

$ ls -l /usr/share/zoneinfo/U??
-rw-r--r--. 8 root root 118 Mar 26 11:37 /usr/share/zoneinfo/UCT
-rw-r--r--. 8 root root 118 Mar 26 11:37 /usr/share/zoneinfo/UTC
$ ls -l /usr/share/zoneinfo/Etc/U??
-rw-r--r--. 8 root root 118 Mar 26 11:37 /usr/share/zoneinfo/Etc/UCT
-rw-r--r--. 8 root root 118 Mar 26 11:37 /usr/share/zoneinfo/Etc/UTC

However, identify_system_timezone() doesn't treat symlinks differently
from regular files, so this doesn't explain anything about the problem
at hand, AFAICS.


Reply | Threaded
Open this post in threaded view
|

Re: UCT (Re: pgsql: Update time zone data files to tzdata release 2019a.)

Christoph Berg-2
Re: Tom Lane 2019-06-11 <[hidden email]>
> The only way I can get it to pick "Etc/UCT" is if that's what I put
> into /etc/localtime.  (In which case I maintain that that's not a bug,
> or at least not our bug.)

Did you try a symlink or a plain file for /etc/localtime?

> So I'm still mystified by Christoph's report, and am forced to suspect
> pilot error -- specifically, /etc/localtime not containing what he said.

On Debian unstable, deleting /etc/timezone, $TZ not set, and with this symlink:
lrwxrwxrwx 1 root root 27 Mär 28 14:49 /etc/localtime -> /usr/share/zoneinfo/Etc/UTC

/usr/lib/postgresql/11/bin/initdb -D pgdata
$ grep timezone pgdata/postgresql.conf
log_timezone = 'UCT'
timezone = 'UCT'

/usr/lib/postgresql/12/bin/initdb -D pgdata
$ grep timezone pgdata/postgresql.conf
log_timezone = 'Etc/UTC'
timezone = 'Etc/UTC'

Same behavior on Debian Stretch (stable):
lrwxrwxrwx 1 root root 27 Mai  7 11:14 /etc/localtime -> /usr/share/zoneinfo/Etc/UTC

$ grep timezone pgdata/postgresql.conf
log_timezone = 'UCT'
timezone = 'UCT'

$ grep timezone pgdata/postgresql.conf
log_timezone = 'Etc/UTC'
timezone = 'Etc/UTC'

> Anyway, moving on to the question of what should we do about this,
> I don't really have anything better to offer than back-patching 23bd3cec6.

The PG12 behavior seems sane, so +1.

Christoph


Reply | Threaded
Open this post in threaded view
|

Re: UCT (Re: pgsql: Update time zone data files to tzdata release 2019a.)

Tom Lane-2
Christoph Berg <[hidden email]> writes:
> Re: Tom Lane 2019-06-11 <[hidden email]>
>> The only way I can get it to pick "Etc/UCT" is if that's what I put
>> into /etc/localtime.  (In which case I maintain that that's not a bug,
>> or at least not our bug.)

> Did you try a symlink or a plain file for /etc/localtime?

Symlink --- if it's a plain file, our code can't learn anything from it.

> On Debian unstable, deleting /etc/timezone, $TZ not set, and with this symlink:
> lrwxrwxrwx 1 root root 27 Mär 28 14:49 /etc/localtime -> /usr/share/zoneinfo/Etc/UTC

> /usr/lib/postgresql/11/bin/initdb -D pgdata
> $ grep timezone pgdata/postgresql.conf
> log_timezone = 'UCT'
> timezone = 'UCT'

> /usr/lib/postgresql/12/bin/initdb -D pgdata
> $ grep timezone pgdata/postgresql.conf
> log_timezone = 'Etc/UTC'
> timezone = 'Etc/UTC'

That's what I'd expect.  Do you think your upthread report of HEAD
picking "Etc/UCT" was a typo?  Or maybe you actually had /etc/localtime
set that way?

>> Anyway, moving on to the question of what should we do about this,
>> I don't really have anything better to offer than back-patching 23bd3cec6.

> The PG12 behavior seems sane, so +1.

OK, I'll make that happen.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: UCT (Re: pgsql: Update time zone data files to tzdata release 2019a.)

Andrew Gierth
>>>>> "Tom" == Tom Lane <[hidden email]> writes:

 >>> Anyway, moving on to the question of what should we do about this,
 >>> I don't really have anything better to offer than back-patching
 >>> 23bd3cec6.

 >> The PG12 behavior seems sane, so +1.

 Tom> OK, I'll make that happen.

This isn't good enough, because it still picks "UCT" on a system with no
/etc/localtime and no TZ variable. Testing on HEAD as of 3da73d683 (on
FreeBSD, but it'll be the same anywhere else):

% ls -l /etc/*time*
ls: /etc/*time*: No such file or directory

% env -u TZ bin/initdb -D data -E UTF8 --no-locale
[...]
selecting default timezone ... UCT

We need to absolutely prefer UTC over UCT if both match.

--
Andrew (irc:RhodiumToad)


Reply | Threaded
Open this post in threaded view
|

Re: UCT (Re: pgsql: Update time zone data files to tzdata release 2019a.)

Tom Lane-2
Andrew Gierth <[hidden email]> writes:
> This isn't good enough, because it still picks "UCT" on a system with no
> /etc/localtime and no TZ variable. Testing on HEAD as of 3da73d683 (on
> FreeBSD, but it'll be the same anywhere else):

[ shrug... ]  Too bad.  I doubt that that's a common situation anyway.

> We need to absolutely prefer UTC over UCT if both match.

I don't see a reason why that's a hard requirement.  There are at least
two ways for a user to override initdb's decision (/etc/localtime or TZ),
or she could just change the GUC setting after the fact, and for that
matter it's not obvious that it matters to most people how TimeZone
is spelled as long as it delivers the right external behavior.  We had
the business with "Navajo" being preferred for US Mountain time for
quite a few years, with not very many complaints.

I don't see any way that we could "fix" this except with a hardwired
special case to prefer UTC over other spellings, and I definitely do
not want to go there.  If we start putting in magic special cases to make
particular zone names be preferred over other ones, where will we stop?
(I've been lurking on the tzdb mailing list for long enough now to know
that that's a fine recipe for opening ourselves up to politically-
motivated demands that name X be preferred over name Y.)

A possibly better idea is to push back on tzdb's choice to unify
these zones.   Don't know if they'd listen, but we could try.  The
UCT symlink hasn't been out there so long that it's got much inertia.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: UCT (Re: pgsql: Update time zone data files to tzdata release 2019a.)

Andrew Gierth
>>>>> "Tom" == Tom Lane <[hidden email]> writes:

 >> This isn't good enough, because it still picks "UCT" on a system with no
 >> /etc/localtime and no TZ variable. Testing on HEAD as of 3da73d683 (on
 >> FreeBSD, but it'll be the same anywhere else):

 Tom> [ shrug... ]  Too bad.  I doubt that that's a common situation anyway.

Literally every server I have set up is like this...

 >> We need to absolutely prefer UTC over UCT if both match.

 Tom> I don't see a reason why that's a hard requirement.

Because the reverse is clearly insane.

--
Andrew (irc:RhodiumToad)


12