From: Tom Lane <tgl@sss.pgh.pa.us>
To: James Keener <jim@jimkeener.com>
cc: "David G. Johnston" <david.g.johnston@gmail.com>,
        "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #18097: Immutable expression not allowed in generated at
In-reply-to: <118FF128-C9CA-490C-8E67-65888EE8283F@jimkeener.com>
References: <18097-ebb179674f22932f@postgresql.org>
 <CAKFQuwZW=wyr_Nfi8Ww+U-jxdh4tvztvMjT23iv2Ly+Oc0n5eQ@mail.gmail.com>
 <118FF128-C9CA-490C-8E67-65888EE8283F@jimkeener.com>
Comments: In-reply-to James Keener <jim@jimkeener.com>
	message dated "Fri, 08 Sep 2023 11:22:07 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1887196.1694189299.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 08 Sep 2023 12:08:19 -0400
Message-ID: <1887197.1694189299@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1887197.1694189299%40sss.pgh.pa.us>
Precedence: bulk

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 I explicitly cast it to text=
 (which should have happened implicitly anyways). (The cast is turning a n=
on-immutable expression to 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 vola=
tile
     * 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 t=
he
     * particular input type we're dealing with.
     *
     * We assume here that expression_planner() won't scribble on its inpu=
t.
     */
    expr =3D 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