Postgres 10

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 master (
  id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY, 
  PRIMARY KEY (id)
);

CREATE TABLE detail (
  id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY, 
  master_id INT, 
  PRIMARY KEY (id, master_id)
);

ALTER TABLE detail 
ADD CONSTRAINT detail_master_id_fkey
FOREIGN KEY (master_id)
REFERENCES master (id);

CREATE FUNCTION assert_master_has_detail() RETURNS trigger
AS
$$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM detail WHERE master_id = NEW.id)
  THEN
    RAISE EXCEPTION 'no detail for master_id=%', NEW.id;
  ELSE
    RETURN NEW;
  END IF;
END;
$$
LANGUAGE plpgsql;

CREATE FUNCTION assert_no_leftover_master() RETURNS trigger
AS
$$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM detail WHERE master_id = OLD.master_id)
     AND EXISTS (SELECT 1 FROM master WHERE id = OLD.master_id)
  THEN
    RAISE EXCEPTION 'last detail for master_id=% removed, but master still exists', OLD.master_id;
  ELSE
    RETURN NULL;
  END IF;
END;
$$
LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER trigger_assert_master_has_detail 
  AFTER INSERT OR UPDATE OF id ON master 
  DEFERRABLE INITIALLY DEFERRED FOR EACH ROW 
  EXECUTE PROCEDURE assert_master_has_detail();
  
CREATE CONSTRAINT TRIGGER trigger_assert_no_leftover_master
  AFTER DELETE OR UPDATE OF master_id ON detail
  DEFERRABLE INITIALLY DEFERRED FOR EACH ROW
  EXECUTE PROCEDURE assert_no_leftover_master();
INSERT INTO master
VALUES (1);
ERROR:  no detail for master_id=1
CONTEXT:  PL/pgSQL function assert_master_has_detail() line 5 at RAISE


INSERT INTO master
VALUES (1);

INSERT INTO detail
VALUES (10, 1);
1 rows affected

1 rows affected

DELETE FROM detail
WHERE master_id = 1;
ERROR:  last detail for master_id=1 removed, but master still exists
CONTEXT:  PL/pgSQL function assert_no_leftover_master() line 6 at RAISE


DELETE FROM detail
WHERE master_id = 1;

DELETE FROM master
WHERE id = 1;
1 rows affected

1 rows affected