I’m trying to clean up/delete some data on an interval (once a month) but want the option to rollback, those specific records only, at a later date. I would need any new records inserted and all other changes after the deletion to remain intact.
My original thought was to just generate the sql statements myself via a bunch of insert operations with the related columns/values and saving that somewhere. My second thought is that Repo.transaction already knows how to rollback exactly what changed, is there a way to save the statement it generates to initiate a rollback later on?
Any other advice for approaching this issue? I’ve considered working with database backups or managing a replica, it just seems less ideal but still open to feedback.
It might be more convenient to implement soft-deletes instead.
1 Like
I’ve initially ruled this out as because there are many queries/views that access this data that would need to be updated and would be a much larger effort with many more changes and validations needed. It unfortunately spans many tables and associations.
Then a slightly different implementation of soft-delete:
Before your clean up task deletes records, store the to-be-deleted records as json in another table. You can then get them back whenever you want.
1 Like
That still involves generating statements/queries to reinsert the data and associations manually, which I was hoping to avoid if possible. I have considered saving it as json, which is probably better than saving an sql string since we can utilize ecto to do so.
That isn’t how transaction rollbacks work. What you’re picturing is that for each logical operation, it knows the inverse operation, which it then performs on rollback. This isn’t what happens at all.
When a transaction is in progress it is making rows that are flagged in a certain way, making them invisible to queries outside the transaction. These changes are essentially “discarded” during a rollback, it isn’t like they are reversed.
More to the point, the internal mechanics of a rollback are invisible to Ecto or any other Postgres client. It won’t help you at all after a transaction has committed.
5 Likes
Yeah that makes sense, thanks for clarifying. I was diving into reverse engineering Ecto.Multi._apply_ and I see what you mean. There’s just a reference to that change that gets undone, it doesn’t generate a delete operation to rollback an insert (if I’m understanding that correctly).
Correct. Transaction commit and rollback are database level operations that Ecto merely uses, not something that Ecto itself implements.
2 Likes
