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 以构建函数时，关键字 IMMUTABLE 已被注入到 LANGUAGE SQL 语句之后.documentation 指出默认值是 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 添加到函数中，并且必须在开发过程中添加.

我猜发生的事情是这个函数正在被评估并且结果被缓存以进行优化，因为它被标记为 IMMUTABLE 向 Postgres 引擎表明在给定相同(空)参数列表的情况下，返回值不应改变.但是，当不在触发器中使用时，当直接在 INSERT 语句中使用时，该函数将返回一个不同的值五次，然后从那时起返回相同的值.这是由于某些优化算法会说如果一个 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.)

但我不完全清楚为什么.

But I am not altogether clear on why.

推荐答案

关键字 IMMUTABLE 是由 pgAdmin 或 Postgres 从不自动添加的.创建或替换该函数的人就是这样做的.

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

正确的波动率 对于给定的函数是 VOLATILE(也是默认值)，而不是 STABLE - 否则没有意义使用 clock_timestamp() 与 now() 或 CURRENT_TIMESTAMP 是 STABLE:它们在同一事务中返回相同的时间戳.手册:

The correct volatility for the given function is VOLATILE (also the default), not STABLE - or it wouldn't make sense to use clock_timestamp() which is VOLATILE in contrast to now() or CURRENT_TIMESTAMP which are STABLE: those return the same timestamp within the same transaction. The manual:

clock_timestamp() 返回实际的当前时间，因此它的即使在单个 SQL 命令中，值也会发生变化.

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

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

不适用于希望查询已修改行的 AFTER 触发器通过当前命令.

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

.. 因为重复评估触发器函数可以为同一行返回不同 结果.所以，不是STABLE.

.. because repeated evaluation of the trigger function can return different results for the same row. So, not STABLE.

你问:

你知道函数为什么正确返回五设置为 IMMUTABLE 时，在坚持第五个值之前的次数?

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 函数覆盖(voids function inlining):不同的查询计划仍然有意义.相关:

Bold emphasis mine. Doesn't seem to make sense for an IMMUTABLE function without input parameters. But the false label is overridden by the VOLATILE function in the body (voids function inlining): a different query plan can still make sense. Related:

• PostgreSQL 存储过程性能

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屋！