Should AT TIME ZONE be volatile?

Поиск
Список
Период
Сортировка
От Shay Rojansky
Тема Should AT TIME ZONE be volatile?
Дата
Msg-id CADT4RqDVBbqSbQVH_v_vS5_9DPhjsfmQw07E+q-ddR_XfZjffw@mail.gmail.com
обсуждение исходный текст
Ответы Confused with PostgreSQL on Synology NAS  (chris <yuanzefuwater@126.com>)
Re: Should AT TIME ZONE be volatile?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-hackers
Greetings hackers.

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.

Sample table creation DDL:

CREATE TABLE events (
    id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    local_timestamp timestamp without time zone NOT NULL,
    utc_timestamp timestamp with time zone GENERATED ALWAYS AS (local_timestamp AT TIME ZONE time_zone_id) STORED,
    time_zone_id text NULL
);

For comparison, SQL Server does consider AT TIME ZONE to be non-deterministic, and therefore does not allow it in stored generated columns (it does allow it in non-stored ones).

Shay

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Dinesh Chemuduru
Дата:
Сообщение: Re: [PROPOSAL] new diagnostic items for the dynamic sql
Следующее
От: Daniel Gustafsson
Дата:
Сообщение: Re: On login trigger: take three