PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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:

  • Multiple rows with overlapping myrange on key can be added as long as all of them are isread.
  • In case a new row with isread=FALSE is added, one of three things should happen:
    1. There is already at least one isread row present in the database, so the constraint should fail (concurrent read access already exists, cannot write).
    2. There is already an isread=FALSE row present, so the constraint fails (concurrent write access already exists, cannot write).
    3. There are no rows matching exclusion constraint: the row is added (no concurrent access, can write).

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?

Tags:

2

Answers


  1. 0

    This can be achieved by adding an additional parameter to your exclusion constraint to represent the read/write status of the lock. I have modeled a read lock with a range encompassing only the id of that row, which is guaranteed to be unique and never overlap the id of any other row. I have then used an infinite range to represent a write lock which will overlap all other ranges regardless of whether they represent read or write locks.

    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 =,
    (CASE WHEN isread THEN int8range(id, id, '[]')
                      ELSE int8range(null, null) END) WITH &&
  )
);
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search