PostgreSQL插入或更新触发器函数的不稳定性类别

Question

假设我在我的数据库(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()的易变性。文档说：

任何具有副作用的函数必须标记为易变

但据我所知，这个函数并没有改变它所依赖的表中的任何数据，所以我可以将这个函数标记为IMMUTABLE。这样做是否正确？

如果我在同一个事务中批量插入许多行到FOLDERFILE中，是否会对不可变触发器函数造成任何问题？

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

- 2can

---

这个“函数”（严格来说，它的主体）不是IMMUTABLE的，它只是STABLE的（它从您的FOLDER表中选择，该表会随着时间改变：您的函数不能保证使用相同的“参数”获得相同的结果）--但是，它是一个触发器，而不是经典的函数：函数的不稳定性只影响查询优化，但如果您优化触发器，则它只会给您带来麻烦，例如使用STABLE。对于希望查询由当前命令修改的行的AFTER触发器来说，这是不合适的。 - pozs

谢谢@pozs！我实际上并没有完全理解“IMMUTABLE”和“STABLE”的区别，所以现在我需要在我的数据库中修复一些东西。非常感谢你的评论。 - 2can

@pozs，就我所知- 将我的函数从“VOLATILE”更改为“STABLE”不会给我带来任何性能优势？如果你能把它写成答案，那就很好了，这样我可以接受它，否则我必须自己做。 - 2can

很难预测STABLE是否实际上表现更好。查询优化取决于许多因素。（在您的情况下，即在函数内不查询触发器的目标表FOLDERFILE，这应该没有关系）。但是，如果您想要精确，应该使用真实数据测试两种变体。 - pozs

好的，谢谢。我稍后会进行基准测试并在这里发布结果。 - 2can

Answer 1

首先，正如@pozs已经指出的那样，您提供的函数定义绝对是STABLE而不是IMMUTABLE，因为它执行数据库查找。这意味着结果不仅仅是从输入参数派生而来(IMMUTABLE应该是这个意思)，而且还与存储在FOLDER表中的数据相关(这些值可能会发生变化)。根据文档:

STABLE表示函数不能修改数据库，并且在单个表扫描中，它将为相同的参数值一致地返回相同的结果，但其结果可能会跨SQL语句发生变化。 这是用于结果取决于数据库查找、参数变量(例如当前时区)等函数的正确选择。

其次，向您的触发器函数添加稳定性修饰符(IMMUTABLE/STABLE/VOLATILE)最多只起到说明作用，因为据我所知PostgreSQL实际上并没有进行任何计划来证明它们的使用是必要的。来自pgsql-hackers邮件列表的以下帖子似乎支持我的说法:

对于触发器函数，不稳定性对于计划来说完全是无效的，其他计划参数(如cost/rows)也是如此，因为在触发器调用中没有涉及到计划。

总之，因为包含这些关键词似乎没有多大好处但可能会导致一些意想不到的陷阱(请参见@pozs第一个评论的结尾)，所以您最好暂时避免在触发器过程中使用稳定关键字。

- Priidu Neemre