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?.
select version();
create table public.common
(
    created_at timestamp with time zone default CURRENT_TIMESTAMP,
    updated_at timestamp with time zone default CURRENT_TIMESTAMP
);


create table public.installations
(
    id  text not null constraint installations_pkey primary key,
    client_key text,
    data       jsonb
) inherits (public.common);


create table public.platforms
(
    id      text not null constraint platforms_pkey primary key,
    jpd_url text,
    jpd_key text
) inherits (public.common);

create table public.team_platforms
(
    team_id text not null constraint jpd_id primary key
        constraint team_platforms_team_id_foreign 
            references public.installations
            on delete cascade,
    jpd_id  text not null
        constraint team_platforms_jpd_id_foreign
            references public.platforms
            on delete cascade
) inherits (public.common);

create table public.users
(
    id  text not null constraint users_pkey primary key,
    team_id  text not null
        constraint users_team_id_foreign
            references public.installations
            on delete cascade,
    onboarded_date   timestamp with time zone,
    account_bind_key text,
    jpd_username     text,
    jpd_id           text
        constraint users_jpd_id_foreign
            references public.platforms
            on delete cascade  -- added this
) inherits (public.common);

create table public.items  -- added another level of cascading
(
    id text not null constraint items_pkey primary key,
    user_id text not null
        constraint items_user_id_foreign
            references public.users
            on delete cascade,
    data jsonb
) inherits (public.common);

-- An example of how to "cascade" backwards.
-- Be sure your business logic really wants this before implementing.

create function cleanup_team_platforms()
    returns trigger AS $$
        BEGIN
            DELETE FROM public.installations WHERE id=OLD.team_id;
            DELETE FROM public.platforms WHERE id=OLD.jpd_id;
            RETURN OLD;
        END;
    $$ LANGUAGE plpgsql;

create trigger trigger_team_platforms
    after delete on public.team_platforms
    for each row
    execute function cleanup_team_platforms();

create function cleanup_users()
    returns trigger AS $$
        BEGIN
            DELETE FROM public.installations WHERE id=OLD.team_id;
            DELETE FROM public.platforms WHERE id=OLD.jpd_id;
            RETURN OLD;
        END;
    $$ LANGUAGE plpgsql;

create trigger trigger_users
    after delete on public.users
    for each row
    execute function cleanup_users();

create function cleanup_items()
    returns trigger AS $$
        BEGIN
            DELETE FROM public.users WHERE id=OLD.user_id;
            RETURN OLD;
        END;
    $$ LANGUAGE plpgsql;

create trigger trigger_items
    after delete on public.items
    for each row
    execute function cleanup_items();



version


PostgreSQL 13.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit



SELECT 1



CREATE TABLE



CREATE TABLE



CREATE TABLE



CREATE TABLE



CREATE TABLE



CREATE TABLE



CREATE FUNCTION



CREATE TRIGGER



CREATE FUNCTION



CREATE TRIGGER



CREATE FUNCTION



CREATE TRIGGER


insert into public.installations (created_at, updated_at, id, client_key, data) 
values ('2021-06-04 22:22:45.422746', '2021-06-04 22:22:45.422746', 'T01QL4TQW2E', 'U45tKSFX_oBRyhKJ3lk19ouLqHU9Cg1LEwNrdHpI', to_jsonb('missing'::text));

insert into public.platforms (created_at, updated_at, id, jpd_url, jpd_key) 
values ('2021-06-04 22:23:34.230229', '2021-06-04 22:23:34.230229', '4945387a-6881-4223-9770-680d53fa865d', 'https://myinstance.com', 'foo');

insert into public.team_platforms (created_at, updated_at, team_id, jpd_id) 
values ('2021-06-04 22:23:34.234910', '2021-06-04 22:23:34.234910', 'T01QL4TQW2E', '4945387a-6881-4223-9770-680d53fa865d');

insert into public.users (created_at, updated_at, id, team_id, onboarded_date, account_bind_key, jpd_username, jpd_id) 
values ('2021-06-04 22:22:59.526669', '2021-06-04 22:22:59.526669', 'U01R4LUSM8B', 'T01QL4TQW2E', '2021-06-04 22:22:59.513000', 'nsdoosncinsodciosdincos', null, '4945387a-6881-4223-9770-680d53fa865d');

insert into public.items (created_at, updated_at, id, user_id, data)
values ('2021-06-04 22:24:01.234567', '2021-06-04 22:24:01.234567', 'I200', 'U01R4LUSM8B', to_jsonb('example'::text))

INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


select inst.id as installation_id, jpd.id as platform_id,
       tjp.jpd_id as joined, u.id as user, i.id as item
  from installations inst,
       team_platforms as tjp,
       platforms as jpd,
       users u,
       items i
 where     inst.id = 'T01QL4TQW2E'
       and inst.id = tjp.team_id
       and jpd.id = tjp.jpd_id
       and u.team_id = tjp.team_id
       and i.user_id = u.id;


installation_id
platform_id
joined
user
item


T01QL4TQW2E
4945387a-6881-4223-9770-680d53fa865d
4945387a-6881-4223-9770-680d53fa865d
U01R4LUSM8B
I200



SELECT 1


delete from installations where id = 'T01QL4TQW2E';
-- delete from items where id ='I200';

DELETE 1


select * from installations;
select * from team_platforms;
select * from users;
select * from items;
select * from platforms;


created_at
updated_at
id
client_key
data



SELECT 0




created_at
updated_at
team_id
jpd_id



SELECT 0




created_at
updated_at
id
team_id
onboarded_date
account_bind_key
jpd_username
jpd_id



SELECT 0




created_at
updated_at
id
user_id
data



SELECT 0




created_at
updated_at
id
jpd_url
jpd_key



SELECT 0