Overlapping time ranges constraints in 8.4

From: "EXT-Rothermel, Peter M" <Peter(dot)M(dot)Rothermel(at)boeing(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Overlapping time ranges constraints in 8.4
Date: 2012-08-24 17:59:56
Message-ID: 135FBA69693F3B46A0BE8D4FF49D2D834A7C9C0706@XCH-NW-01V.nw.nos.boeing.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a temporal data question that may be much easier to handle in version 9.x but I am stuck on version 8.4.

One table has a time range that is implemented as start_time and end_time columns of type TIMESTAMP with Timezone.
A second table has information that is needed to determine if there is a schedule conflict in the items in the first table.

I am considering using row level INSERT and UPDATE triggers to prevent overlapping time ranges.

TABLE campus (
id SERIAL,
foo BOOLEAN NOT NULL,
...
PRIMARY KEY (id)
) ;

TABLE B (
id SERIAL,
campus_id INTEGER NOT NULL,
start_time timestamp NOT NULL,
stop_time timestamp NOT NULL,
...
PRIMARY KEY (id),
FOREIGN KEY (campus_id) REFERENCES campus(id) ON DELETE CASCADE;
);

Records in table B are not considered overlapping if their campus has its foo column set to FALSE.

In my triggers (PL/pgSQL) I am using a expression like this

SELECT B.* INTO v_overlapping from INNER JOIN campus ON (campus.id=B.campus_id)
where campus.colA = 't' AND (campus.start_time, campus.stop_time) OVERLAPS (NEW.start_time, NEW.stop_TIME);

I am worried that the transaction serialization will not do the predicate locking that is needed for concurrent inserts/updates.
Can I use add a FOR UPDATE clause to my SELECT INTO expression in PL/pgSQL ?

Pete Rothermel

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kevin Grittner 2012-08-24 20:23:11 Re: Overlapping time ranges constraints in 8.4
Previous Message John D. West 2012-08-24 16:45:46 run function on server restart