PLpgsql BEFORE UPDATE with select from same table

Question

我正在尝试创建一个PL/PGSQL触发器函数来检查新行的日期范围，以确保表中没有其他行的日期范围重叠(对于相同的product_id)．我已经成功地创建了函数并将其设置为BEFORE INSERT触发器，但我正试图弄清楚如何也将其设置为BEFORE UPDATE触发器，因为触发器内部的SELECT语句肯定会抛出异常，因为它符合重叠更新版本的日期的标准。

下面是我的函数:

CREATE OR REPLACE FUNCTION check_specials_dates() 
RETURNS trigger AS 
$$
DECLARE
BEGIN
  IF EXISTS (SELECT * FROM rar.product_specials 
           WHERE product_id = NEW.product_id 
             AND (
             (NEW.end_time between start_time and end_time) OR
             (NEW.start_time between start_time and end_time) OR
             (start_time between NEW.start_time and NEW.end_time))
  THEN
    RAISE EXCEPTION 
     'Cannot insert overlapping specials date for Product ID#%', NEW.product_id;   
 END IF; 
  RETURN NEW;
END
$$ LANGUAGE plpgsql;

我的想法是，IF EXISTS SELECT语句将返回一个匹配，因为它将匹配它试图更新的行。

正确吗?如果是这样，我该如何避开呢?

Answer 1

您使用的是哪个版本的PostgreSQL ?从9.0开始，您可以使用排除约束和cube/btree_gist扩展来实现所有这些。

要使用触发器实现这一点，我通常会使用after insert/update触发器，该触发器查看具有相同产品ID但具有插入/更新行不同主键的其他特殊值。即:

IF EXISTS (SELECT 1 FROM rar.product_specials
           WHERE product_specials.product_id = NEW.product_id
                 AND product_specials.product_special_id <> NEW.product_special_id
                 AND overlaps(NEW.start_time, NEW.end_time,
                              product_specials.start_time, product_specials.end_time))

如果您还没有为product_specials生成主键，我想这将证明添加一个主键是合理的。

排除约束解

(因为我一直需要提醒自己怎么做，所以我想把它写下来)

(需要注意的是，如果你的开始/结束时间是离散的(例如日期或者你可以将你的端点固定到足够大的粒度)，那么你可以在一个由触发器填充的辅助表上使用唯一性约束，而不是PostgreSQL，触发器和并发来强制一个临时键)

PostgreSQL可以使用其广泛的操作符/索引方法基础设施来强制通用的排除约束——如果任何其他行满足一组操作，则拒绝接受该行。传统的唯一性约束本质上是这种情况的一种特殊情况——如果该行中的某些值/值集与其他行的值/值集相等，则会导致该行被拒绝。

在您的示例中，如果与表中的其他行相比，product_id相等并且范围(start_time,end_time)重叠，则希望拒绝一行。

索引方法"gist"可以用来建立索引来满足这类请求(特别是重叠范围)。扩展"cube"提供了一种通用的数据类型，它是gist可索引的，而"btree_gist"为整数提供了一个gist索引方法，允许这两种类型在单个索引中组合。

所以在PostgreSQL 9.1中:

CREATE EXTENSION cube;
CREATE EXTENSION btree_gist;

(在9.0中，从contrib运行脚本)

下面是我测试的例子:

create table product_specials(product_special_id serial primary key,
    product_id int not null,
    start_time timestamp not null, end_time timestamp not null);
insert into product_specials(product_id, start_time, end_time)
 values(1, '2011-10-31 15:00:00', '2011-11-01 09:00:00'),
 (2, '2011-10-31 12:00:00', '2011-11-01 12:00:00'),
 (1, '2011-11-01 15:00:00', '2011-11-02 09:00:00');

现在，这些范围不重叠所以我们可以添加约束:

alter table product_specials add constraint overlapping_times exclude using gist (
  product_id with = ,
  cube(extract(epoch from start_time), extract(epoch from end_time)) with &&
);

cube(n1, n2)创建了一个从n1到n2的一维"立方体"。extract(epoch from t)将时间戳t转换为数字。如果有两个立方体，如果它们重叠，"&&"操作符返回true。因此，这将为每一行索引product_id和start_time/end_time"多维数据集"，并且每次插入/更新一行时，通过查找与新行值匹配的现有行来测试约束:使用"="操作符测试product_id，使用"&&"操作符测试start_time/end_time"多维数据集"。

如果您现在尝试插入冲突行，您将得到一个错误:

insert into product_specials(product_id, start_time, end_time)
  values(2, '2011-10-31 00:00:00', '2011-10-31 13:00:00');
ERROR:  conflicting key value violates exclusion constraint "overlapping_times"
DETAIL:  Key (product_id, cube(date_part('epoch'::text, start_time), date_part('epoch'::text, end_time)))=(2, (1320019200),(1320066000)) conflicts with existing key (product_id, cube(date_part('epoch'::text, start_time), date_part('epoch'::text, end_time)))=(2, (1320062400),(1320148800)).

如您所见，错误消息细节的易读性还有待改进!(@a_horse_with_no_name提到的文章http://thoughts.j-davis.com/2010/09/25/exclusion-constraints-are-generalized-sql-unique/中的"period"类型可能会产生更好的类型)但是，功能是完整的。

使用约束排除解决了一些我还没有解决的与锁有关的小问题。严格地说，在触发器中的"IF EXISTS…"查询之前，您应该执行SELECT 1 FROM rar.product_specials WHERE product_specials.product_id = NEW.product_id FOR SHARE，以确保在检查的约束与其事务提交之间，您正在测试的其他行都不会发生变化。然而，当同时插入两个新的特殊值时，仍然存在潜在的竞争条件，因为没有什么可锁定的——这就是使用辅助表来排除离散值的动机，但是随着排除空间变得更细粒度，这会产生缩放问题。

对于PostgreSQL 9.2，将有一个"范围"数据类型，这将消除使用多维数据集扩展或类似的需要。range类型还允许适当地指定边界在两端是打开还是关闭，而使用立方体边界总是在两端关闭(因此您需要做一些调整以避免关于日期范围重叠的错误)。与往常一样，Depesz在这个特性上有一个很好的帖子:http://www.depesz.com/index.php/2011/11/07/waiting-for-9-2-range-data-types/

例如:

create table product_specials(product_special_id serial primary key,
    product_id int not null,
    applicable_dates tsrange not null);
insert into product_specials(product_id, applicable_dates)
 values(1, tsrange('2011-10-31 15:00:00', '2011-11-01 09:00:00')),
 (2, tsrange('2011-10-31 12:00:00', '2011-11-01 12:00:00')),
 (1, tsrange('2011-11-01 15:00:00', '2011-11-02 09:00:00'));
alter table product_specials add exclude using gist (
  product_id with =,
  applicable_dates with &&
);

现在，如果您尝试插入一个冲突的行，您也会得到一个更可读的错误消息:

insert into product_specials(product_id, applicable_dates)    
  values(2, tsrange('2011-10-31 00:00:00', '2011-10-31 13:00:00'));
ERROR:  conflicting key value violates exclusion constraint "product_specials_product_id_applicable_dates_excl"
DETAIL:  Key (product_id, applicable_dates)=(2, ["2011-10-31 00:00:00","2011-10-31 13:00:00")) conflicts with existing key (product_id, applicable_dates)=(2, ["2011-10-31 12:00:00","2011-11-01 12:00:00")).

注意，您不必更改表的模式来使用这个新类型，因为您可以为函数调用的结果建立索引。因此，使用范围类型来强制约束的细节不必放在应用程序或触发器中。即:

alter table product_specials add exclude using gist (
  product_id with =,
  tsrange(start_time, end_time) with &&
);