Re: Should AT TIME ZONE be volatile?

Shay Rojansky <roji(at)roji(dot)org> writes:
>> Yeah, we generally don't take such hazards into account. The poster
>> child here is that if we were strict about this, text comparisons
>> couldn't be immutable, because the underlying collation rules can
>> (and do) change from time to time. That's obviously unworkable.

> Thanks for the explanation Tom. I get the logic, though I think there may
> be a difference between "dependent on external rules which may
> theoretically change but almost never actually do" and "dependent on
> something that really does change frequently"... Countries really do change
> their daylight savings quite frequently, whereas I'm assuming collation
> rules are relatively immutable and changes are very rare.

Meh. Yeah, there are some banana republics that change their DST rules
at the drop of a hat. More serious governments realize that there are
costs to that. For comparison's sake, glibc have modified their
collation rules significantly (enough for us to hear complaints about
it) at least twice in the past decade. That's considerably *more*
frequent than DST law changes where I live.

> On the other hand, it could be argued that this should be allowed, and that
> it should be the user's responsibility to update generated columns when the
> time zone database changes (or periodically, or whatever). Users always
> have the option to define a trigger anyway, so we may as well make this
> easier via a generated column.

Yeah, it's not clear that forbidding this would make anyone's life any
better. If you want an index on the UTC equivalent of a local time,
you're going to have to find a way to cope with potential mapping
changes. But refusing to let you use a generated column doesn't
seem to help that.

regards, tom lane