PostgreSQL 15.2无需触发器跟踪最后更新时间失效问题排查

问题描述 #

我尝试重现某博客中的示例，在PostgreSQL 15.2中执行以下代码：

create or replace function im_now () returns timestamptz as $$  
  select CURRENT_TIMESTAMP;  
$$ language sql strict immutable;  

create table t1 (id int primary key, info text, crt_time timestamp, 
mod_time timestamp GENERATED ALWAYS AS (im_now()) stored); 

insert into t1 (id, info, crt_time) values (1,'test', now());  

select * from t1;

update t1 set info='a' where id=1;

select * from t1;

执行后发现mod_time仅在创建记录时赋值，更新记录后完全没变化，也没有任何报错。

问题根源 #

问题出在im_now函数的**immutable属性**上：

• PostgreSQL里，immutable函数被定义为：输入相同则输出永远固定，不受数据库状态、时间变化影响。PostgreSQL会对这类函数的结果做永久缓存，不会在每次调用时重新计算。
• 但你用immutable标记了一个返回当前时间的函数，这本身就违背了immutable的定义——当前时间是随时间变化的，根本不可能“永远不变”。
• 对于存储型生成列来说，只有当它依赖的列发生变化时才会重新计算。但因为im_now被标记为immutable，PostgreSQL认定它的结果永远不会变，所以哪怕你更新了行里的其他字段，也不会重新执行这个函数来更新mod_time。

修复方法 #

把函数的属性从immutable改成stable或者volatile：

• stable表示函数在单个事务内结果保持不变，完全符合当前时间的特性（同一事务内多次调用CURRENT_TIMESTAMP结果一致）；
• volatile表示函数结果每次调用都可能变化，也能满足需求，但stable更贴合场景。

修改后的函数代码：

create or replace function im_now () returns timestamptz as $$  
  select CURRENT_TIMESTAMP;  
$$ language sql strict stable;

删除原有的函数和表后重新执行，再做插入、更新操作，mod_time就会在更新行时自动刷新了。

额外提示：如果只是要实现“记录最后更新时间”的功能，其实不用自定义函数，直接把生成列的表达式写成CURRENT_TIMESTAMP，或者用触发器来实现更灵活的更新逻辑，效果是一样的。

内容的提问来源于stack exchange，提问作者Vedran Mornar