Currently I'm working on a better staging setup for Birdfeeder. A big part of that is syncing the production database to the staging database while cleaning out user specific information. An easy way to do that is to use SQL to truncate the users table.

When I tried to truncate my users table I noticed that my foreign keys don't have an ON DELETE action set. In that case deleting a row that is referenced from another row would fail instead of cascading the delete action further up the tree.

I was wondering what's the best way to see the dependencies between entities in Postgres and found pg_depend which does exactly that.

Query

with recursive chain as (
    select classid, objid, objsubid, conrelid
    from pg_depend d
    join pg_constraint c on c.oid = objid
    where refobjid = 'users'::regclass and deptype = 'n'
union all
    select d.classid, d.objid, d.objsubid, c.conrelid
    from pg_depend d
    join pg_constraint c on c.oid = objid
    join chain on d.refobjid = chain.conrelid and d.deptype = 'n'
    )
select pg_describe_object(classid, objid, objsubid), pg_get_constraintdef(objid)
from chain;

Source: Thanks to klin on StackOverflow for that neat snippet.

Output

Example from Birdfeeder

The bookmark mapping table maps a tweet to a user. If I would delete the user from the users table that is referenced in the bookmarks table it would fail.

CREATE TABLE birdfeederdb.bookmarks (
    id BIGSERIAL PRIMARY KEY,
    tweet_id bigint NOT NULL REFERENCES birdfeederdb.tweets(id),
    user_id bigint NOT NULL REFERENCES birdfeederdb.users(id),
);

We have to add a migration to tell the database that it can also delete the row if the row it's referencing got deleted ("If someone deletes the user, delete the bookmarks of that user").

After running the migration the schema will look like this:

CREATE TABLE birdfeederdb.bookmarks (
    id BIGSERIAL PRIMARY KEY,
    tweet_id bigint NOT NULL REFERENCES birdfeederdb.tweets(id),
    user_id bigint NOT NULL REFERENCES birdfeederdb.users(id) ON DELETE CASCADE,);

If you use Rails the migration is as simple as that:

class AddCascadeToBookmarks < ActiveRecord::Migration[7.0]
  def change
    remove_foreign_key "bookmarks", "users"
    add_foreign_key "bookmarks", "users", on_delete: :cascade
  end
end

Now you can just delete a user from the users table and the database will take care of cleaning up the entities that reference that user.