Should AT TIME ZONE be volatile?

From: Shay Rojansky <roji(at)roji(dot)org>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Should AT TIME ZONE be volatile?
Date: 2021-11-10 09:03:29
Message-ID: CADT4RqDVBbqSbQVH_v_vS5_9DPhjsfmQw07E+q-ddR_XfZjffw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Daniel Gustafsson 2021-11-10 09:11:56 Re: On login trigger: take three
Previous Message Dinesh Chemuduru 2021-11-10 08:28:58 Re: [PROPOSAL] new diagnostic items for the dynamic sql