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 company (
  name text not null,
  primary key (name)
);

create table staff (
  name text not null,
  company text not null references company (name),
  primary key (name)
);

create table roles (
  id int NOT NULL,
  role text not null,
  staffid text not null references staff (name),
  primary key (id)
);

CREATE FUNCTION roles_updates() RETURNS trigger AS $$
BEGIN

  IF TG_OP = 'DELETE' THEN
    IF (select count(1) from staff where name = OLD.staffid) < 2 THEN
        RAISE EXCEPTION 'not allowed';
    END IF;
  END IF;

  RETURN OLD;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER roles_updates
BEFORE UPDATE OR DELETE ON roles
FOR EACH ROW
EXECUTE FUNCTION roles_updates();

insert into company values ('amd');
insert into company values ('intel');
insert into staff values ('lisa', 'amd');
insert into roles values (1, 'ceo', 'lisa');
1 rows affected

1 rows affected

1 rows affected

1 rows affected

-- should be ok: delete whole graph
begin;
delete from roles where id = 1;
delete from staff where name = 'lisa';
delete from company where name = 'amd';
rollback;
ERROR:  not allowed
CONTEXT:  PL/pgSQL function roles_updates() line 6 at RAISE


-- should be ok: another staff member took the ownership
begin;
insert into staff values ('larissa', 'amd');
insert into roles values (2, 'ceo', 'larissa');
delete from roles where id = 1;
rollback;
ERROR:  current transaction is aborted, commands ignored until end of transaction block


-- should fail: company without ceo isn't allowed
begin;
delete from roles where id = 1;
rollback;
ERROR:  current transaction is aborted, commands ignored until end of transaction block


-- should fail: company without ceo isn't allowed
begin;
update roles set role = 'cto' where id = 1;
rollback;
ERROR:  current transaction is aborted, commands ignored until end of transaction block


-- should fail: company without ceo isn't allowed
begin;
update staff set company = 'intel' where name = 'lisa';
rollback;
ERROR:  current transaction is aborted, commands ignored until end of transaction block


-- should fail: company without staff isn't allowed
begin;
delete from roles where id = 1;
delete from staff where name = 'lisa';
rollback;
ERROR:  current transaction is aborted, commands ignored until end of transaction block