title: PostgreSQL, triggers, and concurrency to enforce a temporal key
body: <p>I want to define a trigger in PostgreSQL to check that the inserted row, on a generic table, has the the property: "no other row exists with the same key in the same valid time" (the keys are  sequenced keys). In fact, I has already implemented it. But since the trigger has to scan the entire table, now i'm wondering: is there a need for a table-level lock? Or this is managed someway by the PostgreSQL itself?</p>
  
  <p>Here is an example. 
  In the upcoming PostgreSQL 9.0 I would have defined the table in this way:</p>
  
  <pre>
  CREATE TABLE medicinal_products
  (
  aic_code CHAR(9), -- sequenced key
  full_name VARCHAR(255),
  market_time PERIOD,
      EXCLUDE USING gist
      (aic_code CHECK WITH =,
      market_time CHECK WITH &&)
  );
  </pre>
  
  <p>but in fact I have been defined it like this:</p>
  
  <pre>
  CREATE TABLE medicinal_products
  (
  PRIMARY KEY (aic_code, vs),
  aic_code CHAR(9), -- sequenced key
  full_name VARCHAR(255),
  vs DATE NOT NULL,
  ve DATE,
  CONSTRAINT valid_time_range
          CHECK (ve > vs OR ve IS NULL)
  );
  </pre>
  
  <p>Then, I have written a trigger that check the costraint: "two distinct medicinal products can have the same code in two different periods, but not in same time".</p>
  
  <p>So the code: </p>
  
  <pre>
  INSERT INTO medicinal_products VALUES ('1','A','2010-01-01','2010-04-01');
  INSERT INTO medicinal_products VALUES ('1','A','2010-03-01','2010-06-01');
  </pre>
  
  <p>return an error.</p>