PostgreSQL 插入或更新触发函数波动类别

Question

假设，我的数据库中有 2 个表 (postgresql-9.x)

CREATE TABLE FOLDER (
    KEY BIGSERIAL PRIMARY KEY,
    PATH TEXT,
    NAME TEXT
);
CREATE TABLE FOLDERFILE (
    FILEID BIGINT,
    PATH TEXT,
    PATHKEY BIGINT
);

每当我插入或更新 FOLDERFILE 时，我都会自动从 FOLDER.KEY 更新 FOLDERFILE.PATHKEY:

CREATE OR REPLACE FUNCTION folderfile_fill_pathkey() RETURNS trigger AS $$
DECLARE
  pathkey bigint;
  changed boolean;
BEGIN
  IF tg_op = 'INSERT' THEN
     changed := TRUE;
    ELSE IF old.FILEID != new.FILEID THEN
       changed := TRUE;
    END IF;
  END IF;
  IF changed THEN
     SELECT INTO pathkey key FROM FOLDER WHERE PATH = new.path;
     IF FOUND THEN
       new.pathkey = pathkey;
     ELSE
       new.pathkey = NULL;
     END IF;
  END IF;
  RETURN new;
END
$$ LANGUAGE plpgsql VOLATILE;

CREATE TRIGGER folderfile_fill_pathkey_trigger AFTER INSERT OR UPDATE 
ON FOLDERFILE FOR EACH ROW EXECUTE PROCEDURE fcliplink_fill_pathkey();

所以问题是关于函数 folderfile_fill_pathkey() 的波动性。文件说

Any function with side-effects must be labeled VOLATILE

但据我所知——此函数不会更改它所依赖的表中的任何数据，因此我可以将此函数标记为IMMUTABLE。对吗？

如果我在同一事务中将许多行批量插入到 FOLDERFILE 中，IMMUTABLE 触发器函数会有任何问题吗，例如:

BEGIN;
INSERT INTO FOLDERFILE ( ... );
...
INSERT INTO FOLDERFILE ( ... );
COMMIT;

Answer 1

首先，正如@pozs 已经指出的那样，您提供的函数定义绝对是 STABLE 而不是 IMMUTABLE 因为它执行数据库查找。这意味着结果不仅来自输入参数(如 IMMUTABLE 所建议的那样)，还来自存储在您的 FOLDER 表中的数据(它必然会改变).根据文档:

STABLE indicates that the function cannot modify the database, and that within a single table scan it will consistently return the same result for the same argument values, but that its result could change across SQL statements. This is the appropriate selection for functions whose results depend on database lookups, parameter variables (such as the current time zone), etc.

其次，向触发器函数添加稳定性修饰符(IMMUTABLE/STABLE/VOLATILE)充其量只是为了说明目的，因为 AFAIK PostgreSQL实际上并没有执行任何可以保证使用它们的计划。 following post来自 pgsql-hackers 邮件列表似乎支持我的主张:

Volatility is a complete no-op for a trigger function anyway, as are other planner parameters such as cost/rows, because there is no planning involved in trigger calls.

总结一下:你现在最好避免在你的触发器(!)过程中使用稳定性关键字，因为包括它们似乎几乎没有什么好处，但会带来一些意想不到的警告/陷阱(见@的结尾pozs 的第一条评论)。