mutually dependent object cycle, detection w/ m2o/o2m without the backref

[Vsevolod Fedorov]

I have two tables referencing each other mutually, each with a foreign key referencing the other. Here is the test, which raise IntegrityError when deleting instances. Note that it fails only when using postresql backend, and works fine with sqlite.

#!/usr/bin/env python

from sqlalchemy import *
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base


metadata = MetaData()
Base = declarative_base(metadata=metadata)


class Key(Base):

    __tablename__ = 'keys'
    __table_args__ = (
        ForeignKeyConstraint(['op_cert_id']('op_cert_id'), ['certificates.id']('certificates.id'), use_alter=True, name='fk_key_op_cert_id'),
        {},
    )

    id = Column(Integer, primary_key=True)
    op_cert_id = Column(Integer)
    op_cert = relationship('Certificate', primaryjoin='Key.op_cert_id==Certificate.id')


class Certificate(Base):

    __tablename__ = 'certificates'

    id = Column(Integer, primary_key=True)
    key_id = Column(Integer, ForeignKey('keys.id'))
    key = relationship('Key', primaryjoin='Certificate.key_id==Key.id')


# fails with postgresql engine:
engine = create_engine('postgresql://my-user@localhost/my-db')  # insert your db/user
# but works fine with sqlite engine:
#engine = create_engine('sqlite:///:memory:', echo=False)
Session = sessionmaker(bind=engine)
metadata.create_all(engine)
db = Session()

def test():
    key1 = Key()
    cert1 = Certificate(key=key1)
    key2 = Key(op_cert=cert1)
    cert2 = Certificate(key=key2)
    db.add(cert1)
    db.add(cert2)
    db.commit()
    db.delete(key1)
    db.delete(key2)
    db.delete(cert1)
    db.delete(cert2)
    db.commit()

# does not always fail, so wrapped to cycle
while True:
    print '.',
    test()

It does not //always// fail, so I inserted a cycle to fail for sure. I tried sqlalchemy versions: 0.7.10 and 0.8.0, with the same results. The output is:

Traceback (most recent call last):
  File "./circular.py", line 60, in <module>
    test()
  File "./circular.py", line 55, in test
    db.commit()
  File "/home/seva/src/sqlalchemy-default/lib/sqlalchemy/orm/session.py", line 719, in commit
    self.transaction.commit()
  File "/home/seva/src/sqlalchemy-default/lib/sqlalchemy/orm/session.py", line 352, in commit
    self._prepare_impl()
  File "/home/seva/src/sqlalchemy-default/lib/sqlalchemy/orm/session.py", line 332, in _prepare_impl
    self.session.flush()
  File "/home/seva/src/sqlalchemy-default/lib/sqlalchemy/orm/session.py", line 1814, in flush
    self._flush(objects)
  File "/home/seva/src/sqlalchemy-default/lib/sqlalchemy/orm/session.py", line 1896, in _flush
    flush_context.execute()
  File "/home/seva/src/sqlalchemy-default/lib/sqlalchemy/orm/unitofwork.py", line 367, in execute
    n.execute_aggregate(self, set_)
  File "/home/seva/src/sqlalchemy-default/lib/sqlalchemy/orm/unitofwork.py", line 642, in execute_aggregate
    uow)
  File "/home/seva/src/sqlalchemy-default/lib/sqlalchemy/orm/persistence.py", line 115, in delete_obj
    cached_connections, mapper, table, delete)
  File "/home/seva/src/sqlalchemy-default/lib/sqlalchemy/orm/persistence.py", line 677, in _emit_delete_statements
    connection.execute(statement, del_objects)
  File "/home/seva/src/sqlalchemy-default/lib/sqlalchemy/engine/base.py", line 664, in execute
    params)
  File "/home/seva/src/sqlalchemy-default/lib/sqlalchemy/engine/base.py", line 764, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/home/seva/src/sqlalchemy-default/lib/sqlalchemy/engine/base.py", line 878, in _execute_context
    context)
  File "/home/seva/src/sqlalchemy-default/lib/sqlalchemy/engine/base.py", line 871, in _execute_context
    context)
  File "/home/seva/src/sqlalchemy-default/lib/sqlalchemy/engine/default.py", line 320, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (IntegrityError) update or delete on table "keys" violates foreign key constraint "certificates_key_id_fkey" on table "certificates"
DETAIL:  Key (id)=(21) is still referenced from table "certificates".
 'DELETE FROM keys WHERE keys.id = %(id)s' {'id': 21}

[Vsevolod Fedorov]

test file

[Mike Bayer]

this is very close (but yes, slightly different in this specific test) to the "mutually dependent foreign key" pattern, described at http://docs.sqlalchemy.org/en/rel_0_8/orm/relationships.html#rows-that-point-to-themselves-mutually-dependent-rows .

In that pattern, post_update=True is definitely needed, as the two rows are mutually dependent so there is necessarily an UPDATE statement to delink the two rows, before one is deleted.

In this specific test, there is not actually a cycle between the four rows in question. If they are deleted in order, the test can proceed every time:

        db.delete(cert2)
        db.flush()
        db.delete(key2)
        db.flush()
        db.delete(cert1)
        db.flush()
        db.delete(key1)
        db.flush()

But we don't want to do that, OK. The next thing you can do, is add backreferences, which helps the unit of work to decide how it should be sorting these things, which resolves this case:

class Key(Base):
    # ...

    op_cert = relationship('Certificate', primaryjoin='Key.op_cert_id==Certificate.id',
                    backref="q")


class Certificate(Base):
    # ...
    key = relationship('Key', primaryjoin='Certificate.key_id==Key.id',
                    backref="q")

assuming your data doesn't actually have mutually dependent cycles, that's probably what you should do for now; those "q" backrefs (the name is arbitrary) don't have any other impact and give the UOW the clue it needs.

There's probably a fixable bug here which is that the current direction without the backref should figure out the same dependency, so will leave this open.

The other workaround is to just do the traditional "mutually dependent rows" workaround, which is post_update, which just delivers a separate UPDATE step before the delete:

class Key(Base):
    # ...

    op_cert = relationship('Certificate', primaryjoin='Key.op_cert_id==Certificate.id',
                    post_update=True)

[Vsevolod Fedorov]

backref worked in test sample, but not in real app. But post_update completely solved the problem. Thanks!

[Mike Bayer]

I think im going to close this because a mutually dependent FK situation will need post_update in the vast majority of cases anyway, and to try to get it to work around the case when there aren't actually any insert/delete cycles is quite difficult - it would be code that almost never gets used since post_update would normally be on anyway.

[Mike Bayer]

Removing milestone: 0.8.xx (automated comment)