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_16_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/f1d27a92252b894a44db0d793ffc5976b4a44060 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(-)
