Обсуждение: BUG #18097: Immutable expression not allowed in generated at

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

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?

James Keener <jim@jimkeener.com> writes:
> The issue here, though, is that it works as an expression for an index, but doesn't work as a generated column unless
Iexplicitly cast it to text (which should have happened implicitly anyways). (The cast is turning a non-immutable
expressionto be immutable.) 

The reason that the generated expression fails is that (if you don't
explicitly cast to text) then it relies on anytextcat(anynonarray,text),
which is only stable, and can't be marked any more restrictively because
depending on the type of the non-text argument the corresponding output
function might not be immutable.

But then why doesn't the equivalent index definition spit up?
I found the answer in indexcmds.c's CheckMutability():

    /*
     * First run the expression through the planner.  This has a couple of
     * important consequences.  First, function default arguments will get
     * inserted, which may affect volatility (consider "default now()").
     * Second, inline-able functions will get inlined, which may allow us to
     * conclude that the function is really less volatile than it's marked. As
     * an example, polymorphic functions must be marked with the most volatile
     * behavior that they have for any input type, but once we inline the
     * function we may be able to conclude that it's not so volatile for the
     * particular input type we're dealing with.
     *
     * We assume here that expression_planner() won't scribble on its input.
     */
    expr = expression_planner(expr);

    /* Now we can search for non-immutable functions */
    return contain_mutable_functions((Node *) expr);

Applying expression_planner() solves the problem because it inlines
anytextcat(anynonarray,text), resolving that the required cast is
numeric->text which is immutable.  The code for generated expressions
omits that step and arrives at the less desirable answer.  I wonder
where else we have the same issue.

            regards, tom lane