SELECT on parent performed when committing DELETE on child even without cascading nor mappings to update #10596
-
Describe the bugWith a many-to-one lazy loaded relationship from Child to Parent, without an actual SQL foreign key, SQLAlchemy is performing a SELECT on the Parent when deleting the Child even if there is no cascade nor any Parent field to update. Optional link from https://docs.sqlalchemy.org which documents the behavior that is expectedhttps://docs.sqlalchemy.org/en/20/orm/relationship_api.html#sqlalchemy.orm.relationship.params.lazy SQLAlchemy Version in Use2.0.23 DBAPI (i.e. the database driver)pysqlite Database Vendor and Major VersionSQLite Python Version3.11.6, 3.12.0 Operating systemLinux, OSX To Reproduce#!/usr/bin/env python3
from sqlalchemy import create_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, foreign, mapped_column, relationship, remote
class Base(DeclarativeBase):
"""SQLAlchemy declarative base"""
class Parent(Base):
__tablename__ = "parent"
id: Mapped[int] = mapped_column(primary_key=True)
class Child(Base): # pylint:disable=too-few-public-methods
__tablename__ = "child"
name: Mapped[str] = mapped_column(primary_key=True)
parent_id: Mapped[int] = mapped_column()
# custom foreign condition without SQL-level foreign keys
parent: Mapped[Parent] = relationship(
primaryjoin=remote(Parent.id) == foreign(parent_id),
cascade="none",
lazy="select"
)
engine = create_engine("sqlite:///:memory:", echo=False)
Base.metadata.create_all(engine)
session = Session(bind=engine)
# NB: we are not creating any Parent
child = Child(name="some_child", parent_id=0)
session.add(child)
session.commit()
engine.echo = True
# test case
print("******************************** delete")
session.delete(child)
print("******************************** commit after delete")
session.commit()
# here SQLAlchemy will perform a SELECT FROM parent before a DELETE FROM childErrorAdditional contextDocumentation about
And documentation about
Here we have It is my understanding that SQLAlchemy should not perform a
I am not using an actual SQL ForeignKey because in my use case it is expected and normal to have childs referring to parent IDs that do not exist in the parent table. Tested variations:
The above test case has been reproduced in clean environments on Linux with Python 3.11.6, on macOS with Python 3.11.6 and on macOS with Python 3.12.0, always using the latest stable release of SQLAlchemy 2.0.23 installed through pip. |
Beta Was this translation helpful? Give feedback.
Replies: 2 comments 6 replies
-
|
it's not clear there is any "bug" here, though the documentation shows the form you are using: this form is insufficient at the moment because "parent_id" has not yet been mapped, which is leading the "use get" condition to revert to False: using a lambda allows the parent: Mapped[Parent] = relationship(
primaryjoin=lambda: remote(Parent.id) == foreign(Child.parent_id),
cascade="none",
lazy="select"
)will look into the annotation thing. |
Beta Was this translation helpful? Give feedback.
-
|
yah that parent_id column has no type yet. applying foreign() to it makes a copy of it, which has no type. the so far I think the most reasonable course of action here is to emit a warning / raise an error if foreign() or remote() are applied to columns not set up with a type yet |
Beta Was this translation helpful? Give feedback.
-
|
ill have a fix in #10597 |
Beta Was this translation helpful? Give feedback.
-
|
yeah I'm not 100% sure that it would strictly be classified as a "bug", at least not in the sense that something's broken, but from my expectations as a user after reading docs I did not expected an extra query. I guess it could have a non-trivial db load / performance impact on some DELETE-intensive workloads involving models configured as the provided example. I can confirm that the lambda approach solves the extra query and it works even with all relationship settings at their default (ie. not specifying class Child(Base):
__tablename__ = "child"
name: Mapped[str] = mapped_column(primary_key=True)
parent_id: Mapped[int] = mapped_column()
# custom foreign condition without SQL-level foreign keys
parent: Mapped[Parent] = relationship(primaryjoin=lambda: remote(Parent.id) == foreign(Child.parent_id)) |
Beta Was this translation helpful? Give feedback.
-
|
As far as I can tell #10597 was included in SQLAlchemy 2.0.24 but I'm still getting the extra SELECT described above even with the latest SA 2.0.29. I've also found a different use case that IMHO is more common and can have a bigger impact on workloads: one extra SELECT gets issued for every single row that gets deleted from an adjacency list. It is also a simpler code to reproduce. SQLAlchemy Version in Use2.0.29 DBAPI (i.e. the database driver)pysqlite Database Vendor and Major VersionSQLite Python Version3.11.8, 3.12.3 Operating systemLinux, OSX To Reproduce#!/usr/bin/env python3
"""SQLAlchemy test to reproduce weird SELECT-before-DELETE case"""
from sqlalchemy import create_engine, ForeignKey, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column, relationship
class Base(DeclarativeBase):
"""SQLAlchemy declarative base"""
class Node(Base):
__tablename__ = "nodes"
id: Mapped[int] = mapped_column(primary_key=True)
parent_id: Mapped[int | None] = mapped_column(ForeignKey("nodes.id"))
parent: "Mapped[Node | None]" = relationship(back_populates="children", remote_side="Node.id")
children: "Mapped[list[Node]]" = relationship(back_populates="parent")
engine = create_engine("sqlite:///:memory:", echo=True)
Base.metadata.create_all(engine)
session = Session(bind=engine)
session.add(Node(id=999)) # root node
session.add_all([Node(id=_, parent_id=999) for _ in range(100)])
session.commit()
session.expunge_all()
# test case
print("******************************** delete")
childs = session.execute(select(Node).where(Node.parent_id == 999)).scalars().all()
for child in childs:
session.delete(child)
print("******************************** commit after delete")
session.commit()
# here SQLAlchemy will perform 100 selects (one per deleted row) before issuing a single delete for all deleted PKs |
Beta Was this translation helpful? Give feedback.
-
|
I ran the original test case at the top, the output is: there is no SELECT from parent your second test is totally different. the Node.children relationship is loading on each Node because it's attempting to check for elements that should be DELETEd. Solution, add passive_deletes, which will assume other means are being used to emit the DELETE, I would recommend putting ON DELETE CASCADE on the foreign key constraint: https://docs.sqlalchemy.org/en/20/orm/cascades.html#passive-deletes output: |
Beta Was this translation helpful? Give feedback.
-
|
thanks for the feedback! I'll need some time to go trough everything :) |
Beta Was this translation helpful? Give feedback.
-
|
I had time to re-check this issue more carefully and I can confirm that I'm no more getting SELECT-before-DELETE from my first test case, I don't know what I was looking at when I said I had it with SA 2.0.29, sorry. I was also writing a reply about the second use case and having to go through that made me understand (I think) the reason of SELECTs in that use case. SQLAlchemy is basically doing |
Beta Was this translation helpful? Give feedback.
I ran the original test case at the top, the output is: