Re: storing TZ along timestamps

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Right now we rely on the tzdata files on disk for things like
> pg_timezone_names and other accesses of TZ data; so the files are the
> authoritative source of TZ info. So we need to ensure that whenever the
> files are updated, the catalogs are updated as well.
> I think we could make this work if we "refreshed" the catalog from the
> files on SIGHUP if the directory changes (say, a new timezone is
> created).

(1) SIGHUP processing normally occurs outside any transaction.

(2) The only obvious way to ensure the "refresh" is done once, and not
once per backend, is to have the postmaster do it ... which is a
nonstarter for many reasons.

I'd suggest instead considering something like the pg_collations
approach: load up the catalog once at initdb. If the user really needs
to add to the set of accessible TZ names later, give him a tool to do
that. But it's 100% not worth either the implementation pain or the
cycles to try to auto-update the catalog, especially not as often as
once per SIGHUP.

BTW, what will you do about pg_upgrade? Ensuring the OID mapping
doesn't change seems like loads of fun.

regards, tom lane