Re: Proposal for better support of time-varying timezone abbreviations

I wrote:
> I got interested in the problem discussed in
> http://www.postgresql.org/message-id/20714.1412456604@sss.pgh.pa.us
> to wit:
>> It's becoming clear to me that our existing design whereby zone
>> abbreviations represent fixed GMT offsets isn't really good enough.
>> I've been wondering whether we could change things so that, for instance,
>> "EDT" means "daylight time according to America/New_York" and the system
>> would consult the zic database to find out what the prevailing GMT offset
>> was in that zone on that date. This would be a lot more robust in the
>> face of the kind of foolishness we now see actually goes on.

Attached is an updated patch for this, incorporating my previous work on
changing the representation of datetkn. The code changes are complete
I believe, but I've not touched the user documentation yet, nor updated
the tznames data files except for changing MSK for testing purposes.

Some notes:

* There wasn't any reasonable way to return the required information about
a dynamic zone abbreviation from DecodeSpecial(). However, on looking
closely I found that of the existing callers of DecodeSpecial(), only two
actually relied on it to find both timezone abbreviations and regular
keywords. The other callers only wanted one case or the other. So it
seemed to me that it'd be best to split the abbreviation-lookup behavior
apart from keyword-lookup. DecodeSpecial() now does only the latter, and
there's a new function DecodeTimezoneAbbrev() to do the former. This
avoids touching any code that doesn't care about timezone abbreviations,
and should be a bit faster for those cases too (but about the same speed
otherwise).

* I found that there were pre-existing bugs in DetermineTimeZoneOffset()
for cases in which a zone changed offset but neither the preceding nor
following time segment was marked as DST time. This caused strange
behaviors for cases like Europe/Moscow's 2011 and 2014 time changes.
This is not terribly surprising because we never thought about zone
changes other than simple DST spring-forward/fall-back changes when
that code was written.

* I've not touched ecpg except for cosmetic changes to keep the struct
definitions in sync, and to fix the previously-mentioned bogus free()
attempt. I doubt that it would be worth teaching ecpg how to access the
zic timezone database --- the problem of configuring where to find those
files seems like more trouble than it's worth given the lack of
complaints. I'm not sure what we should do about the obsolete timezone
abbreviations in its table.

* timetz_zone() and DecodeTimeOnly() are not terribly consistent about
how they resolve timezones when not given a date. The former resolves
based on the value of time(NULL), which is a moving target even within
a transaction (which is why the function is marked volatile I guess).
The latter gets today's date as of start of transaction (so at least
it's stable) and then merges that m/d/y with the h/m/s from the time
value. That behavior does not seem terribly well thought out either:
on the day of, or day before or after, a DST change it's likely to
produce strange results, especially if the session timezone is different
from the zone specified in the input. I think we ought to consider
changing one or both of these, though it's not material for back-patching.

* In HEAD, we might want to extend the pg_timezone_abbrevs view to have
a column that shows the underlying zone for a dynamic abbreviation.
The attached patch just shows the abbreviation's behavior as of current
time (defined as now()), which is sort of good enough but certainly not
the whole truth.

Comments?

regards, tom lane