PLpgsql UPDATE之前，从同一表中进行选择 [英] PLpgsql BEFORE UPDATE with select from same table

问题描述

我正在尝试创建一个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语句将返回一个匹配项，因为它将在它尝试更新的行上匹配.

这是正确的吗?如果是这样，我该如何解决?

解决方案

您正在使用哪个版本的PostgreSQL?从9.0开始，您也许可以使用排除约束和cube/btree_gist扩展来实现所有这些功能.

要使用触发器来实现此目的，我通常会使用一个插入/更新后触发器，该触发器以相同的产品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可以使用其广泛的运算符/索引方法基础结构来实施广义的排除约束-如果任何其他行满足一组操作，则拒绝接受该行.传统的唯一性约束本质上是这种情况的一种特殊情况-如果行中的某个值/值集与其他行中的某个值/值集都等于，则它们会导致拒绝行. /p>

在您的情况下，如果与表中的其他行相比product_id相等并且范围(开始时间，结束时间)重叠，则希望拒绝该行.

可以使用索引方法"gist"来构建索引来满足这种要求(特别是重叠范围).扩展名"cube"为此提供了一种通用的数据类型索引，而"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)).

如您所见，错误消息详细信息的易读性有待改进！ (来自SELECT 1 FROM rar.product_specials WHERE product_specials.product_id = NEW.product_id FOR SHARE以确保您要测试的其他行均不能在检查的约束及其事务提交之间发生突变.但是，当同时插入两个新的特殊字符时，仍然没有竞争的条件，这是潜在的竞争条件-这是使用辅助表排除离散值的动机，但是作为排除空间存在缩放问题变得更细致.

对于 PostgreSQL 9.2 ，将有一个范围"数据类型，这将消除在此处使用多维数据集扩展名或类似内容的需要.范围类型还允许适当地指定边界在两端是打开还是封闭，而使用多维数据集边界始终在两端都是封闭的(因此，您需要做一些摆弄以避免日期范围重叠的错误).像往常一样，Depesz在此功能上有一篇不错的文章:

请注意，不必更改表的架构即可使用此新类型，因为您可以为函数调用的结果建立索引.因此，不必将范围类型用于强制约束的细节放在应用程序或触发器中.那就是:

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

I'm trying to create a PL/PGSQL trigger function to check a new row's date range to ensure there are no other rows within the table for which the date ranges overlap (for the same product_id) . I have successully created the function and set it as a BEFORE INSERT trigger, but I'm trying to figure out how to also set it as a BEFORE UPDATE trigger, since the SELECT statement inside the trigger is certain to throw an exception because it fits the criteria of overlapping the date of an updated version of itself.

Here is my function:

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;

My thought is that the IF EXISTS SELECT statement will return a match because it is going to match on the row it is trying to update.

Is this correct? If so, how can I get around it?

解决方案

Which version of PostgreSQL are you using? From 9.0 you might be able to implement all this using an exclusion constraint and the cube/btree_gist extensions.

To implement this using a trigger, I would generally use an after insert/update trigger which looked at other specials in with the same product ID but with a different primary key to the row being inserted/updated. That is:

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

If you don't already have a generated primary key for product_specials, imho this would justify adding one.

Exclusion constraint solution

(Because I keep needing to remind myself how to do it, so I want to write it down somewhere)

(Just a note that if your start/end times are discrete (e.g. dates or you can fix your endpoints to a large enough granularity) then you can use a uniqueness constraint on an auxiliary table populated by triggers instead: PostgreSQL, triggers, and concurrency to enforce a temporal key)

PostgreSQL can use its extensive operators/indexing methods infrastructure to enforce generalised exclusion constraints- refuse to accept a row if any other row satisfies a set of operations. Traditional uniqueness constraints are essentially a special case of this- they cause rows to be refused if some value/set of values from the row are all equal to a value/set of values from some other row.

In your case, you want a row to be refused if, compared to some other row in the table, product_id is equal and the range (start_time,end_time) overlaps.

The indexing method "gist" can be used to build indices to satisfy this kind of request (specifically, the overlapping ranges). The extension "cube" provides a general data type that is gist-indexable for this, and "btree_gist" provides a gist index method for integers, allowing the two types to be combined in a single index.

So in PostgreSQL 9.1:

CREATE EXTENSION cube;
CREATE EXTENSION btree_gist;

(in 9.0, run the scripts from contrib)

Here's the example I tested with:

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

Now, those ranges don't overlap so we can add the constraint:

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) creates a one-dimensional "cube" that extends from n1 to n2. extract(epoch from t) converts a timestamp t into a number. If you have two cubes, the "&&" operator returns true if they overlap. So this indexes the product_id and the start_time/end_time "cube" for each row, and every time you insert/update a row, the constraint is tested by looking for an existing row that matches the new row's values: testing product_id with the "=" operator, and the start_time/end_time "cube" with the "&&" operator.

If you try to insert a conflict row now, you will get an error:

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

As you can see, the legibility of the error message detail leaves something to be desired! (The "period" type from the article http://thoughts.j-davis.com/2010/09/25/exclusion-constraints-are-generalized-sql-unique/ that @a_horse_with_no_name mentioned presumably produces better ones) However, the functionality is intact.

Using a constraint exclusion solves some niggly problems to do with locking that I haven't addressed. Strictly, before your "IF EXISTS ..." query in the trigger, you should do a SELECT 1 FROM rar.product_specials WHERE product_specials.product_id = NEW.product_id FOR SHARE to ensure none of the other rows you are testing against can be mutated between the constraint being checked and its transaction committing. However, there is still potentially a race condition when inserting two new specials at the same time, where there's nothing to lock--- this was the motivation for using an auxiliary table to exclude discrete values, but that has scaling issues as the exclusion space becomes more granular.

With PostgreSQL 9.2 there will be a "range" data type that will remove the need to use the cube extension or similar here. The range type also allows proper specification of whether bounds are open or closed at each end, whereas using cube bounds are always closed at both ends (so you need to do some fiddling to avoid errors about date ranges overlapping). Depesz has a good post on this feature, as usual: http://www.depesz.com/index.php/2011/11/07/waiting-for-9-2-range-data-types/

For example:

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

Now if you try to insert a conflicting row, you get a more-readable error message too:

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

Note that you don't have to change the schema of the table to use this new type, since you can index the result of the function call. So the specifics of using the range type to enforce the constraint don't have to be put into the application or a trigger. That is:

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

这篇关于PLpgsql UPDATE之前，从同一表中进行选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！