passive_deletes for joined table inheritance

this is not supported right now, a SELECT is emitted in all cases if the PK of the child table is not synonymous with the parent.

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base= declarative_base()

class A(Base):
    __tablename__ = "a"
    id = Column('id', Integer, primary_key=True)
    type = Column(String)
    __mapper_args__ = {
        'polymorphic_on':type,
        'polymorphic_identity':'a'
    }
class B(A):
    __tablename__ = 'b'
    b_table_id = Column('b_table_id', Integer, primary_key=True)
    bid = Column('bid', Integer, ForeignKey('a.id'))
    data = Column('data', String)
    __mapper_args__ = {
        'polymorphic_identity':'b'
    }

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
s = Session(e)

s.add(B(b_table_id=5))
s.commit()
s.close()

b1 = s.query(A).first()
s.delete(b1)
# emits load for "b" as we don't have b_table_id
s.commit()

now here is the thing. We are in fact just loading the "B" row based on the FK:

SELECT b.bid AS b_bid, b.b_table_id AS b_b_table_id, b.data AS b_data 
FROM b 
WHERE %(param_1)s = b.bid
{'param_1': 1}
DELETE FROM b WHERE b.b_table_id = %(b_table_id)s
{'b_table_id': 5}
DELETE FROM a WHERE a.id = %(id)s

so in theory, we could at least skip the need for the SELECT if we emitted the DELETE based on the FK criterion ! That would work in all cases without a flag. The DELETE would check that exactly one row was deleted.

This would be a non-trivial enhancement as the whole mechanism of mapper._delete_obj() would become more complex.

A passive_deletes equivalent flag would also make mapper._delete_obj() more complex but not as much as it means we just skip the dependent table, rather than constructing a new kind of DELETE.

Consider opening a second ticket to distinguish the "delete on FKs" optimization from the "add passive_deletes" optimization. In all likelihood we'd skip the more elaborate one as the new flag would be the better solution in any case, if the target backend supports ON DELETE CASCADE.

Make sure this caveat is documented in the inheritance docs, and suggest query.delete() and passive_deletes=True as preferred workarounds to the "SELECT a row to DELETE it" problem.

Comments (3)