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:
myrange
on key
can be added as long as all of them are isread
.isread=FALSE
is added, one of three things should happen:
isread
row present in the database, so the constraint should fail (concurrent read access already exists, cannot write).isread=FALSE
row present, so the constraint fails (concurrent write access already exists, cannot 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?
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
int8range
idea, the example above took about 15% less space (11MB
down from 13MB
) to accommodate the table and indexes enforcing the constraints.13.01s
down from 15.21s
) to complete compared to the other example.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.