Re: BUG #18097: Immutable expression not allowed in generated at

On Thursday, September 7, 2023, PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 18097
> Logged by: Jim Keener
> Email address: jim(at)jimkeener(dot)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.