postgresql - 触发器过程的一致性(行触发器之前)Postgresql

Question

使用 Postgresql。

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

问题是……

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

检查新行1 -> 插入行1 -> 检查新行2 -> 插入行2

--
--

-- 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 trigger ，这被推迟到事务提交时间。这些触发器函数需要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();