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 |
