Re: Should AT TIME ZONE be volatile?

> > It seems that PostgreSQL 14 allows using the AT TIME ZONE operator
within
> > generated column definitions; according to the docs, that means the
> > operator is considered immutable. However, unless I'm mistaken, the
result
> > of AT TIME ZONE depends on the time zone database, which is external and
> > can change. I think that means that generated column data can become
> > out-of-date upon tz database changes.
>
> 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.

> I'm not sure how big a deal this really is for timestamps. The actual
> stored time is either UTC or local time, and those are generally pretty
> well-defined. If you make the wrong choice of which one to store for
> your use-case, you might be unhappy.

The example I'm working with, is storing a user-provided local timestamp
and time zone ID, but also having an index generated column in UTC, to be
able to order all rows on the global timeline regardless of time zone (see this
blog post
<https://codeblog.jonskeet.uk/2019/03/27/storing-utc-is-not-a-silver-bullet/>
by Jon Skeet for some context). If the time zone database changes after the
generated column is computed, the UTC timestamp is out of sync with regards
to the reality. This seems unsafe.

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.

In any case, if this is the intended behavior, no problem - I was a bit
surprised by it, and found the difference with SQL Server interesting.

Shay