Execute some code when an SQLAlchemy object's deletion is actually committed

Question

I have a SQLAlchemy model that represents a file and thus contains the path to an actual file. Since deletion of the database row and file should go along (so no orphaned files are left and no rows point to deleted files) I added a delete() method to my model class:

def delete(self):
    if os.path.exists(self.path):
        os.remove(self.path)
    db.session.delete(self)

This works fine but has one huge disadvantage: The file is deleted immediately before the transaction containing the database deletion is committed.

One option would be committing in the delete() method - but I don't want to do this since I might not be finished with the current transaction. So I'm looking for a way to delay the deletion of the physical file until the transaction deleting the row is actually committed.

SQLAlchemy has an after_delete event but according to the docs this is triggered when the SQL is emitted (i.e. on flush) which is too early. It also has an after_commit event but at this point everything deleted in the transaction has probably been deleted from SA.

Answer 1

When using SQLAlchemy in a Flask app with Flask-SQLAlchemy it provides a models_committed signal which receives a list of (model, operation) tuples. Using this signal doing what I'm looking for is extremely easy:

@models_committed.connect_via(app)
def on_models_committed(sender, changes):
    for obj, change in changes:
        if change == 'delete' and hasattr(obj, '__commit_delete__'):
            obj.__commit_delete__()

With this generic function every model that needs on-delete-commit code now simply needs to have a method __commit_delete__(self) and do whatever it needs to do in that method.

---

It can also be done without Flask-SQLAlchemy, however, in this case it needs some more code:

• A deletion needs to be recorded when it's performed. This is be done using the after_delete event.
• Any recorded deletions need to be handled when a COMMIT is successful. This is done using the after_commit event.
• In case the transaction fails or is manually rolled back the recorded changes also need to be cleared. This is done using the after_rollback() event.

Answer 2

This follows along with the other event-based answers, but I thought I'd post this code, since I wrote it to solve pretty much your exact problem:

The code (below) registers a SessionExtension class that accumulates all new, changed, and deleted objects as flushes occur, then clears or evaluates the queue when the session is actually committed or rolled back. For the classes which have an external file attached, I then implemented obj.after_db_new(session), obj.after_db_update(session), and/or obj.after_db_delete(session) methods which the SessionExtension invokes as appropriate; you can then populate those methods to take care of creating / saving / deleting the external files.

Note: I'm almost positive this could be rewritten in a cleaner manner using SqlAlchemy's new event system, and it has a few other flaws, but it's in production and working, so I haven't updated it :)

import logging; log = logging.getLogger(__name__)
from sqlalchemy.orm.session import SessionExtension

class TrackerExtension(SessionExtension):

    def __init__(self):
        self.new = set()
        self.deleted = set()
        self.dirty = set()

    def after_flush(self, session, flush_context):
        # NOTE: requires >= SA 0.5
        self.new.update(obj for obj in session.new 
                        if hasattr(obj, "after_db_new"))
        self.deleted.update(obj for obj in session.deleted 
                            if hasattr(obj, "after_db_delete"))
        self.dirty.update(obj for obj in session.dirty 
                          if hasattr(obj, "after_db_update"))

    def after_commit(self, session):
        # NOTE: this is rather hackneyed, in that it hides errors until
        #       the end, just so it can commit as many objects as possible.
        # FIXME: could integrate this w/ twophase to make everything safer in case the methods fail.
        log.debug("after commit: new=%r deleted=%r dirty=%r", 
                  self.new, self.deleted, self.dirty)
        ecount = 0

        if self.new:
            for obj in self.new:
                try:
                    obj.after_db_new(session)
                except:
                    ecount += 1
                    log.critical("error occurred in after_db_new: obj=%r", 
                                 obj, exc_info=True)
            self.new.clear()

        if self.deleted:
            for obj in self.deleted:
                try:
                    obj.after_db_delete(session)
                except:
                    ecount += 1
                    log.critical("error occurred in after_db_delete: obj=%r", 
                                 obj, exc_info=True)
            self.deleted.clear()

        if self.dirty:
            for obj in self.dirty:
                try:
                    obj.after_db_update(session)
                except:
                    ecount += 1
                    log.critical("error occurred in after_db_update: obj=%r", 
                                 obj, exc_info=True)
            self.dirty.clear()

        if ecount:
            raise RuntimeError("%r object error during after_commit() ... "
                               "see traceback for more" % ecount)

    def after_rollback(self, session):
        self.new.clear()
        self.deleted.clear()
        self.dirty.clear()

# then add "extension=TrackerExtension()" to the Session constructor 

Answer 3

this seems to be a bit challenging, Im curious if a sql trigger AFTER DELETE might be the best route for this, granted it won't be dry and Im not sure the sql database you are using supports it, still AFAIK sqlalchemy pushes transactions to the db but it really doesn't know when they have being committed, if Im interpreting this comment correctly:

its the database server itself that maintains all "pending" data in an ongoing transaction. The changes aren't persisted permanently to disk, and revealed publically to other transactions, until the database receives a COMMIT command which is what Session.commit() sends.

taken from SQLAlchemy: What's the difference between flush() and commit()? by the creator of sqlalchemy ...

Answer 4

If your SQLAlchemy backend supports it, enable two-phase commit. You will need to use (or write) a transaction model for the filesystem that:

• checks permissions, etc. to ensure that the file exists and can be deleted during the first commit phase
• actually deletes the file during the second commit phase.

That's probably as good as it's going to get. Unix filesystems, as far as I know, do not natively support XA or other two-phase transactional systems, so you will have to live with the small exposure from having a second-phase filesystem delete fail unexpectedly.