Make Memoize cache keys only of Params from outer query levels Commit 708eb5158 made each PARAM_EXEC Param buried inside a larger Memoize cache key expression a cache key by itself, and, in master only, commit f8da64e6d extended that to the Params in the memoized relation's base quals, targetlist and join clauses. Both meant to catch Params supplied by an outer query level, but they took every PARAM_EXEC Param they found, including the output Params of initPlans and SubPlans.
When an initPlan's output Param is made a cache key, it is evaluated on every cache probe, which runs the initPlan even where the query doesn't need it, such as in a CASE arm that is never taken, and can raise an error the query shouldn't raise. A SubPlan's output Params have a value only while the SubPlan evaluates its testexpr, so making a cache key of one fails with "plan should not reference subplan's variable". To fix, make cache keys only of the Params listed in some outer query level's plan_params, as those hold values supplied by outer levels and are always safe to evaluate. Any other Param is left to purge the cache when it changes. Back-patch to all supported branches. Reported-by: Fujii Masao <[email protected]> Author: Richard Guo <[email protected]> Discussion: https://postgr.es/m/CAHGQGwHdaau1KooFbY10=mdhktfvgpmprdiwnpee6bwu7wp...@mail.gmail.com Backpatch-through: 14 Branch ------ REL_17_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/db7e885d093f96ffdf70bbb5dae92fa2fa13a031 Modified Files -------------- src/backend/optimizer/path/joinpath.c | 34 ++++++++++++++++++-- src/test/regress/expected/memoize.out | 58 +++++++++++++++++++++++++++++++++++ src/test/regress/sql/memoize.sql | 24 +++++++++++++++ 3 files changed, 114 insertions(+), 2 deletions(-)
