PostgreSQL，触发器和并发来执行临时键 [英] PostgreSQL, triggers, and concurrency to enforce a temporal key

问题描述

我想在PostgreSQL中定义一个触发器，以检查在通用表上插入的行是否具有以下属性：在相同的有效时间内不存在具有相同键的其他行（键为顺序键） 。实际上，我已经实现了它。但是由于触发器必须扫描整个表，所以现在我想知道：是否需要表级锁？还是由PostgreSQL本身来管理？

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?

这里是一个例子。
在即将发布的PostgreSQL 9.0中，我将以这种方式定义表：

Here is an example. In the upcoming PostgreSQL 9.0 I would have defined the table in this way:

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

但实际上我的定义是这样的：

but in fact I have been defined it like this:

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

然后，我编写了一个触发器来检查成本消耗：两种截然不同的药品在两个不同的时期可以具有相同的代码，但是

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".

因此代码：

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

返回错误。

推荐答案

一种解决方案是使用第二张表检测冲突，并使用触发器填充该表。使用您添加到问题中的模式：

One solution is to have a second table to use for detecting clashes, and populate that with a trigger. Using the schema you added into the question:

CREATE TABLE medicinal_product_date_map(
   aic_code char(9) NOT NULL,
   applicable_date date NOT NULL,
   UNIQUE(aic_code, applicable_date));

（注意：这是第二次尝试，因为第一次读错了您的要求。希望是对的

(note: this is the second attempt due to misreading your requirement the first time round. hope it's right this time).

一些用于维护此表的函数：

Some functions to maintain this table:

CREATE FUNCTION add_medicinal_product_date_range(aic_code_in char(9), start_date date, end_date date)
RETURNS void STRICT VOLATILE LANGUAGE sql AS $$
  INSERT INTO medicinal_product_date_map
  SELECT $1, $2 + offset
  FROM generate_series(0, $3 - $2)
$$;
CREATE FUNCTION clr_medicinal_product_date_range(aic_code_in char(9), start_date date, end_date date)
RETURNS void STRICT VOLATILE LANGUAGE sql AS $$
  DELETE FROM medicinal_product_date_map
  WHERE aic_code = $1 AND applicable_date BETWEEN $2 AND $3
$$;

并在第一时间填充表格：

And populate the table first time with:

SELECT count(add_medicinal_product_date_range(aic_code, vs, ve))
FROM medicinal_products;

现在创建触发器以在对medicinal_products进行更改后填充日期图：在插入调用add_之后，在更新调用之后

Now create triggers to populate the date map after changes to medicinal_products: after insert calls add_, after update calls clr_ (old values) and add_ (new values), after delete calls clr_.

CREATE FUNCTION sync_medicinal_product_date_map()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
    PERFORM clr_medicinal_product_date_range(OLD.aic_code, OLD.vs, OLD.ve);
  END IF;
  IF TG_OP = 'UPDATE' OR TG_OP = 'INSERT' THEN
    PERFORM add_medicinal_product_date_range(NEW.aic_code, NEW.vs, NEW.ve);
  END IF;
  RETURN NULL;
END;
$$;
CREATE TRIGGER sync_date_map
  AFTER INSERT OR UPDATE OR DELETE ON medicinal_products
  FOR EACH ROW EXECUTE PROCEDURE sync_medicinal_product_date_map();

medicinal_product_date_map的唯一性约束将捕获同一天使用相同代码添加的所有产品：

The uniqueness constraint on medicinal_product_date_map will trap any products being added with the same code on the same day:

steve@steve@[local] =# INSERT INTO medicinal_products VALUES ('1','A','2010-01-01','2010-04-01');
INSERT 0 1
steve@steve@[local] =# INSERT INTO medicinal_products VALUES ('1','A','2010-03-01','2010-06-01');
ERROR:  duplicate key value violates unique constraint "medicinal_product_date_map_aic_code_applicable_date_key"
DETAIL:  Key (aic_code, applicable_date)=(1        , 2010-03-01) already exists.
CONTEXT:  SQL function "add_medicinal_product_date_range" statement 1
SQL statement "SELECT add_medicinal_product_date_range(NEW.aic_code, NEW.vs, NEW.ve)"
PL/pgSQL function "sync_medicinal_product_date_map" line 6 at PERFORM

这取决于所检查的值是否具有离散空间-这就是为什么我询问日期的原因与时间戳。尽管从技术上讲时间戳是离散的，因为Postgresql仅存储微秒分辨率，但产品适用的每微秒在映射表中添加一个条目是不切实际的。

This depends on the values being checked for having a discrete space- which is why I asked about dates vs timestamps. Although timestamps are, technically, discrete since Postgresql only stores microsecond-resolution, adding an entry to the map table for every microsecond the product is applicable for is not practical.

具有说，您可能还可以得到比全表扫描更好的功能来检查重叠的时间戳记间隔，并且在不寻找间隔的情况下仅寻找第一个间隔……有些技巧，但是对于简单的离散空间我我更喜欢这种方法，IME在其他方面也可以派上用场（例如，报告需要快速找到在某一天适用的产品）。

Having said that, you could probably also get away with something better than a full-table scan to check for overlapping timestamp intervals, with some trickery on looking for only the first interval not after or not before... however, for easy discrete spaces I prefer this approach which IME can also be handy for other things too (e.g. reports that need to quickly find which products are applicable on a certain day).

我也喜欢之所以采用这种方法，是因为以这种方式利用数据库的唯一性约束机制是正确的。另外，我觉得在对主表进行并发更新的情况下它将更加可靠：如果不锁定表以防止并发更新，则验证触发器有可能看不到冲突并允许在两个并发会话中插入，即然后当两个交易的效果都可见时就会发生冲突。

I also like this approach because it feels right to leverage the database's uniqueness-constraint mechanism this way. Also, I feel it will be more reliable in the context of concurrent updates to the master table: without locking the table against concurrent updates, it would be possible for a validation trigger to see no conflict and allow inserts in two concurrent sessions, that are then seen to conflict when both transaction's effects are visible.

这篇关于PostgreSQL，触发器和并发来执行临时键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！