> > 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 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