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