How to avoid overlapping intervals with PostgreSQL

This page contains historical information or deprecated articles.

This page contains code or administrative procedures which do not produce correct results due to failure to consider the effects of concurrency. This may be due to unnoticed or unhandled race conditions, incorrect use of locking or similar problems. Be wary of assuming that the code works even if tested, since the race conditions may not be obvious. See the talk page for further discussion.

Important caveat: This solution provides no protection from concurrent inserts clashing whatsoever. Consider using exclusion constraints, available in PostgreSQL 9.0. If you must emulate them in a prior version, the following PDF file discusses approaches to the problem, and their attendant limitations: http://www.pgcon.org/2010/schedule/attachments/136_exclusion_constraints2.pdf

by Matteo Nastasi
16th July 2001

Introduction

When creating a pocket agenda application, it's quite useful to have a mechanism inhibiting intervals overlapping (i.e. first job starting at 10:00, lasting two hours and second job starting at 11:00, lasting two hours). This article is a possible implementation method for PostgreSQL 6.5 and maybe for PostgreSQL 7.1. ;)

After two sweaty weeks worried about a trigger which did not work, I can finally and proudly explain the problem I had and how I solved it.

I am writing a web based application (Apache, PHP, PostgreSQL) for managing a music hall reservation system and here I faced a problem I'd never thought about. How to avoid two different reservations overlapping? In this case a "unique key" constraint is not enough to guarantee reservations will be correctly inserted because the key "oneness" itself cannot guarantee that two reservations are not partially overlapped (you can only see this with a very aimed SELECT).

Possible solutions:

1. Developing extra dedicated checks in the php code.
2. Adding to the reservation table a trigger which inhibits the feared overlapping.

Since the problem was generally recognized, and since the extra checking structure would have been boring but necessary for safely guaranteeing the right insertion and change, I opted for the latter (b).

What's a trigger anyway?

PostgreSQL allows you to launch a procedure before or after you do an INSERT, UPDATE or DELETE transaction. This launched procedure is called a trigger. If you specify to launch it before though, it could fail and force the whole operation to fail. What coding languages can this trigger be written with?

PostgreSQL allows you to write the trigger associated procedure in C, SQL, PL/pgSQL, or PL/TCL. I used PL/pgSQL because I thought it was the quickest way to implement the trigger, but it could also be written in the other languages (being better performing in C (Code is better ;-)).

To have a trigger equipped table in PostgreSQL you must (in order) :

1. Create the table
2. Create the procedure used by the trigger
3. Create the trigger

Here you have the minimum working example; more documentation can be found at www.postgresql.org in the PL/pgSQL chapter ( for pgsql 6.5 Programmer's Guide, III volume, cap. 42 ).

Creating the trigger

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

Here you created the table. idr is a unique record identifier (you cannot have two records with the same identifier). start is the starting reservation time. len is the reservation time.

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

Here you created the function to be triggered. I'll promptly give you a private explanation after you have studied that chapter in the PostgreSQL manual. Some tips :

• TG_OP contains "INSERT" when the trigger is called for inserting and "UPDATE" when it's called for a change. This distinction was necessary since in the change case the record had to be excluded from the "SELECT" because at procedure call time it's still not modified in the table.

• NEW is a type RECORD macro which contains the new record.

• OLD is a type RECORD macro which contains the old record (in cases where changes are occuring).

• FOUND is a boolean macro which is TRUE if the select has found at least one record.

• RAISE exits the procedure and returns a NULL value (for which the trigger makes the insert or change fail).

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

This linked the function as a trigger to the table.

Now, when launching sequentially these two example insertions :

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

the second will fail.

Goal!!

Thanks to

DaDoS e NdK di ircnet/#programmazione

Copyright © 2001 Matteo Nastasi (matteo.nastasi@milug.org), Translated by Martino Bana (martino.bana@milug.org)

Copy and distribution of this paper is allowed in any way as long as the copyright notice and this notice are present.