4 ms·

Setting up row level security policies to exclude rows `where deleted_at is not null` solves most of the issues with the discarded solution. Of course it would

Setting up row level security policies to exclude rows `where deleted_at is not null` solves most of the issues with the discarded solution. Of course it would be crazy to have a system where you will need any extra where clauses for the default queries being made. You can even make a simple function that sets a statement level variable checked in the rls policy so that deleted rows can be included or only deleted rows be returned. The only negative thing I see about using a deleted_at column is that cancelling the delete in the before delete trigger changes the resulting "deleleted rows" count to be 0 instead of the expected number.

bastawhiz 4y ago
Excluding the rows still doesn't solve problems with foreign keys (you can't DELETE CASCADE and instead have to iterate all relationships manually). It also means you still need to remember to consider deleted_at when doing things like setting up unique indexes.
- paulryanrogers 4y ago
  > It also means you still need to remember to consider deleted_at when doing things like setting up unique indexes. Considering soft deleted for uniqueness can be a feature, especially if one has a restore feature. Though I agree it can be easily overlooked if you want to exclude them, such as forgetting to use COALESCE with some reserved value. (Otherwise null will make the whole constraint always unique.)
  - bastawhiz 4y ago
    In my experience, you only want machine-generated values to be reserved sheet soft delete. Operationally, too many users want to delete stuff so that they can free up a user-generated value. In my service, users frequently delete their sites so that they can replace the URL/subdomain with another that they want to replace it with. Especially when you consider foreign keys, restoration of deleted data is essentially never just setting the deleted field to null. You need a more robust system to fill in missing details or rectifying the deleted data with new data.

bastawhiz 4y ago

Excluding the rows still doesn't solve problems with foreign keys (you can't DELETE CASCADE and instead have to iterate all relationships manually). It also means you still need to remember to consider deleted_at when doing things like setting up unique indexes.

paulryanrogers 4y ago
> It also means you still need to remember to consider deleted_at when doing things like setting up unique indexes. Considering soft deleted for uniqueness can be a feature, especially if one has a restore feature. Though I agree it can be easily overlooked if you want to exclude them, such as forgetting to use COALESCE with some reserved value. (Otherwise null will make the whole constraint always unique.)
- bastawhiz 4y ago
  In my experience, you only want machine-generated values to be reserved sheet soft delete. Operationally, too many users want to delete stuff so that they can free up a user-generated value. In my service, users frequently delete their sites so that they can replace the URL/subdomain with another that they want to replace it with. Especially when you consider foreign keys, restoration of deleted data is essentially never just setting the deleted field to null. You need a more robust system to fill in missing details or rectifying the deleted data with new data.