On Thursday, September 7, 2023, PG Bug reporting form <
noreply@postgresql.org> wrote:
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:
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?
Casting very much can be a non-immutable activity, dates being the prime example, and I presume going from numeric to text is indeed defined to be stable hence the error. This is probably due to needing to consult locale for deciding how to represent the decimal places divider. This is one of the few places, assuming you write the function to set an environment fixing locale to some know value like you did with the time zones, where creating an immutable function around a stable expression makes sense.
David J.