Can't delete cascade many-to-many with polymorphic relationships unless using lazy=dynamic

165 views
Skip to first unread message

maqui...@gmail.com

unread,
Dec 17, 2020, 5:50:06 PM12/17/20
to sqlalchemy
I have a polymorphic data model where association tables are to base classes and some relationships that link to different child classes to filter out the non-matching base classes, however these seem to result in sqlalchemy being unable to delete cascade properly

In a real case lets say i have
class Base():
   children = relationship("Base", secondary="mix_base_base", cascade="all, delete")
   someTypes = relationship("SomeType", secondary="mix_base_base", cascade="all, delete")
   other_types = relationship("OtherType", secondary="mix_base_base", cascade="all")

class SomeType(Base):
    parents = relationship("Base", secondary="mix_base_base", passive_deletes=True)

class OtherType(Base):
    parents = relationship("Base", secondary="mix_base_base", passive_deletes=True)


if I delete a base that doesn't have relationships to SomeType and OtherType, then everything works great. However once I add those extra relationships sqlalchemy no longer deletes the children relationships.  This can be overcome by using lazy="dynamic" on the relationships, but at GREAT performance cost (about 100%), which isn't good.

Does anyone know how to get around this without using dynamic loading? The specific relationships are important for front end pagination of data, but the performance cost is too great for the amount of data in this system to double the query times.

a simplified and stupid workable example where I just add a redundant children2 relationship to ChildClass, resulting in the same problem:

from uuid import uuid4

from sqlalchemy import Column
from sqlalchemy.dialects.postgresql.base import UUID
from sqlalchemy.orm import relationship

from webapp.database.orm.base import XPressoBase

class ChildClass(Base):
__tablename__ = "child_class"
id = Column("id", UUID(as_uuid=True), primary_key=True, default=uuid4())
parents = relationship("ParentClass", secondary="mix_parent_class_child_class", passive_deletes=True)
children = relationship("ChildChildClass", secondary="mix_child_class_child_child_class", cascade="all, delete")
children2 = relationship("ChildChildClass", secondary="mix_child_class_child_child_class", cascade="all, delete")

class ChildChildClass(Base):
__tablename__ = "child_child_class"
id = Column("id", UUID(as_uuid=True), primary_key=True, default=uuid4())
parents = relationship("ChildClass", secondary="mix_child_class_child_child_class", passive_deletes=True)

class ParentClass(Base):
__tablename__ = "parent_class"
id = Column("id", UUID(as_uuid=True), primary_key=True, default=uuid4())
children = relationship("ChildClass", secondary="mix_parent_class_child_class", cascade="all, delete")

from sqlalchemy.sql.schema import Table, ForeignKey, Column, UniqueConstraint

from webapp.database.orm.base import XPressoBase

mix_parent_class_child_class = Table(
"mix_parent_class_child_class",
XPressoBase.metadata,
Column("parent_class_id", ForeignKey("parent_class.id", ondelete="CASCADE"), nullable=False),
Column("child_class_id", ForeignKey("child_class.id"), nullable=False),
UniqueConstraint(
"parent_class_id", "child_class_id", name="uix_parent_class_child_class"
),
)

mix_child_class_child_child_class = Table(
"mix_child_class_child_child_class",
XPressoBase.metadata,
Column("child_class_id", ForeignKey("child_class.id", ondelete="CASCADE"), nullable=False),
Column("child_child_class_id", ForeignKey("child_child_class.id"), nullable=False),
UniqueConstraint(
"child_class_id", "child_child_class_id", name="uix_child_class_child_child_class"
),
)

maqui...@gmail.com

unread,
Dec 17, 2020, 6:01:21 PM12/17/20
to sqlalchemy

I think I may have just found a solution? Not sure if this is correct but it looks like it worked when i changed the "extra" relationship to passive_deletes=True instead of cascade

class ChildClass(XPressoBase):

__tablename__ = "child_class"
id = Column("id", UUID(as_uuid=True), primary_key=True, default=uuid4())
parents = relationship("ParentClass", secondary="mix_parent_class_child_class", passive_deletes=True)
children = relationship("ChildChildClass", secondary="mix_child_class_child_child_class", cascade="all, delete")
children2 = relationship("ChildChildClass", secondary="mix_child_class_child_child_class", passive_deletes=True)

Mike Bayer

unread,
Dec 17, 2020, 7:09:32 PM12/17/20
to noreply-spamdigest via sqlalchemy
your examples aren't complete or runnable so I don't really know what the issue is, although having two relationships to the same target class seems a little unusual and I'm not sure why you'd need that.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

Mark Aquino

unread,
Dec 17, 2020, 7:16:00 PM12/17/20
to sqlal...@googlegroups.com
They seem to be runnable except the base should be DeclarativeMeta

The relationships aren’t to the same target in real life,

It’s like this:

Class BaseClass:
...

Class SubClassA(BaseClass)
...

Class SubclassB(BaseClass):
...

(Plus Many other subclasses)

A Mixer “BaseClassBaseClass” such that child subclassA and subclassB are both present in mix_base_class_bass_class

BaseClass has relationships to BaseClass, SubClassA and SubClassB, so you can access only SubClassAs and SubClassBs or all SubClassXs that could be filtered to whatever subclass you need. All these relationships use the same mixer table, because they all share a primary key with BaseClass (and removing the need to make explicit mixed tables for every subclass).

Mark Aquino
From: sqlal...@googlegroups.com <sqlal...@googlegroups.com> on behalf of Mike Bayer <mik...@zzzcomputing.com>
Sent: Thursday, December 17, 2020 7:08:58 PM
To: noreply-spamdigest via sqlalchemy <sqlal...@googlegroups.com>
Subject: Re: [sqlalchemy] Re: Can't delete cascade many-to-many with polymorphic relationships unless using lazy=dynamic
 

Mike Bayer

unread,
Dec 17, 2020, 7:25:58 PM12/17/20
to noreply-spamdigest via sqlalchemy


On Thu, Dec 17, 2020, at 7:15 PM, Mark Aquino wrote:
They seem to be runnable except the base should be= DeclarativeMeta

OK, runnable example is below. 

how about:

1. target database type (Postgresql?)

2. sample data, inserts, etc.

3. sample queries

4. operation that fails

5. stack trace, etc


thanks!

from uuid import uuid4

from sqlalchemy import Column
from sqlalchemy.dialects.postgresql.base import UUID
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class ChildClass(Base):
    __tablename__ = "child_class"
    id = Column("id", UUID(as_uuid=True), primary_key=True, default=uuid4())
    parents = relationship(
        "ParentClass",
        secondary="mix_parent_class_child_class",
        passive_deletes=True,
    )
    children = relationship(
        "ChildChildClass",
        secondary="mix_child_class_child_child_class",
        cascade="all, delete",
    )
    children2 = relationship(
        "ChildChildClass",
        secondary="mix_child_class_child_child_class",
        cascade="all, delete",
    )


class ChildChildClass(Base):
    __tablename__ = "child_child_class"
    id = Column("id", UUID(as_uuid=True), primary_key=True, default=uuid4())
    parents = relationship(
        "ChildClass",
        secondary="mix_child_class_child_child_class",
        passive_deletes=True,
    )


class ParentClass(Base):
    __tablename__ = "parent_class"
    id = Column("id", UUID(as_uuid=True), primary_key=True, default=uuid4())
    children = relationship(
        "ChildClass",
        secondary="mix_parent_class_child_class",
        cascade="all, delete",
    )


from sqlalchemy.sql.schema import Table, ForeignKey, Column, UniqueConstraint


mix_parent_class_child_class = Table(
    "mix_parent_class_child_class",
    Base.metadata,
    Column(
        "parent_class_id",
        ForeignKey("parent_class.id", ondelete="CASCADE"),
        nullable=False,
    ),
    Column("child_class_id", ForeignKey("child_class.id"), nullable=False),
    UniqueConstraint(
        "parent_class_id",
        "child_class_id",
        name="uix_parent_class_child_class",
    ),
)

mix_child_class_child_child_class = Table(
    "mix_child_class_child_child_class",
    Base.metadata,
    Column(
        "child_class_id",
        ForeignKey("child_class.id", ondelete="CASCADE"),
        nullable=False,
    ),
    Column(
        "child_child_class_id",
        ForeignKey("child_child_class.id"),
        nullable=False,
    ),
    UniqueConstraint(
        "child_class_id",
        "child_child_class_id",
        name="uix_child_class_child_child_class",
    ),
)




maqui...@gmail.com

unread,
Dec 17, 2020, 7:32:43 PM12/17/20
to sqlalchemy
1. target database = postgres, 

example queries and stacktrace:

>>> from webapp.database.orm.models import ParentClass, ChildClass, ChildChildClass
>>> p = ParentClass()
>>> c = ChildClass()
>>> cc = ChildChildClass()
>>> c.children.append(cc)
>>> p.children.append(c)
>>> session.add(p)
>>> session.commit()
>>> p = session.query(ParentClass).one()
>>> [cc for c in p.children for cc in c.children]
[ChildChildClass(id=UUID('1253a435-3330-4e36-bafc-ad8ff5176c4d'))]
>>> session.delete(p)
>>> session.flush()
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2496, in flush
    self._flush(objects)
  File "/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2637, in _flush
    transaction.rollback(_capture_exception=True)
  File "/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
    raise exception
  File "/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2597, in _flush
    flush_context.execute()
  File "/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 422, in execute
    rec.execute(self)
  File "/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 538, in execute
    self.dependency_processor.process_deletes(uow, states)
  File "/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/orm/dependency.py", line 1104, in process_deletes
    self._run_crud(
  File "/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/orm/dependency.py", line 1201, in _run_crud
    raise exc.StaleDataError(
sqlalchemy.orm.exc.StaleDataError: DELETE statement on table 'mix_child_class_child_child_class' expected to delete 1 row(s); Only 0 were matched.
>>> 
KeyboardInterrupt

Works when ChildClass is declared as follows instead of as written in original question:
class ChildClass(Base):
    __tablename__ = "child_class"
    id = Column("id", UUID(as_uuid=True), primary_key=True, default=uuid4())
    parents = relationship(
        "ParentClass",
        secondary="mix_parent_class_child_class",
        passive_deletes=True,
    )
    children = relationship(
        "ChildChildClass",
        secondary="mix_child_class_child_child_class",
        cascade="all, delete",
    )
    children2 = relationship(
        "ChildChildClass",
        secondary="mix_child_class_child_child_class",
        passive_deletes=True
    )

>>> from webapp.database.orm.models import ParentClass, ChildClass, ChildChildClass
>>> p = ParentClass()
>>> c = ChildClass()
>>> cc = ChildChildClass()
>>> c.children.append(cc)
>>> p.children.append(c)
>>> session.add(p)
>>> session.commit()
>>> p = session.query(ParentClass).one()
>>> session.delete(p)
>>> session.commit()

maqui...@gmail.com

unread,
Dec 17, 2020, 7:40:24 PM12/17/20
to sqlalchemy
My operating assumption is that sqlalchemy looks at each relationship and tries to delete it, but since the previous relationship to the same base class was already deleted, it throws the exception and the session rolls back.

The error from above is essentially the same as the actual error in my code base.  I guess ideally there would be a way to just disable that functionality but my solution works.  Just will take several hours to change everything correctly like this because we have about 200 models that are all interconnected like this. I apologize for not giving a proper working example I would have needed to create a brand new project unfortunately because the code base is so complicated

Mike Bayer

unread,
Dec 17, 2020, 9:33:12 PM12/17/20
to noreply-spamdigest via sqlalchemy
perhaps you are looking to set viewonly=True on this second relationship?  that will exclude it from any kind of persistence operation.    it will only be used to load things in from the database but not accept or honor any mutations of data.

not sure why you need to have two relationships that are identical, but if you do, then yes you want only one of them to be the one that writes changes to the DB.

Mark Aquino

unread,
Dec 18, 2020, 8:40:24 AM12/18/20
to sqlal...@googlegroups.com
Thanks. That could work. The reason we need specific relationships to the child class types is for our front end when it queries the data, which we use sqlalchemy-graphene to do, if we query the base class relationship it returns empty rows and makes pagination impossible as the front end is unable to know how many rows containing the desired type are present when the mixer table contains references to several different classes by using the id shared on the base class.

Mark Aquino
Sent: Thursday, December 17, 2020 9:32:40 PM

Mike Bayer

unread,
Dec 18, 2020, 10:26:01 AM12/18/20
to noreply-spamdigest via sqlalchemy
your example shows two relationships to the identical target class:

    children = relationship(
        "ChildChildClass",
        secondary="mix_child_class_child_child_class",
        cascade="all, delete",
    )
    children2 = relationship(
        "ChildChildClass",
        secondary="mix_child_class_child_child_class",
        passive_deletes=True
    )



Reply all
Reply to author
Forward
0 new messages