PostgreSQL,触发器和并发执行临时密钥
我想在PostgreSQL中定义一个触发器来检查通用表上的插入行是否具有以下属性:“没有其他行存在相同的有效时间“(键是排序键)。实际上,我已经实现了它。但由于触发器必须扫描整个表,现在我想知道:是否需要表级锁?或者这是由PostgreSQL自己管理的?PostgreSQL,触发器和并发执行临时密钥
这里是一个例子。 在即将到来的PostgreSQL 9.0我会以这种方式定义表:
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 &&) );
但其实我一直是这样定义的:
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) );
于是,我写了检查触发费用:“两种不同的药品在两个不同的时期可以有相同的代码,但不是在同一时间”。
因此,代码:
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');
返回一个错误。
一个解决方案是使用第二个表来检测冲突,并用触发器填充该表。使用你加入到这个问题的模式:
CREATE TABLE medicinal_product_date_map(
aic_code char(9) NOT NULL,
applicable_date date NOT NULL,
UNIQUE(aic_code, applicable_date));
(注:这是第二次尝试,由于误读您的要求在第一时间轮希望这是正确的这个时候。)。
一些功能,以保持该表:
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
$$;
填充该表第一时间:
SELECT count(add_medicinal_product_date_range(aic_code, vs, ve))
FROM medicinal_products;
现在创建触发器更改medicinal_products后填充最新的地图:后插入调用add_ ,更新后调用clr_(旧值)和add_(新值),删除后调用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意愿陷阱的唯一性约束:
[email protected]@[local] =# INSERT INTO medicinal_products VALUES ('1','A','2010-01-01','2010-04-01');
INSERT 0 1
[email protected]@[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只存储微秒分辨率,但在每一微秒增加一个条目到映射表中,该产品适用于此是不实际的。尽管如此,你也许还可以得到比全表扫描更好的东西来检查重叠的时间戳记间隔,并且在仅仅寻找第一个间隔之前或之前不寻找......但是,对于易于离散的空间,我更喜欢这种方法,即IME也可以用于其他方面(例如需要快速查找哪些产品适用于某一天的报告)。
我也喜欢这种方法,因为这种方式充分利用了数据库的唯一性约束机制。另外,我觉得在主表的并发更新的情况下它会更可靠:在不锁定并发更新的情况下,验证触发器可能看不到冲突,并允许插入两个并发会话,即然后在两个交易的影响都可见时发生冲突。
好吧,我明白了。这是一个有趣的解决方案。但是,我必须存储大约30,000个产品。假设他们平均在市场上待了15年。在这种情况下,地图表将被填充164百万行,不是吗?这并不影响表演? – Hobbes 2010-06-10 07:58:47
那么,具有该行数的表格将对您的数据库产生* some *影响。尽管如此,除非产品表正在更新,否则这个额外的表格甚至不会被使用。 (我没有将代码放入触发器来检查相关列的更新,但也可以这样做)。 – araqnid 2010-06-10 10:55:23
即使安装了一个小的安装(例如默认32mb buffercache),我的开发机器上的一个非常快速的实验表明,这种大小没有问题 - 最初需要一段时间才能创建表,但似乎没有任何问题个别产品更新明显受损。 – araqnid 2010-06-10 11:21:12
只是一个想法,如果有效时间块可以用一个数字或东西被编码,创建的ID + TimeBlock UNIQUE索引是极快的,并解决所有表锁的问题。
它由PostgreSQL自己管理。在select中,它获得一个ACCESS_SHARE锁,这意味着您可以查询该表,但不执行更新。
彻底解决这可能会帮助你是使用高速缓存一样的Ehcache或memcached的存储ID/timeblock信息,而不是在所有使用PostgreSQL的。许多人可以坚持下来,以便他们能够在服务器重启时幸存下来,并且不会出现这种锁定行为。
我认为这实际上是我最终在我的答案中写的 - 在(id,applicable_date)上创建唯一索引,其中applicable_date是您的术语中的TimeBlock;即自1970-01-01以来的天数。尽管仅创建一个索引是不够的,索引需要存储必须以某种方式生成的(id,timeblock)的每个有效组合。 – araqnid 2010-06-09 19:38:46
如果可以计算时间段,则不需要生成时间段。例如:对于一小时的时间段计算自2000/01/01午夜以来的小时数。在桌子被填满时,索引会照顾ret。 Postgres有非常好的功能来切片和骰子日期/时间无论如何你想要的。 – 2010-06-09 20:40:48
这很好,如果数据表中的单个行对应于单个时间段,但在OP的情况下,他们不会 - 他将插入涵盖多天的范围,并且仅添加所涵盖的天/时间段之一是不够的。 – araqnid 2010-06-09 21:06:47
为什么你不能使用UNIQUE约束?会更快(这是一个索引),更容易。
使用两个DATE列存储有效时间。而且我不能使用UNIQUE约束,因为我必须确保句点不重叠。 – Hobbes 2010-06-09 08:20:23
即将发布9.0有一个解决方案,排除约束: http://www.postgresql.org/docs/9.0/static/ddl-constraints.html#AEN2530 – 2010-06-09 08:23:31
我知道,我也读过该功能在这里:http://www.pgcon.org/2010/schedule/events/201.en.html。 但在此期间... – Hobbes 2010-06-09 08:57:41
您在评论中提到一个答案,即使用DATE列存储有效时间。那么你能否澄清一下,实际上你只是在检查日期冲突,而不是完整的时间戳? – araqnid 2010-06-09 15:14:48