Public bug reported: In [1] there is a good discussion on how the 'cascade' property specified for sqlachemy.orm.relationship interacts with the 'ON DELETE CASCADE' specified in DDL.
I stumbled on this when I was doing some DB access profiling and noticed multiple DELETE statements were emitted for a delete subnet operation [2], whereas I expected a single DELETE statement only; I expected that the cascade behaviour configured on db tables would have taken care of DNS servers, host routes, etc. What is happening is that sqlalchemy is perform orm-level cascading rather than relying on the database foreign key cascade options. And it's doing this because we told it to do so. As the SQLAlchemy documentation points out [3] there is no need to add the complexity of orm relationships if foreign keys are correctly configured on the database, and the passive_deletes option should be used. Enabling such option in place of all the cascade options for relationship caused a single DELETE statement to be issued [4]. This is not a massive issue (possibly the time spent in extra queries is just .5ms), but surely it is something worth doing - if nothing else because it seems Neutron is not using SQLAlchemy in the correct way. As someone who's been doing this mistake for ages, for what is worth this has been for me a moment where I realized that sometimes it's good to be told RTFM. [1] http://docs.sqlalchemy.org/en/latest/orm/cascades.html [2] http://paste.openstack.org/show/256289/ [3] http://docs.sqlalchemy.org/en/latest/orm/collections.html#passive-deletes [4] http://paste.openstack.org/show/256301/ ** Affects: neutron Importance: Medium Assignee: Salvatore Orlando (salvatore-orlando) Status: New ** Tags: db ** Changed in: neutron Importance: Undecided => Medium -- You received this bug notification because you are a member of Yahoo! Engineering Team, which is subscribed to neutron. https://bugs.launchpad.net/bugs/1461102 Title: cascade in orm relationships shadows ON DELETE CASCADE Status in OpenStack Neutron (virtual network service): New Bug description: In [1] there is a good discussion on how the 'cascade' property specified for sqlachemy.orm.relationship interacts with the 'ON DELETE CASCADE' specified in DDL. I stumbled on this when I was doing some DB access profiling and noticed multiple DELETE statements were emitted for a delete subnet operation [2], whereas I expected a single DELETE statement only; I expected that the cascade behaviour configured on db tables would have taken care of DNS servers, host routes, etc. What is happening is that sqlalchemy is perform orm-level cascading rather than relying on the database foreign key cascade options. And it's doing this because we told it to do so. As the SQLAlchemy documentation points out [3] there is no need to add the complexity of orm relationships if foreign keys are correctly configured on the database, and the passive_deletes option should be used. Enabling such option in place of all the cascade options for relationship caused a single DELETE statement to be issued [4]. This is not a massive issue (possibly the time spent in extra queries is just .5ms), but surely it is something worth doing - if nothing else because it seems Neutron is not using SQLAlchemy in the correct way. As someone who's been doing this mistake for ages, for what is worth this has been for me a moment where I realized that sometimes it's good to be told RTFM. [1] http://docs.sqlalchemy.org/en/latest/orm/cascades.html [2] http://paste.openstack.org/show/256289/ [3] http://docs.sqlalchemy.org/en/latest/orm/collections.html#passive-deletes [4] http://paste.openstack.org/show/256301/ To manage notifications about this bug go to: https://bugs.launchpad.net/neutron/+bug/1461102/+subscriptions -- Mailing list: https://launchpad.net/~yahoo-eng-team Post to : yahoo-eng-team@lists.launchpad.net Unsubscribe : https://launchpad.net/~yahoo-eng-team More help : https://help.launchpad.net/ListHelp
