Postgres 13

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?.

CREATE TABLE tbl (
  gid          int      NOT NULL DEFAULT 0
, realm        text     NOT NULL DEFAULT ''
, grant_update smallint NOT NULL DEFAULT 0
, CONSTRAINT block_anonymous_page_edit
  CHECK (gid <> 1 OR realm <> 'nodeaccess_rid' OR grant_update = 0)
);

CREATE TABLE


-- These work:
INSERT INTO tbl(gid, realm, grant_update)
VALUES (1, 'nodeaccess_rid', 0);

INSERT INTO tbl(gid, realm, grant_update)
VALUES (1, 'some_string',    1);

INSERT INTO tbl(gid, realm, grant_update)
VALUES (2, 'nodeaccess_rid', 1);

INSERT 0 1



INSERT 0 1



INSERT 0 1


-- Check violation!
INSERT INTO tbl(gid, realm, grant_update)
VALUES (1, 'nodeaccess_rid', 1);

ERROR:  new row for relation "tbl" violates check constraint "block_anonymous_page_edit"
DETAIL:  Failing row contains (1, nodeaccess_rid, 1).