Fix wrong results from Memoize with a Param inside a cache key ExecReScanMemoize() purges the whole cache only when a Param changes which is not part of the cache key; a change to a cache-key Param is assumed to be handled by looking up a different cache entry. To that end, create_memoize_plan() put every Param appearing in the cache key expressions into keyparamids. That assumption holds only when the cache key values determine the Param's value.
It fails for an upper-level Param which is buried inside a larger cache-key expression and also appears elsewhere in the subplan. With a cache key of "t2.hundred + t0.ten" and a filter "t1.twenty = t0.ten", different (t2.hundred, t0.ten) pairs produce the same cache-key value while the filter selects different rows, so one cache entry wrongly serves both. Since t0.ten was included in keyparamids, the cache was never purged when it changed. Also, pull_paramids() collected PARAM_EXTERN Params too, whose numbers are unrelated to those of PARAM_EXEC Params. An external Param in a cache key could therefore keep a change to an unrelated executor Param from purging the cache. To fix, put into keyparamids only the Params whose values the cache keys determine. These are the nestloop Params which replace the outer rel's Vars in the cache keys, as the inner scan can only reference the outer rel through the cache keys, and, in binary mode only, a Param which is a cache key by itself. In logical mode the hash equality operator may not distinguish values that affect the results differently. Any other Param now purges the cache when it changes. Also make pull_paramids() collect PARAM_EXEC Params only. Purging alone would empty the cache on every rescan of the enclosing plan whenever a buried Param changes, even when the Param is used nowhere else and the old code was correct and kept its cache entries across rescans. So also make each buried Param a cache key by itself, which puts Memoize into binary mode, so that cache entries survive across rescans with the same Param value. A buried Param whose type can't be hashed is left to the purging, rather than giving up on Memoize. Back-patch to all supported branches. Reported-by: Jacob Brazeal <[email protected]> Author: Richard Guo <[email protected]> Reviewed-by: David Rowley <[email protected]> Reviewed-by: Yilin Zhang <[email protected]> Discussion: https://postgr.es/m/CA+COZaAHZT7T2G2F0-drdQB+StWJxOzqeDxp=esp+yuiy-k...@mail.gmail.com Backpatch-through: 14 Branch ------ REL_14_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/de7d7d4df80acceca1195d549bd2887d5bc8ab26 Modified Files -------------- src/backend/optimizer/path/joinpath.c | 97 ++++++++++++++++++++------ src/backend/optimizer/plan/createplan.c | 27 +++++++- src/backend/optimizer/util/clauses.c | 6 +- src/include/nodes/execnodes.h | 3 +- src/include/nodes/plannodes.h | 3 +- src/test/regress/expected/memoize.out | 116 ++++++++++++++++++++++++++++++++ src/test/regress/sql/memoize.sql | 59 ++++++++++++++++ 7 files changed, 286 insertions(+), 25 deletions(-)
