TLDR for the article: author uses views and rewrite rules to implement soft deletion without altering the application code.
This was pretty interesting. Triggers have significant overhead, although for deletes I wouldn’t expect that to matter much (simply because you’re normally operating on a small number of rows).
For a complete solution (nb @vosper), you would also want partial indexes of the deleted and undeleted rows; and you’d want to clear the tombstones out of the way, either by periodically hard deleting (maybe to another table) or by clustering the table with an index with the deleted column as the first key.
Tbh I don’t have any intuition as to why this is better than a trigger that inserts a copy of the row to another table, which avoids the need for the indexes and tombstone management I mention in the previous paragraph.
I’m likely going to have to implement soft-delete quite soon, so I’d like to hear people’s thoughts on the approach in this post? I find it quite attractive to push the logic down to the database and allow the application code to think it’s performing real deletes, rather than having to make everything in the app layer aware of soft deletes.
I’m a little torn on this. The wiki says not to use them because
Rules are incredibly powerful, but they don’t do what they look like they do. They look like they’re some conditional logic, but they actually rewrite a query to modify it or add additional queries to it.
But in this case it’s quite clear that rewriting the query is the purpose of using the rule. And the blog linked from the wiki seems kinda like one persons opinion, and people in the comments don’t necessarily agree with them.
If only there was a way to do this reliably on the RDMS without breaking acid principles and not tied to a framework or library of some client language and without resourcing to hacky tricks…
Just implement soft delete manually by creating the appropriate data model for it and put the logic in a stored procedure. You can even use the same logic for multiple tables, just by following simple naming conventions.
If performance and data size allows for it, you can even implement a delete_row(table, id) procedure that saves the serialized rows on a single deleted table.
If performance and data size allows for it, you can even implement a delete_row(table, id) procedure that saves the serialized rows on a single deleted table.
So rather than marking rows as deleted and then filtering them out in future queries, I’d create a new table that stored a serialized copy of the original row, and then do a real delete on the target table? Then if I want to undelete I need to restore from the deletion table? It seems like this would run into a problem with cascading deletes? Or would I want a before-delete trigger on every table, so that as the delete cascades the affected rows are copied first?
I didn’t specify any specific strategy. My point was that you can write callable stored procedure and implement whatever logic you want. This has been done for decades. While articles like this make it sound like soft deletes are possible because of a bunch of tricks with rules or triggers.
Other than that, I don’t understand understand what specific problem with cascade deletes arise from doing it from within a procedure versus doing it with tricks like the one in this post. If you want to undo a cascaded deletion, you have to know the chain and do it in the right order regardless of what method you use.
If performance and data size allows for it, you can even implement a delete_row(table, id) procedure that saves the serialized rows on a single deleted table.
I thought that this delete_row procedure was a specific strategy, so I was trying to ask about that. No worries, though, I started with that idea and I think I have a simple solution that will work for me. Thanks for the pointer!
Other than that, I don’t understand understand what specific problem with cascade deletes arise from doing it from within a procedure versus doing it with tricks like the one in this post. If you want to undo a cascaded deletion, you have to know the chain and do it in the right order regardless of what method you use.
Thanks, I understand this better now. I’m leaving “how to undelete” as a problem to solve separately from “how to soft delete”, and just making sure I am storing enough data about the deletions that it will be possible to do, even if I don’t have code for the process today.
TLDR for the article: author uses views and rewrite rules to implement soft deletion without altering the application code.
This was pretty interesting. Triggers have significant overhead, although for deletes I wouldn’t expect that to matter much (simply because you’re normally operating on a small number of rows).
For a complete solution (nb @vosper), you would also want partial indexes of the deleted and undeleted rows; and you’d want to clear the tombstones out of the way, either by periodically hard deleting (maybe to another table) or by clustering the table with an index with the deleted column as the first key.
Tbh I don’t have any intuition as to why this is better than a trigger that inserts a copy of the row to another table, which avoids the need for the indexes and tombstone management I mention in the previous paragraph.
I’m likely going to have to implement soft-delete quite soon, so I’d like to hear people’s thoughts on the approach in this post? I find it quite attractive to push the logic down to the database and allow the application code to think it’s performing real deletes, rather than having to make everything in the app layer aware of soft deletes.
https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_rules
I’m a little torn on this. The wiki says not to use them because
But in this case it’s quite clear that rewriting the query is the purpose of using the rule. And the blog linked from the wiki seems kinda like one persons opinion, and people in the comments don’t necessarily agree with them.
If only there was a way to do this reliably on the RDMS without breaking acid principles and not tied to a framework or library of some client language and without resourcing to hacky tricks…
Just implement soft delete manually by creating the appropriate data model for it and put the logic in a stored procedure. You can even use the same logic for multiple tables, just by following simple naming conventions.
If performance and data size allows for it, you can even implement a delete_row(table, id) procedure that saves the serialized rows on a single deleted table.
So rather than marking rows as deleted and then filtering them out in future queries, I’d create a new table that stored a serialized copy of the original row, and then do a real delete on the target table? Then if I want to undelete I need to restore from the deletion table? It seems like this would run into a problem with cascading deletes? Or would I want a before-delete trigger on every table, so that as the delete cascades the affected rows are copied first?
I didn’t specify any specific strategy. My point was that you can write callable stored procedure and implement whatever logic you want. This has been done for decades. While articles like this make it sound like soft deletes are possible because of a bunch of tricks with rules or triggers.
Other than that, I don’t understand understand what specific problem with cascade deletes arise from doing it from within a procedure versus doing it with tricks like the one in this post. If you want to undo a cascaded deletion, you have to know the chain and do it in the right order regardless of what method you use.
I thought that this
delete_rowprocedure was a specific strategy, so I was trying to ask about that. No worries, though, I started with that idea and I think I have a simple solution that will work for me. Thanks for the pointer!Thanks, I understand this better now. I’m leaving “how to undelete” as a problem to solve separately from “how to soft delete”, and just making sure I am storing enough data about the deletions that it will be possible to do, even if I don’t have code for the process today.