Re: Should AT TIME ZONE be volatile?

Shay Rojansky <roji(at)roji(dot)org> writes:
> 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.

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.

FWIW, I believe the text search operators are also labeled as if the
underlying configurations won't change, which of course isn't really true.

regards, tom lane