consistency of Trigger Procedure (before row trigger) Postgresql

Using Postgresql.

I try to use TRIGGER procedure to make some consistency check on INSERT.

The question is ......

whether "BEFORE INSERT FOR EACH ROW" can make sure each row to insert "checked" and "inserted" one after another? do I need extra lock on table to survive from concurrent insert?

check for new row1 -> insert row1 -> check for new row2 -> insert 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!

this is OK

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

This is not OK

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

or maybe I should ask, how can I use Trigger to implement "Primary" or "Unique" constraint-like SQL.

---

Your example can be done with a unique index:

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

This will result in a statement within a second transaction that would that could inviolate the constraint, blocking till the first transaction commits or rolls back.

Edited to add:

To get similar (or more complex) transactionally safe behaviour with triggers, you can create a CONSTRAINT trigger, that is deferred till transaction commit time. These trigger functions need to be AFTER triggers, checking whether your constraint has been violated:

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();

---

Why can't you use a unique key to enforce this?