Re: Why cann't simplify stable function in planning phase?
Hi, On 2023-02-08 09:57:04 -0500, Tom Lane wrote: > Tomas Vondra 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.6050...@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
Re: Why cann't simplify stable function in planning phase?
Tomas Vondra 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.6050...@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
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
Re: 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
