如何在 PostgreSQL 中避免时间段重叠

此页面包含 **历史信息或已弃用文章**。

此页面包含由于未考虑并发影响而无法产生正确结果的代码或管理过程。这可能是由于未注意到或未处理的竞争条件、锁定使用不当或类似问题造成的。请注意，即使代码经过测试，也不要假设代码能正常工作，因为竞争条件可能并不明显。请参阅 讨论页面 获取更多讨论。

重要说明：此解决方案无法提供任何保护，防止并发插入发生冲突。请考虑使用排斥约束，该约束在 PostgreSQL 9.0 中可用。如果您必须在早期版本中模拟它们，以下 PDF 文件讨论了解决该问题的方法及其局限性：http://www.pgcon.org/2010/schedule/attachments/136_exclusion_constraints2.pdf

作者：Matteo Nastasi
2001 年 7 月 16 日

简介

在创建口袋日程应用程序时，拥有一个阻止时间段重叠的机制非常有用（例如，第一个工作从 10:00 开始，持续两个小时，第二个工作从 11:00 开始，持续两个小时）。本文是针对 PostgreSQL 6.5 及其可能适用于 PostgreSQL 7.1 的实现方法。 ;)

经过两周的汗水和焦虑，我终于可以自豪地解释我遇到的问题以及我是如何解决的。

我正在编写一个基于 Web 的应用程序（Apache、PHP、PostgreSQL）用于管理音乐厅预订系统，我在这里遇到了一个我从未想过的问题。如何避免两次不同的预订重叠？在这种情况下，“唯一键”约束不足以保证预订将被正确插入，因为键的“唯一性”本身无法保证两次预订没有部分重叠（只有通过非常有针对性的 SELECT 才能看到这一点）。

可能的解决方案

1. 在 php 代码中开发额外的专用检查。
2. 在预订表中添加一个阻止冲突的触发器。

由于这个问题得到了普遍认可，并且额外的检查结构虽然枯燥乏味，但对于安全地保证正确的插入和更改是必要的，所以我选择了后者 (b)。

触发器是什么？

PostgreSQL 允许您在执行 INSERT、UPDATE 或 DELETE 事务之前或之后启动一个过程。此启动过程称为触发器。如果您指定在之前启动它，它可能会失败并导致整个操作失败。此触发器可以使用哪些编码语言编写？

PostgreSQL 允许您使用 C、SQL、PL/pgSQL 或 PL/TCL 编写关联的触发器过程。我使用 PL/pgSQL，因为我认为这是实现触发器的最快方法，但它也可以用其他语言编写（在 C 中的性能更好（代码更好 ;-）。

要让 PostgreSQL 中的表配备触发器，您必须（按顺序） 

1. 创建表
2. 创建触发器使用的过程
3. 创建触发器

这里您拥有最小的工作示例；更多文档可以在 www.postgresql.org 的 PL/pgSQL 章节中找到（对于 pgsql 6.5 程序员指南，第 III 卷，第 42 章）。

创建触发器

CREATE TABLE tritab (
    idr int4,
    nome text,
    start int4,
    len int4);

在这里您创建了表。idr 是唯一的记录标识符（您不能有两个记录具有相同的标识符）。start 是预订的开始时间。len 是预订时间。

CREATE FUNCTION trifun () RETURNS OPAQUE AS '
DECLARE
    myrec RECORD;
BEGIN
    /* se insert verifica se ci sono record vecchi che si intersecano */
    /* if insert verifies if there are old intersecting records */
    IF TG_OP = ''INSERT'' THEN
        SELECT * INTO myrec FROM tritab WHERE
            start < (NEW.start + NEW.len) AND
            (start + len) > NEW.start;
        IF FOUND THEN
            RAISE EXCEPTION ''INSERT failed:
            intersection with record % at (%,%)'',
            myrec.idr, myrec.start, myrec.len;
        END IF;
    END IF;
    /* se update come insert con in piu il constraint che l id non sia quello del record modificato */
    /* if update like insert plus check on id, assuring that it's not the one modified */
    IF TG_OP = ''UPDATE'' THEN
        SELECT * INTO myrec FROM tritab WHERE
            start < (NEW.start + NEW.len) AND
            (start + len) > NEW.start AND
            idr <> OLD.idr;
        IF FOUND THEN
            RAISE EXCEPTION ''UPDATE failed:
            intersection with record % at (%,%)'',
            myrec.idr, myrec.start, myrec.len;
        END IF;
    END IF;
    RETURN NEW;
END;
' LANGUAGE 'plpgsql';

在这里您创建了要触发的函数。在您学习了 PostgreSQL 手册中的该章节之后，我将很快为您提供私人解释。一些提示 

• TG_OP 在触发器被调用以插入时包含“INSERT”，在触发器被调用以更改时包含“UPDATE”。这种区别是必要的，因为在更改的情况下，必须从“SELECT”中排除该记录，因为在过程调用时，该记录在表中仍未修改。

• NEW 是一个类型 RECORD 的宏，它包含新记录。

• OLD 是一个类型 RECORD 的宏，它包含旧记录（在发生更改的情况下）。

• FOUND 是一个布尔宏，如果 select 找到了至少一条记录，则为 TRUE。

• RAISE 退出过程并返回一个 NULL 值（触发器会因其而使插入或更改失败）。

CREATE TRIGGER tritri BEFORE INSERT OR UPDATE ON tritab
FOR EACH ROW EXECUTE PROCEDURE trifun();

这将函数作为触发器链接到表。

现在，当顺序启动这两个示例插入时 

INSERT INTO tritab ( idr, nome, start, len ) values ( 1, 'one', 10, 2);
INSERT INTO tritab ( idr, nome, start, len ) values ( 2, 'two', 11, 2);

第二个将失败。

目标！！

感谢

DaDoS 和 NdK 来自 ircnet/#programmazione

版权 © 2001 Matteo Nastasi ([email protected])，由 Martino Bana ([email protected]) 翻译

只要版权声明和此声明存在，即可以任何方式复制和分发本文档。