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

On Wed, 2023-02-08 at 16:59 +0800, tender wang wrote:
>    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.

Query planning and query execution can happen at different times and using
different snapshots, so the result of a stable function can change in the
meantime.  Think of prepared statements using a generic plan.

Yours,
Laurenz Albe

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

Tomas Vondra <tomas.vondra@enterprisedb.com> writes:
> 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.

We don't do that because, in fact, execution is *never* done with the same
snapshot used for planning.  See comment in postgres.c:

         * While it looks promising to reuse the same snapshot for query
         * execution (at least for simple protocol), unfortunately it causes
         * execution to use a snapshot that has been acquired before locking
         * any of the tables mentioned in the query.  This creates user-
         * visible anomalies, so refrain.  Refer to
         * https://postgr.es/m/flat/5075D8DF.6050500@fuzzy.cz for details.

I'm not entirely sure that that locking argument still holds, but having
been burned once I'm pretty hesitant to try that again.

            regards, tom lane

Hi,

On 2023-02-08 09:57:04 -0500, Tom Lane wrote:
> Tomas Vondra <tomas.vondra@enterprisedb.com> writes:
> > 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.
> 
> We don't do that because, in fact, execution is *never* done with the same
> snapshot used for planning.  See comment in postgres.c:
> 
>          * While it looks promising to reuse the same snapshot for query
>          * execution (at least for simple protocol), unfortunately it causes
>          * execution to use a snapshot that has been acquired before locking
>          * any of the tables mentioned in the query.  This creates user-
>          * visible anomalies, so refrain.  Refer to
>          * https://postgr.es/m/flat/5075D8DF.6050500@fuzzy.cz for details.
> 
> I'm not entirely sure that that locking argument still holds, but having
> been burned once I'm pretty hesitant to try that again.

Because we now avoid re-computing snapshots, if there weren't any concurrent
commits/aborts, the gain would likely not be all that high anyway.

We should work on gettting rid of the ProcArrayLock acquisition in case we can
reuse the snapshot, though. I think it's doable safely, but when working on
it, I didn't succeed at writing a concise description as to why it's sfae, so
I decided that the rest of the wins are big enough to not focus on it then and
there.

Greetings,

Andres Freund