Check Postgres table dependencies for cascadePublished on July 13, 2022
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
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.
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.
|constraint fk_rails_c1ff6fa4ac on table bookmarks||FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE|
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.