[openstack-dev] [oslo.db] Proposal: Get rid of deleted column
cbehrens at codestud.com
Tue Aug 20 19:33:09 UTC 2013
This is kind of a stupid example, but it makes the point:
For instances table, we want to make sure 'uuid' is unique. But we can't put a unique constraint on that alone. If that instance gets deleted.. we should be able to create another entry with the same uuid without a problem. So we need a unique constraint on uuid+deleted. But if 'deleted' is only 0 or 1… we can only have 1 entry deleted and 1 entry not deleted. Using deleted=`id` to mark deletion solves that problem. You could use deleted_at… but 2 creates and deletes within the same second would not work. :)
On Aug 20, 2013, at 7:33 AM, Jay Pipes <jaypipes at gmail.com> wrote:
> *sigh* I wish I'd been aware of these conversations and been in the Grizzly summit session on soft delete...
> What specific unique constraint was needed that changing the deleted column to use the id value solved?
> On 08/19/2013 03:56 AM, Chris Behrens wrote:
>> 'deleted' is used so that we can have proper unique constraints by setting it to `id` on deletion. This was not the case until Grizzly, and before Grizzly I would have agreed completely.
>> - Chris
>> On Aug 19, 2013, at 12:39 AM, Jay Pipes <jaypipes at gmail.com> wrote:
>>> I'm throwing this up here to get some feedback on something that's always bugged me about the model base used in many of the projects.
>>> There's a mixin class that looks like so:
>>> class SoftDeleteMixin(object):
>>> deleted_at = Column(DateTime)
>>> deleted = Column(Integer, default=0)
>>> def soft_delete(self, session=None):
>>> """Mark this object as deleted."""
>>> self.deleted = self.id
>>> self.deleted_at = timeutils.utcnow()
>>> Once mixed in to a concrete model class, the primary join is typically modified to include the deleted column, like so:
>>> class ComputeNode(BASE, NovaBase):
>>> service = relationship(Service,
>>> 'ComputeNode.service_id == Service.id,'
>>> 'ComputeNode.deleted == 0)')
>>> My proposal is to get rid of the deleted column in the SoftDeleteMixin class entirely, as it is redundant with the deleted_at column. Instead of doing a join condition on deleted == 0, one would instead just do the join condition on deleted_at is None, which translates to the SQL: AND deleted_at IS NULL.
>>> There isn't much of a performance benefit -- you're only reducing the row size by 4 bytes. But, you'd remove the redundant data from all the tables, which would make the normal form freaks like myself happy ;)
>>> OpenStack-dev mailing list
>>> OpenStack-dev at lists.openstack.org
>> OpenStack-dev mailing list
>> OpenStack-dev at lists.openstack.org
> OpenStack-dev mailing list
> OpenStack-dev at lists.openstack.org
More information about the OpenStack-dev