BUG #18097: Immutable expression not allowed in generated at
PG Bug reporting form <noreply@postgresql.org>
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: jim@jimkeener.com
Date: 2023-09-08T03:47:49Z
Lists: pgsql-bugs, pgsql-hackers
Commits
Same data as JSON:
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
Ensure we preprocess expressions before checking their volatility.
- f07a3039c72b 16.2 landed
- abd1b1325d60 12.18 landed
- 9bd0f74eac19 14.11 landed
- 9057ddbefe7e 15.6 landed
- 743ddafc7124 17.0 landed
- 5c11104c7d20 13.14 landed
The following bug has been logged on the website: Bug reference: 18097 Logged by: Jim Keener Email address: jim@jimkeener.com PostgreSQL version: 15.0 Operating system: Linux Description: Given this table: CREATE TABLE test_table ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, created_at timestamptz NOT NULL DEFAULT now() ); The following work: * alter table test_table add created_local_y text GENERATED ALWAYS AS (EXTRACT(isoyear FROM created_at AT TIME ZONE 'America/New_York')) STORED; * alter table test_table add created_local_w text GENERATED ALWAYS AS (EXTRACT(week FROM created_at AT TIME ZONE 'America/New_York')) STORED; * alter table test_table add created_local text GENERATED ALWAYS AS (EXTRACT(isoyear FROM created_at AT TIME ZONE 'America/New_York')::text || '|' || EXTRACT(week FROM created_at AT TIME ZONE 'America/New_York')::text) STORED; * CREATE INDEX ON test_table ((EXTRACT(isoyear FROM created_at AT TIME ZONE 'America/New_York') || '|' || EXTRACT(week FROM created_at AT TIME ZONE 'America/New_York'))); However, the following DOES NOT work with an error of (ERROR: generation expression is not immutable): * alter table test_table add created_local text GENERATED ALWAYS AS (EXTRACT(isoyear FROM created_at AT TIME ZONE 'America/New_York') || '|' || EXTRACT(week FROM created_at AT TIME ZONE 'America/New_York')) STORED; Given that casting shouldn't "increase" the immutability of an expression, and expression indexes need also be immutable afaik, I think that there is a bug somewhere here? Thank you, Jim