Re: Why cann't simplify stable function in planning phase?

On 2/8/23 09:59, tender wang wrote:
> Hi hackers,
>    In evaluate_function(), I find codes as shown below:
>
>  /*
>   * Ordinarily we are only allowed to simplify immutable functions. But for
>   * purposes of estimation, we consider it okay to simplify functions that
>   * are merely stable; the risk that the result might change from planning
>   * time to execution time is worth taking in preference to not being able
>    * to estimate the value at all.
>    */
> if (funcform->provolatile == PROVOLATILE_IMMUTABLE)
>     /* okay */ ;
> else if (context->estimate && funcform->provolatile == PROVOLATILE_STABLE)
>      /* okay */ ;
> else
>     return NULL;
>
> The codes say that stable function can not be simplified here(e.g.
> planning phase). 
> I want to know the reason why stable function can not be simplified in
> planning phase.
> Maybe show me a example that it will be incorrect for  a query if
> simplify stable function in 
> planning phases.
>

A function is "stable" only within a particular execution - if you run a
query with a stable function twice, the function is allowed to return
different results.

If you consider parse analysis / planning as a separate query, this
explains why we can't simply evaluate the function in parse analysis and
then use the value in actual execution. See analyze_requires_snapshot()
references in postgres.c.

Note: To be precise this is not about "executions" but about snapshots,
and we could probably simplify the function call with isolation levels
that maintain a single snapshot (e.g. REPEATABLE READ). But we don't.

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company