Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
/* --without the missing test case, this would be enough
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 =,
    isread with <>
  )
);
*/
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 EXCLUDE USING gist (
    myrange WITH &&,
    key WITH = ,
    isread with <> --same-type locks can overlap, different lock types can't
    ),
  CONSTRAINT e2 EXCLUDE USING gist (
    myrange WITH &&,
    key WITH =
    )WHERE(NOT isread)--write locks can't overlap write locks
);                    --combined with `e1`, can't overlap anything

CREATE TABLE

-- ALLOWED read lock on a free range
INSERT INTO excllock (myrange, key, isread) 
  VALUES ('[1,10)', '00000000-0000-0000-0000-000000000001', TRUE);

INSERT 0 1

-- ALLOWED read lock overlapping read-locked range
INSERT INTO excllock (myrange, key, isread) 
  VALUES ('[5,15)', '00000000-0000-0000-0000-000000000001', TRUE);

INSERT 0 1

-- CONFLICTING write lock overlapping read-locked range
INSERT INTO excllock (myrange, key, isread) 
  VALUES ('[1,10)', '00000000-0000-0000-0000-000000000001', FALSE);

ERROR:  conflicting key value violates exclusion constraint "e1"
DETAIL:  Key (myrange, key, isread)=([1,10), 00000000-0000-0000-0000-000000000001, f) conflicts with existing key (myrange, key, isread)=([1,10), 00000000-0000-0000-0000-000000000001, t).

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

INSERT 0 1

-- CONFLICTING read lock inside the write-locked range
INSERT INTO excllock (myrange, key, isread) 
  VALUES ('[105,900)', '00000000-0000-0000-0000-000000000001', TRUE);

ERROR:  conflicting key value violates exclusion constraint "e1"
DETAIL:  Key (myrange, key, isread)=([105,900), 00000000-0000-0000-0000-000000000001, t) conflicts with existing key (myrange, key, isread)=([100,1000), 00000000-0000-0000-0000-000000000001, f).

--this test case was missing

-- CONFLICTING write lock overlapping a write-locked range
INSERT INTO excllock (myrange, key, isread) 
  VALUES ('[105,900)', '00000000-0000-0000-0000-000000000001', FALSE);

ERROR:  conflicting key value violates exclusion constraint "e2"
DETAIL:  Key (myrange, key)=([105,900), 00000000-0000-0000-0000-000000000001) conflicts with existing key (myrange, key)=([100,1000), 00000000-0000-0000-0000-000000000001).

DROP TABLE IF EXISTS excllock;
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 EXCLUDE USING gist (
    myrange WITH &&,
    key WITH = ,
    isread with <> --same-type locks can overlap, different lock types can't
    ),
  CONSTRAINT e2 EXCLUDE USING gist (
    myrange WITH &&,
    key WITH =
    )WHERE(NOT isread)--write locks can't overlap write locks
);                    --combined with `e1`, can't overlap anything

select setseed(.43);
drop table if exists timing;
create table timing as select clock_timestamp() as started;
do $f$
declare
  i int; loops int:=6e4;
begin
  for i in 0..loops loop
    begin
    execute format(
            $q$INSERT INTO excllock (myrange, key, isread) 
               VALUES ( INT8RANGE(%1$s,%1$s+(random()*500)::bigint,'[)')
                       ,'00000000-0000-0000-0000-000000000001'
                       ,random()>.2)
            $q$,(random()*1e4)::bigint);
    exception when others then null;
    end;
  end loop;
end$f$;

DROP TABLE

CREATE TABLE

setseed

SELECT 1

DROP TABLE

SELECT 1

DO

?column?

00:00:13.006983

SELECT 1

count

33123

SELECT 1

pg_total_relation_size
pg_size_pretty

11386880
11 MB

SELECT 1

DROP TABLE IF EXISTS excllock;
CREATE TABLE excllock (
  id BIGINT GENERATED ALWAYS AS IDENTITY 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 &&
  )
);

select setseed(.43);
drop table if exists timing;
create table timing as select clock_timestamp() as started;
do $f$
declare
  i int; loops int:=6e4;
begin
  for i in 0..loops loop
    begin
    execute format(
            $q$INSERT INTO excllock (myrange, key, isread) 
               VALUES ( INT8RANGE(%1$s,%1$s+(random()*500)::bigint,'[)')
                       ,'00000000-0000-0000-0000-000000000001'
                       ,random()>.2)
            $q$,(random()*1e4)::bigint);
    exception when others then null;
    end;
  end loop;
end$f$;

select clock_timestamp()-started from timing;
select count(*) from excllock;

DROP TABLE

CREATE TABLE

setseed

SELECT 1

DROP TABLE

SELECT 1

DO

?column?

00:00:15.205212

SELECT 1

count

33123

SELECT 1

pg_total_relation_size
pg_size_pretty

13320192
13 MB

SELECT 1