Two deletions cascading to the same object can result in an error

If an object A is related to two other objects B and C, both of which have cascade=delete rules, then independently deleting both B and C results in an InvalidRequestError, apparently because sqlalchemy.orm attempts to delete A twice.

The call to session.flush() in between the deletion of B and C seems to be critical to reproducing this bug.

#!/usr/bin/python

from sqlalchemy import *

engine = create_engine('sqlite://')
metadata = BoundMetaData(engine)
session = create_session()

_dose_time = \
    Table('dose_time', metadata,
          Column('dose_time_id', Integer, primary_key=True),
          Column('timepoint', Integer, nullable=False))

_animal = \
    Table('animal', metadata,
          Column('animal_id', Integer, primary_key=True),
          Column('weight', Numeric(6,2), nullable=False))

_dose = \
    Table('dose', metadata,
          Column('dose_id', Integer, primary_key=True),
          Column('dose_time_id', Integer,
                 ForeignKey(_dose_time.c.dose_time_id),
                 nullable=False),
          Column('animal_id', Integer, ForeignKey(_animal.c.animal_id),
                 nullable=False))

metadata.create_all()

class DoseTime(object):
    pass

class Animal(object):
    pass

class Dose(object):
    pass

mapper(DoseTime, _dose_time,
       properties = {'doses': relation(Dose, backref=backref('dose_time'),
                                       cascade='all, delete-orphan')})

mapper(Animal, _animal,
       properties = {'doses': relation(Dose, backref=backref('animal'),
                                       cascade='all, delete-orphan')})

mapper(Dose, _dose)

dose_time = DoseTime()
dose_time.timepoint = 15
session.save(dose_time)

animal = Animal()
animal.weight = 220.7
session.save(animal)

dose = Dose()
dose.animal = animal
dose.dose_time = dose_time
session.save(dose)

session.delete(animal)
session.flush()            # animal and dose are deleted

session.delete(dose_time)  # InvalidRequestError: tried to delete dose again
session.flush()

Comments (2)