Postgres foreign key on delete cascade deletes all but 1, why?

Question

I am using postgres v and I am trying to get a multi table cascading delete to work.

However, when I do it, 1 table/entry doesn't get removed and I can't for the life of me understand why.

Here is my DDL (as extracted, so forgive the formatting).

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.webhooks
(
    id          serial not null constraint webhooks_pkey primary key,
    team_id     text,
    salt        text   not null constraint hashed_pass unique,
    hashed_pass text   not null
) 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.states
(
    id   uuid not null constraint states_pkey primary key,
    data json not null
) 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
) inherits (public.common);

insert data:

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');

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');

now verify; All data is joined properly

slack.public> select inst.id as installation_id, jpd.id as platform_id, tjp.jpd_id as joined, u.id as user from
                  installations inst,
                  team_jfrog_platforms as tjp,
                  jfrog_platforms as jpd,
                  users u
                      where inst.id = 'T01QL4TQW2E'
                        and inst.id = tjp.team_id
                        and  jpd.id = tjp.jpd_id
                        and u.team_id = tjp.team_id
[2021-06-04 15:27:59] 1 row retrieved starting from 1 in 28 ms (execution: 5 ms, fetching: 23 ms)

now delete the installation:
```sql
delete from installations where id = 'T01QL4TQW2E';

slack.public> delete from installations where id = 'T01QL4TQW2E'
[2021-06-04 15:41:59] 1 row affected in 4 ms

verify the delete:

slack.public> select * from installations where id = 'T01QL4TQW2E'
[2021-06-04 15:43:00] 0 rows retrieved in 32 ms (execution: 4 ms, fetching: 28 ms)

slack.public> select * from team_platforms
[2021-06-04 15:43:46] 0 rows retrieved in 34 ms (execution: 4 ms, fetching: 30 ms)

slack.public> select * from users
[2021-06-04 15:44:07] 0 rows retrieved in 34 ms (execution: 5 ms, fetching: 29 ms)

#but the platform is still there:
slack.public> select * from platforms
[2021-06-04 15:44:37] 1 row retrieved starting from 1 in 30 ms (execution: 3 ms, fetching: 27 ms)

So, the cascading delete removes everything except the entry in the platforms table? Why? What am I missing?

My question: I want it so that a delete from any of these tables removes all records from their related joining tables. How do I do that?

Answer 1

Cascading only works in one direction, from the referenced item to the records that depend on it.

• So team_platforms will be deleted if their installations or platforms are removed.
• Similarly users will be deleted if their installations are removed.
• Your example does not have an on delete cascade clause for the users reference to platforms, so deleting the platforms record results in an error.
• platforms does not depend on anything so it does not get removed automatically in any circumstance.

If redefining your foreign keys gets what you need, great. If not, you can define triggers to delete the non-dependent data, though I would be careful that ensuing cascades do not delete large swathes of data you did not expect. I've included a dbfiddle with triggers based on your question as a proof-of-concept.