A good way to insert non-overlaping, roughly continuous integer intervals

Question

I have a table conforming to the following basic structure:

create table ranges (range_begin int, length int);

I want it to enforce the following invariants for any insert:

range_begin is unique (obvious primary key candidate)
An integer interval [range_begin, range_begin + length) is also unique and overlaps no other interval in the table (the crux of the question)

In my application, I want a client to supply only the desired length, whereupon range_begin can be computed.

For example, for a series of client specified lengths of 10, 90, 50, 20 the following outcome will be achieved:

 range_begin | length 
-------------+-----------
           0 |        10 
          10 |        90 
         100 |        50 
         150 |        20
(4 rows)

A simplistic approach will be to lock the table, select the row at max(range_begin), then insert whatever additional interval the client desires. But, this feels rather cumbersome and requires a helper function.

May there be a better way to achieve the described functionality? The inserts may happen concurrently from multiple clients.

justsomeguy · Accepted Answer · 2022-12-24T20:39:43.653

If you are looking to not have any overlapping intervals, this is one approach you can try that works (generated columns are available in postgres versions 12 and above, but you can still achieve the solution without it).

Here, a generated column to store the beginning and end of the range, which will help us enforce range boundaries later):

create table ranges (
  id serial,                                            
  range_begin int,                                       
  length int,                                            
  full_range int4range GENERATED ALWAYS AS (int4range(range_begin, range_begin + length)) STORED,
  primary key (id)
);
insert into ranges (range_begin, length) values(0, 10);
insert into ranges (range_begin, length) values(10, 90);
insert into ranges (range_begin, length) values(100, 50);
insert into ranges (range_begin, length) values(150, 20);

The table will populate as such:

 id | range_begin | length | full_range 
----+-------------+--------+------------
  1 |           0 |     10 | [0,10)
  2 |          10 |     90 | [10,100)
  3 |         100 |     50 | [100,150)
  4 |         150 |     20 | [150,170)

In a nutshell, these are how to interpret the notation for ranges:

inclusive lower bound [
exclusive lower bound (
inclusive upper bound ]
exclusive upper bound )

Now, in order to get the ranges to not be overlapping, you will need an exclusion constraint using the btree_gist extension:

 create extension btree_gist;
 alter table ranges                                     
   add constraint unique_full_range
     exclude using gist (full_range WITH &&);

If you try to insert a range that overlaps, you will get an error that looks like this:

 insert into ranges (range_begin, length) values(1, 11);
 ERROR:  conflicting key value violates exclusion constraint "unique_full_range"
 DETAIL:  Key (full_range)=([1,12)) conflicts with existing key (full_range)=([0,10)).

 insert into ranges (range_begin, length) values(10, 1);
 ERROR:  conflicting key value violates exclusion constraint "unique_full_range"
 DETAIL:  Key (full_range)=([10,11)) conflicts with existing key (full_range)=([10,100)).

 ERROR:  conflicting key value violates exclusion constraint "unique_full_range"
 DETAIL:  Key (full_range)=([88,91)) conflicts with existing key (full_range)=([10,100)).

 insert into ranges (range_begin, length) values(99, 3);
 ERROR:  conflicting key value violates exclusion constraint "unique_full_range"
 DETAIL:  Key (full_range)=([99,102)) conflicts with existing key (full_range)=([10,100)).

 insert into ranges (range_begin, length) values(168, 4);
 ERROR:  conflicting key value violates exclusion constraint "unique_full_range"
 DETAIL:  Key (full_range)=([168,172)) conflicts with existing key (full_range)=([150,170)).

 insert into ranges (range_begin, length) values(155, 100);
 ERROR:  conflicting key value violates exclusion constraint "unique_full_range"
 DETAIL:  Key (full_range)=([155,255)) conflicts with existing key (full_range)=([150,170)).

And finally, an insert on a range that does not overlap with any other works:

 insert into ranges (range_begin, length) values(170, 10);

Result:

  id | range_begin | length | full_range 
 ----+-------------+--------+------------
   1 |           0 |     10 | [0,10)
   2 |          10 |     90 | [10,100)
   3 |         100 |     50 | [100,150)
   4 |         150 |     20 | [150,170)
  16 |         170 |     10 | [170,180)
 (5 rows)

A good way to insert non-overlaping, roughly continuous integer intervals

1 Answers1