delete of polymorphic objects leaves orphaned parent objects

[natsjoo sodillepa]

Hi all,

We have a list of polymorphic objects from which delete object does not work, not matter what we try.

The situation:

class ItemGroup(Base):
    __tablename__ = 'item_group'
    __table_args__ = (
        UniqueConstraint('model_id', 'item_group_color_id', name='unique_model_id_item_group_color_id_uc'),
    )

    id = Column(Integer, primary_key=True)
    items = relationship("Item", back_populates="item_group")

class Item(Base):
    __tablename__ = 'item'

    id = Column(Integer, primary_key=True)

    item_group_id = Column(ForeignKey('item_group.id'), nullable=False, index=True)
    item_group = relationship('ItemGroup', back_populates="items", uselist=False)

    __mapper_args__ = {
        'polymorphic_identity': __tablename__,
        'polymorphic_on': item_type
    }

class ItemMeta(Item):
    __tablename__ = 'item_meta'

    id = Column(Integer, ForeignKey('item.id', ondelete="CASCADE"), primary_key=True)

    meta_name = Column(String(255, collation), nullable=False)

    __mapper_args__ = {
        'polymorphic_identity': __tablename__,
    }

The problem occurs after a delete:

  session.query(ItemMeta).filter_by(item_group=ig).delete()

Now, querying the child works fine:

  session.query(ItemMeta).filter_by(item_group=ig).all()

  []

But querying the parent:

  test_fixtures.session.query(Item).filter_by(item_group=ig).all()

 

  Give: Instance '<ItemMeta at 0x7f0b024afcf8>' has been deleted, or its row is otherwise not present.

In the database I can see the lingering parent objects. I guess that I have to use something like
delete-orphan, but as I dont have a relation from child to father, so now I'm stuck.

Any ideas are welcome.

Kind regards,
Nacho

[Simon King]

You haven't given a complete script to reproduce the problem, so I'm
not certain what happened. The following is just a guess

You are using query(...).delete() with joined-table inheritance, which
has a lot of caveats in the docs:

https://docs.sqlalchemy.org/en/13/orm/query.html#sqlalchemy.orm.query.Query.delete

As far as I can tell, SA will issue a query to delete rows from the
item table. You've got ON DELETE CASCADE on the item_meta.id foreign
key, so I guess the db will delete the corresponding rows from
item_meta.

The default value for synchronize_session in Query.delete is
'evaluate', which means SA will try to find objects in the in-memory
session which match your deletion criteria and remove them from the
session. I'm guessing that this step is missing at least one ItemMeta
object for some reason.

When you query for Item objects, SA will first autoflush any pending
changes in the session. It looks like you have some pending changes on
an ItemMeta instance, so it tries to flush those, but the
corresponding row has already been deleted, hence the error.

If you provide a runnable test script, we may be able to give more answers.

Hope that helps,

Simon

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> 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.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/7dea2eaa-6390-4a54-abd5-fae925727c17%40googlegroups.com.

[natsjoo sodillepa]

Hi Simon,

Thank you for your reply.

I've tried all the variant I could think of, also eg for item in items: session.delete(it),

with and without remove on the list of items, all known mapper and relation params but nothing.

However, I've tried a similar structure from examples from the web and those seems to work,

so the problem must lie somewhere in our code. Or could it have anything to do with scoped sessions?

Anyway: I'm working on the isolation of the problem, which is not that easy. I'll come back here if I've

got something more MCVE or with a solution.

Kind regards,

Nacho

[natsjoo sodillepa]

Ok,

I have managed to get rid of the problem, but I'm don't like what is happening.

Solution: remove a secondary relation from the model:

model = relationship("Model", secondary='item_group', uselist=False)

After this, the following code does do a proper delete of the items:

for it in self.session.query(ItemMeta).filter_by(item_group=item_group).all():
    if type(it) == ItemMeta:
        item_group.items.remove(it)
        self.session.delete(it)
self.session.commit()

However, things are still not ideal. The following should work but doesn't:

for item in item_group.items:

   self.session.delete(item)

Then only half of the item get deleted, which is a bit weird.

Anybody any thoughts on this? Why does a secondary relation of this kind of effect on a delete?

Kinde regards,

Nacho

[Simon King]

I can't make a guess without a script to reproduce it I'm afraid.

Sorry,

Simon

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> 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.

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/073b7947-ed51-4b63-bfdb-a5499128c06c%40googlegroups.com.

[Mike Bayer]

like Simon wrote, we would need more detail on the exact query and how you came up with it, in the original example it refers to an ItemGroup object "ig", is that the object that may have been deleted, is there a stack trace, etc.

If you can just put together a script that has the minimal mappings in use, and a little bit of sample data, and the steps you are doing to get to the query that fails, even if the script doesn't reproduce the problem I may be able to come up with how it would come to the stack trace you are describing (need that too as it shows where the problem originates).

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/073b7947-ed51-4b63-bfdb-a5499128c06c%40googlegroups.com.

[natsjoo sodillepa]

@Mike @Simon,

Thanks guys,

I fully understand what you need. However I'm currently very, very busy and just don't have the time.

The reason I posted without providing what you need was a little hope that maybe somebody

recognized a similar problem.

We have around 120 objects and trimming down to the root of the evil will take some time. I will

try to do so and come back here if I don't get it.

Cya,

Nacho