Re: here does postgres take its timezone information from?
> 6 nov. 2019 kl. 03:03 skrev Thomas Munro : > > On Wed, Nov 6, 2019 at 2:20 PM Tom Lane wrote: >> Thomas Munro writes: >>> On Wed, Nov 6, 2019 at 12:02 PM Adrian Klaver >>> wrote: On 11/5/19 3:00 PM, Chris Withers wrote: > Hmm. Is there any option to use the system timezone packages? >> --with-system-tzdata=DIRECTORY >> >>> I hope that most distributions do that*, because otherwise you >>> could finish up with lots of out-of-sync copies of the tzdata database >>> inside your database, your JVM, your libc, etc etc, and you want a >>> single source of truth for that stuff. >> >> Right. Our recommendation is to use --with-system-tzdata if you're on >> a platform where the vendor updates their copy of tzdata regularly. >> The fact that we supply a copy of tzdata at all is really just a fallback >> for folks on poorly-supported platforms. (Naming no names here ...) > > Incidentally, that's also why I don't want to give up on libc > collations quite as easily as some, despite their limitations. It > should be possible to get all the software on your system to agree on > the ordering of two strings and the current time! > >>> *It looks like FreeBSD's port uses the copy of tzdata from the >>> PostgreSQL source tree by default and thus that is what you get if you >>> install PostgreSQL with "pkg". That's not a great default IMHO and >>> should be changed. >> >> Ugh. Who can we poke there? > > Maybe Palle? (CCed). Hi, The decision to use postgresql's tzdata is quite old. It was based on the assumption that postgres is updated more frequently than the operating system, and that for that reason it was better to use postgresql's tzdata, since it would be more accurate more often. This is probably not true anymore, so I agree it should probably be changed to default=system-tzdata on FreeBSD. I will commit an upgrade in Thursday, and unless anybody raise their voice against it, I will change the default setting then. Regards, Palle
Re: here does postgres take its timezone information from?
> 15 nov. 2019 kl. 21:32 skrev Thomas Munro : > > On Sat, Nov 16, 2019 at 8:38 AM Tom Lane <mailto:t...@sss.pgh.pa.us>> wrote: >> Palle Girgensohn writes: >>>> 6 nov. 2019 kl. 03:03 skrev Thomas Munro : >>>>> *It looks like FreeBSD's port uses the copy of tzdata from the >>>>> PostgreSQL source tree by default and thus that is what you get if you >>>>> install PostgreSQL with "pkg". That's not a great default IMHO and >>>>> should be changed. >> >>> The decision to use postgresql's tzdata is quite old. It was based on the >>> assumption that postgres is updated more frequently than the operating >>> system, and that for that reason it was better to use postgresql's tzdata, >>> since it would be more accurate more often. This is probably not true >>> anymore, so I agree it should probably be changed to default=system-tzdata >>> on FreeBSD. >>> I will commit an upgrade in Thursday, and unless anybody raise their voice >>> against it, I will change the default setting then. >> >> So it seems that that change was not entirely without fallout: >> >> https://www.postgresql.org/message-id/flat/16118-ef1e45e342c52416%40postgresql.org >> >> I don't think this is reason to revert the change, exactly, >> but it's a concern. I wonder why FreeBSD editorializes on >> the set of zone names? > > Ugh. It doesn't have the old backward compatibility names like > US/Pacific installed by default, which is a problem if that's what > initdb picked for your cluster (or you've stored references to any of > those names in other ways). Ogh, I had no idea of this limitation. One quick fix is to revert the change. Tom thinks this is not reason to revert. Would it be enough to edit the postgresql.conf to use the correct "modern" name for US/Pacific (PST?)? In rhar case, an update note might be sufficient? Palle
Re: here does postgres take its timezone information from?
> 16 nov. 2019 kl. 23:06 skrev Thomas Munro : > > On Sat, Nov 16, 2019 at 7:13 PM Tom Lane <mailto:t...@sss.pgh.pa.us>> wrote: >> Palle Girgensohn writes: >>> 15 nov. 2019 kl. 21:32 skrev Thomas Munro : >>>> Ugh. It doesn't have the old backward compatibility names like >>>> US/Pacific installed by default, which is a problem if that's what >>>> initdb picked for your cluster (or you've stored references to any of >>>> those names in other ways). >> >>> One quick fix is to revert the change. Tom thinks this is not reason to >>> revert. Would it be enough to edit the postgresql.conf to use the correct >>> "modern" name for US/Pacific (PST?)? In rhar case, an update note might be >>> sufficient? >> >> I think the "official" name of that zone is America/Los_Angeles. >> But initdb might seize on the US/Pacific alias, if available, >> because it's shorter. We've seen related problems with other >> time zone names, though usually it was just cosmetic and not a >> reason for the postmaster to fail to start. >> >> Yes, changing the zone name in postgresql.conf should be a sufficient >> fix. In theory, a FreeBSD user ought to know the "official" alias >> for their zone, since the rest of the system would expect that. >> So this is slightly tedious if initdb chose a non-official alias, >> but I don't think it's reason to panic. > > Perhaps the best thing would be to revert this for the older > PostgreSQL releases so that people doing minor version upgrades are > inconvenienced by a system that can't start up after "pkg upgrade", > but do it for 12 since not many people will be using that yet? That could be a way, yes. Any thoughts on this from others following this thread? Palle
