触发器过程的一致性（行触发器之前）Postgresql

Question

使用 Postgresql。

我尝试使用 TRIGGER 过程对 INSERT 进行一些一致性检查。

问题是……

“BEFORE INSERT FOR EACH ROW”能否保证每一行依次插入“检查”和“插入”？我是否需要在表上加额外的锁才能避免并发插入？

检查新的 row1 ->插入第1行->检查新的 row2 ->插入 row2

--
--

-- unexpired product name is unique.
CREATE TABLE product (
  "name"    VARCHAR(100) NOT NULL,
  "expired" BOOLEAN      NOT NULL
);

CREATE OR REPLACE FUNCTION check_consistency() RETURNS TRIGGER AS $$
  BEGIN
    IF EXISTS (SELECT * FROM product WHERE name=NEW.name AND expired='false') THEN
      RAISE EXCEPTION 'duplicated!!!';              
    END IF;
    RETURN NEW;
  END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_check_consistency
BEFORE INSERT ON product
  FOR EACH ROW EXECUTE PROCEDURE check_consistency();

--
INSERT INTO product VALUES("prod1", true);
INSERT INTO product VALUES("prod1", false);
INSERT INTO product VALUES("prod1", false); // exception!

这是可以的

 name | expired
 ==============
 p1   |  true
 p1   |  true
 p1   |  false

这不行

 name | expired
 ==============
 p1   |  true
 p1   |  false
 p1   |  false

或者也许我应该问， 如何使用触发器来实现“主要”或“唯一”约束式 SQL。

Answer 1

您的示例可以使用唯一索引来完成：

CREATE UNIQUE INDEX uq_check_consistency ON product ( name ) WHERE NOT expired;

这将导致第二个事务中的语句可能违反约束，并阻塞直到第一个事务提交或回滚。

编辑添加：

要使用触发器获得类似（或更复杂）的事务安全行为，您可以创建一个 CONSTRAINT 触发器，推迟到事务提交时间。这些触发器函数需要是 AFTER 触发器，检查您的约束是否被违反：

CREATE OR REPLACE FUNCTION after_check_consistency() RETURNS TRIGGER AS $
  BEGIN
    IF (SELECT count(*) FROM product WHERE name=NEW.name AND expired='false') > 1 THEN
      RAISE EXCEPTION 'duplicated!!!';              
    END IF;
    RETURN NULL;
  END;
$ LANGUAGE plpgsql;


CREATE CONSTRAINT TRIGGER trigger_check_consistency
AFTER INSERT OR UPDATE ON product
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE PROCEDURE after_check_consistency();

Answer 2

为什么不能使用唯一的密钥来强制执行此操作？