postgresqlconstraintsexclusion-constraint

Read/write lock on range using EXCLUDE constraint


I have a table:

CREATE EXTENSION btree_gist;
CREATE TABLE excllock (
  id BIGSERIAL PRIMARY KEY,
  myrange INT8RANGE NOT NULL,
  key UUID NOT NULL,
  isread BOOLEAN NOT NULL,
  EXCLUDE USING gist (
    myrange WITH &&,
    key WITH =
  )
);

I'd like it to behave this way:

So in effect there can only be multiple isread=TRUE or one isread=FALSE at a time (or no records matching (key, myrange) at all), essentially creating a read/write lock on the key-range pair.

Examples:

-- Insert multiple read locks (allowed)
INSERT INTO excllock (myrange, key, isread) VALUES ('[1,10)', '00000000-0000-0000-0000-000000000001', TRUE);
INSERT INTO excllock (myrange, key, isread) VALUES ('[5,15)', '00000000-0000-0000-0000-000000000001', TRUE);

-- Try inserting a write lock (should fail due to read locks)
INSERT INTO excllock (myrange, key, isread) VALUES ('[1,10)', '00000000-0000-0000-0000-000000000001', FALSE);

-- Insert a write lock on a free range
INSERT INTO excllock (myrange, key, isread) VALUES ('[100,1000)', '00000000-0000-0000-0000-000000000001', FALSE);

-- Try to insert a read lock inside the write range (should fail)
INSERT INTO excllock (myrange, key, isread) VALUES ('[105,900)', '00000000-0000-0000-0000-000000000001', TRUE);

I thought of using where (NOT isread), but it will ignore isread rows completely.

Is there a clever way of creating this kind of constraint?


Solution

  • Slightly more text, but it's faster and lighter, and the whole logic is handled by one <>, plus an additional but simpler, partial constraint:
    demo at db<>fiddle

    CREATE TABLE excllock (
      id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      myrange INT8RANGE NOT NULL,
      key UUID NOT NULL,
      isread BOOLEAN NOT NULL,
      CONSTRAINT e1_locks_cant_mix EXCLUDE USING gist (
        myrange WITH &&,
        key WITH = ,
        isread with <> --same-type locks can overlap, different lock types can't
        ),
      CONSTRAINT e2_writes_cant_overlap EXCLUDE USING gist (
        myrange WITH &&,
        key WITH =
        )WHERE(NOT isread)--write locks can't overlap write locks
    );                    --combined with `e1`, can't overlap anything
    
    1. The above decreases the storage footprint of this table. In the attached demo, compared to the int8range idea, the example above took about 15% less space (11MB down from 13MB) to accommodate the table and indexes enforcing the constraints.
    2. It's a bit faster in general. Test also took around 15% less time (13.01s down from 15.21s) to complete compared to the other example.
    3. You're missing a test case. My understanding is you don't want to allow multiple write (not isread) entries/locks to share a key and range. Without a test case for that, it's enough to
      EXCLUDE USING GiST(
        myrange WITH &&
       ,key WITH =
       ,isread with <>  )
      
      And that would pass all of the ones you showed, way faster than either method, with a smaller footprint.

    With the missing test case included, both methods shown so far are logically equivalent, rejecting the exact same 26'877 entries out of 60'000 generated from the same seed. That being said, what @EAW showed seems like a cooler method (also, less lines if you're into that) and I share your enthusiasm.