Thread: Overlapping time ranges constraints in 8.4

"EXT-Rothermel, Peter M" <Peter.M.Rothermel@boeing.com> wrote:

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

That is unfortunate.  Getting this to work correctly in 8.4 will
probably be a lot more work than upgrading to 9.1 and getting it to
work there.

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

To get that sort of predicate locking in PostgreSQL, you must be
using version 9.1 or later and the transactions must be using the
serializable transaction isolation level.  But for something like
this, you might be better off using the "exclusion constraint"
feature of 9.0 and later.  (The only reason I say "might" instead of
"would" is that I'm not sure that feature can handle the
complication of the boolean in a separate table.)

> Can I use add a FOR UPDATE clause to my SELECT INTO expression in
> PL/pgSQL ?

That won't help -- it just locks the actual rows read; it doesn't
protect against insertion of conflicting rows.  You could use
explicit locking to actually serialize the transactions which do
this.  There are other options, but none of them are pretty.

-Kevin

I thought that this was going to be tricky.

Perhaps I could use rules to populate a shadow table that is like a INNER JOIN of the two tables. This would
consolidatethe Boolean on the separate table into the same table that holds the time ranges. In version 8.4 I would
stillneed to use explicit locking on this shadow table but in 9.x an exclusion constraint may have a better chance of
working.

Thanks for your help on this one.

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

To get that sort of predicate locking in PostgreSQL, you must be using version 9.1 or later and the transactions must
beusing the serializable transaction isolation level.  But for something like this, you might be better off using the
"exclusionconstraint" 
feature of 9.0 and later.  (The only reason I say "might" instead of "would" is that I'm not sure that feature can
handlethe complication of the boolean in a separate table.) 

> Can I use add a FOR UPDATE clause to my SELECT INTO expression in
> PL/pgSQL ?

That won't help -- it just locks the actual rows read; it doesn't protect against insertion of conflicting rows.  You
coulduse explicit locking to actually serialize the transactions which do this.  There are other options, but none of
themare pretty. 

-Kevin


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, Aug 27, 2012 at 11:46 AM, EXT-Rothermel, Peter M
<Peter.M.Rothermel@boeing.com> wrote:
> I thought that this was going to be tricky.
>
> Perhaps I could use rules to populate a shadow table that is like a INNER JOIN of the two tables. This would
consolidatethe Boolean on the separate table into the same table that holds the time ranges. In version 8.4 I would
stillneed to use explicit locking on this shadow table but in 9.x an exclusion constraint may have a better chance of
working.

Are you expecting a lot of concurrent activity?  If not, your best bet
is probably to serialize all writes with an elevated lock.  Barring
that, you are probably going to have to write pending scheduling
activity into a queue and have a subsequent transaction consume the
queue and enforce the overlapping rules.

Also, it's worth giving a quick shout to the upcoming 9.2 (which is
going into release candidate status) range types feature for solving
these types of problems.

merlin