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
* /messages/by-id/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
* /messages/by-id/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