PostgreSQL中PL / pgSQL函数的执行计划缓存 [英] Execution plan cache for PL/pgSQL functions in PostgreSQL

问题描述

如果我更改了另一个PL / pgSQL函数中使用的PL / pgSQL函数，PostgreSQL会为它们两者还是仅针对已更改的PostgreSQL重建执行计划？

If I change PL/pgSQL function that is in use in another PL/pgSQL function will the PostgreSQL rebuild execution plan for both of them or only for changed one?

说我有2个使用第三种功能。说函数 check_permission（user_id）被 get_company（user_id）和 get_location（user_id）使用。

Say I have 2 functions using third one. Say function check_permission(user_id) is used by get_company(user_id) and get_location(user_id).

我以某种方式缓存了他们的执行计划。

And I they got cached their execution plan somehow.

然后我更改 check_permission ，执行计划是否会缓存 get_company（user_id）和 get_location（user_id ）是否可以按需删除并重建？

And then I change check_permission, would the execution plan caches for get_company(user_id) and get_location(user_id) be deleted and rebuilt on demand?

推荐答案

PostgreSQL跟踪依赖关系，并且它非常积极地刷新缓存当事情改变时。

PostgreSQL tracks dependencies, and it flushes caches pretty aggressively when things change.

如果您更改一个函数，它将至少使依赖于该函数的所有函数的计划失效。实际上，IIRC只是完全刷新所有缓存的查询计划。

If you change a function, it'll invalidate at least the plans of all functions that depend on it. In practice, IIRC it just flushes all cached query plans entirely.

依赖于其他视图的视图，引用视图的准备好的语句等也是如此。

The same is true of views that depend on other views, prepared statements that reference views, etc.

如果您发现失败的情况，那么您发现了一个错误。请提供完整的可重复测试用例。

If you find a case where it fails to do so you have found a bug. Please report it with a complete reproducible test case.

这篇关于PostgreSQL中PL / pgSQL函数的执行计划缓存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！