PostgreSQL BEFORE UPDATE条件更新触发器操作是否具备原子性？

你尝试创建一个BEFORE UPDATE触发器，仅当新行的timestamp字段大于旧行时才允许更新（已知可以直接用UPDATE ... WHERE ...实现，但因特定原因选择触发器方案），对应的实现代码如下：

CREATE
OR REPLACE FUNCTION update_only_with_higher_timestamp()
  RETURNS trigger LANGUAGE plpgsql AS
$$BEGIN
 IF
NEW.timestamp < OLD.timestamp THEN
    RETURN OLD;
END IF;

RETURN NEW;
END;$$;


CREATE TRIGGER avoid_updating_rows_with_lower_timestamp
    BEFORE UPDATE
    ON table
    FOR EACH ROW
    EXECUTE PROCEDURE update_only_with_higher_timestamp();

问题解答 #

• 原子性说明
  该操作具备原子性。PostgreSQL中，触发器逻辑与触发它的UPDATE语句属于同一个事务：要么整个操作（包括触发器的条件判断、最终的行更新）全部成功提交，要么出现异常时全部回滚，不会产生部分执行的中间状态。

• 旧数据的一致性保障
  不会出现基于非最新旧数据判断的情况。PostgreSQL通过行级排他锁处理并发更新：当一个事务发起UPDATE时，会立即锁定目标行，其他并发的UPDATE请求会被阻塞，直到该事务提交或回滚。因此触发器中的OLD值一定是该行的最新已提交版本，条件判断会基于当前最新的行数据执行。

内容的提问来源于stack exchange，提问作者Lukáš Gemela