IMMUTABLE，STABLE和VOLATILE关键字如何影响函数的行为？ [英] How do IMMUTABLE, STABLE and VOLATILE keywords effect behaviour of function?

问题描述

我们写了一个函数 get_timestamp（）定义为

We wrote a function get_timestamp() defined as

CREATE OR REPLACE FUNCTION get_timestamp()
  RETURNS integer AS
$$
SELECT (FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 10) - 13885344000)::int;
$$
LANGUAGE SQL;

这在INSERT和UPDATE中用于在创建和修改的字段中输入或编辑值数据库记录。但是，我们发现在连续添加或更新记录时，它返回相同的值。

This was used on INSERT and UPDATE to enter or edit a value in a created and modified field in the database record. However, we found when adding or updating records consecutively it was returning the same value.

在检查pgAdmin III中的函数时，我们注意到在运行SQL来构建函数时，在LANGUAGE SQL语句之后注入了关键词IMMUTABLE。 文档说明默认值为VOLATILE（如果这些都不是出现，VOLATILE是默认假设）所以我不知道为什么IMMUTABLE注入，但是，将此更改为STABLE已解决了重复值的问题。

On inspecting the function in pgAdmin III we noted that on running the SQL to build the function the key word IMMUTABLE had been injected after the LANGUAGE SQL statement. The documentation states that the default is VOLATILE (If none of these appear, VOLATILE is the default assumption) so I am not sure why IMMUTABLE was injected, however, changing this to STABLE has solved the issue of repeated values.

注意：如接受的答案所述，IMMUTABLE从不会通过pgAdmin或Postgres添加到函数中，并且必须在开发过程中添加。

NOTE: As stated in the accepted answer, IMMUTABLE is never added to a function by pgAdmin or Postgres and must have been added during development.

我猜测发生了什么，这个函数被评估，并且结果被缓存进行优化，因为它被标记为IMMUTABLE，指示Postgres引擎返回给定相同（空）参数列表的值不应该改变。但是，当不在触发器中使用时，直接在INSERT语句中使用时，函数将返回一个不同的值FIVE次，然后从此返回相同的值。这是由于一些优化算法，如说如果一个IMMUTABLE函数在会话中使用5次以上，缓存结果为未来的调用？

I am guessing what was happening was that this function was being evaluated and the result was being cached for optimization, as it was marked IMMUTABLE indicating to the Postgres engine that the return value should not change given the same (empty) parameter list. However, when not used within a trigger, when used directly in the INSERT statement, the function would return a distinct value FIVE times before then returning the same value from then on. Is this due to some optimisation algorithm that says something like "If an IMMUTABLE function is used more that 5 times in a session, cache the result for future calls"?

要了解如何在Postgres函数中使用这些关键字，我们将不胜感激。对于我们来说，STABLE是正确的选项，因为我们在触发器中使用这个函数，或者有更多需要考虑的东西，例如文档：

Any clarification on how these keywords should be used in Postgres functions would be appreciated. Is STABLE the correct option for us given that we use this function in triggers, or is there something more to consider, for example the docs say:

（不适用于AFTER触发器，希望查询当前命令修改的行
。）

(It is inappropriate for AFTER triggers that wish to query rows modified by the current command.)

推荐答案

关键字 IMMUTABLE 从不由pgAdmin或Postgres自动添加。谁创建或替换了函数。

The key word IMMUTABLE is never added automatically by pgAdmin or Postgres. Who ever created or replaced the function did that.

正确的函数波动性（读取手册）设置为 VOLATILE STABLE - 或者使用 clock_timestamp（） VOLATILE 反对 now（） 或 CURRENT_TIMESTAMP ，它们定义 STABLE ：那些在同一事务中返回相同的时间戳，每个文档：

The correct function volatility (read the manual) setting for the given function is VOLATILE, not STABLE - or it wouldn't make sense to use clock_timestamp() which is VOLATILE as opposed to now() or CURRENT_TIMESTAMP, which are defined STABLE: those return the same timestamp within the same transaction, per documentation:

clock_timestamp（）返回实际当前时间，因此
值即使在单个SQL命令。

clock_timestamp() returns the actual current time, and therefore its value changes even within a single SQL command.

手动警告函数的波动性 STABLE ...

The manual warns that function volatility STABLE ...

不适用于 AFTER 触发器，希望查询当前命令修改的
行。

is inappropriate for AFTER triggers that wish to query rows modified by the current command.

..因为对同一行的触发器函数的重复计算可以返回不同的结果。所以，不要 STABLE 。

.. because the repeated evaluation of the trigger function for the same row can return different results. So, not STABLE. I don't think the warning is even needed there, since it's rather obvious.

你问：

当你设置为 IMMUTABLE 时，你有一个想法，为什么函数返回正确5
次，然后贴在第五个值上？

Do you have an idea as to why the function returned correctly five times before sticking on the fifth value when set as IMMUTABLE?

引用 Postgres Wiki ：

9.2，计划者将使用关于
参数发送（查询将在执行时计划），除非
查询执行多次，并且计划器决定
通用计划不是太贵

With 9.2, the planner will use specific plans regarding to the parameters sent (the query will be planned at execution), except if the query is executed several times and the planner decides that the generic plan is not too much more expensive than the specific plans.

大胆强调我。对于 IMMUTABLE 函数似乎没有意义（也不会有害处），但也许使用 VOLATILE 函数仍然触发初始重新计划。

这里更多的解释：

Bold emphasis mine. Doesn't seem to make sense for an IMMUTABLE function (doesn't do harm either), but maybe the use of a VOLATILE function within still triggers initial re-planing. (The last bit is just my speculation.)
More explanation here:

• PostgreSQL存储过程性能

• PostgreSQL Stored Procedure Performance

trunc（） 稍微快于 floor（），并且在这里也是这样，因为正数可以保证：

trunc() is slightly faster than floor() and does the same here, since positive numbers are guaranteed:

SELECT (trunc(EXTRACT(EPOCH FROM clock_timestamp()) * 10) - 13885344000)::int

这篇关于IMMUTABLE，STABLE和VOLATILE关键字如何影响函数的行为？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！