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
