[openstack-dev] [oslo.db] Proposal: Get rid of deleted column

Jay Pipes jaypipes at gmail.com
Tue Aug 20 20:05:33 UTC 2013


I see the following use case:

1) Create something with a unique name within your tenant
2) Delete that
3) Create something with the same unique name immediately after

As a pointless and silly use case that we should not cater to.

It's made the database schema needlessly complex IMO and added columns 
to a unique constraint that make a DBA's job more complex in order to 
fulfill a use case that really isn't particularly compelling.

I was having a convo on IRC with Boris and stated the use case in 
different terms:

If you delete your Gmail email address, do you expect to immediately be 
able to create a new Gmail email with the previous address?

If you answer yes, then this unique constraint on the deleted column 
makes sense to you. If you answer no, then the whole thing seems like 
we've spent a lot of effort on something that isn't particularly useful 
except in random test cases that try to create and delete the same thing 
in rapid succession. And IMO, those kinds of test cases should be 
deleted -- hard-deleted.

Best,
-jay

On 08/20/2013 03:33 PM, Chris Behrens wrote:
>
> 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. :)
>
> - Chris
>
>
> 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?
>>
>> -jay
>>
>> 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()
>>>>         self.save(session=session)
>>>>
>>>> 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):
>>>>     <snip>...
>>>>     service = relationship(Service,
>>>>                            backref=backref('compute_node'),
>>>>                            foreign_keys=service_id,
>>>>                            primaryjoin='and_('
>>>>                                 '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 ;)
>>>>
>>>> Thoughts?
>>>>
>>>> -jay
>>>>
>>>> _______________________________________________
>>>> OpenStack-dev mailing list
>>>> OpenStack-dev at lists.openstack.org
>>>> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>>>
>>>
>>> _______________________________________________
>>> OpenStack-dev mailing list
>>> OpenStack-dev at lists.openstack.org
>>> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>>>
>>
>>
>> _______________________________________________
>> OpenStack-dev mailing list
>> OpenStack-dev at lists.openstack.org
>> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>
>
> _______________________________________________
> OpenStack-dev mailing list
> OpenStack-dev at lists.openstack.org
> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>




More information about the OpenStack-dev mailing list