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 <jaypi...@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 <jaypi...@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@lists.openstack.org
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
_______________________________________________
OpenStack-dev mailing list
OpenStack-dev@lists.openstack.org
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
_______________________________________________
OpenStack-dev mailing list
OpenStack-dev@lists.openstack.org
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
_______________________________________________
OpenStack-dev mailing list
OpenStack-dev@lists.openstack.org
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
_______________________________________________
OpenStack-dev mailing list
OpenStack-dev@lists.openstack.org
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
