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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: James Keener <jim(at)jimkeener(dot)com>
Cc: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Re: BUG #18097: Immutable expression not allowed in generated at
Date: 2023-09-08 16:08:19
Message-ID: 1887197.1694189299@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

James Keener <jim(at)jimkeener(dot)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 non-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 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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Laurenz Albe 2023-09-08 17:45:17 Re: BUG #18099: ERROR: could not access status of transaction 4007513275
Previous Message David G. Johnston 2023-09-08 15:25:03 Re: BUG #18094: max max_connections cannot be set

Browse pgsql-hackers by date

  From Date Subject
Next Message Paul A Jungwirth 2023-09-08 17:26:59 Re: SQL:2011 application time
Previous Message Tom Lane 2023-09-08 15:31:22 Re: Possibility to disable `ALTER SYSTEM`