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

Jay Pipes jaypipes at gmail.com
Tue Aug 20 20:59:58 UTC 2013

On 08/20/2013 04:16 PM, Chris Behrens wrote:
> On Aug 20, 2013, at 1:05 PM, Jay Pipes <jaypipes at gmail.com> wrote:
>> 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.
> I would answer 'no' to the gmail question.  I would answer 'yes' depending on what other things we may talk about.  If we put (or maybe we have this -- I didn't check) unique constraints on the metadata table for metadata key… It would be rather silly to not allow someone to reset some metadata with the same key immediately.  One could argue that we just un-delete the former row and update it, however… but I think that breaks archiving (something*I'm* not a fan of ;)

Yeah, totally agreed. It's wrong to lump all entities into the same boat.

Some back-of-napkin thoughts...

We should take a look at look at the various entities in the various 
database schemata and ask the following questions:

1) Do we care about archival of the entity?

2) Do we care about audit history of changes to the entity?

3) Do we care about whether an entity with a unique name/key needs to be 
able to be deleted and immediately re-created with the same name/key?

For entities with "No" answers to #1 -- and I would suggest instance 
metadata might be one of these things... -- we could do hard-delete and 

For entities with "Yes" answers to #1 and "No" answers to #2 and #3, we 
could use a simple periodic purge/archive process and leave deleted 
columns out of unique constraints on the tables

For entities with "Yes" answers to #1 and #2 and "No" answers to #3, we 
could use audit tables (note: not shadow tables, but audit tables, which 
record the changes made to entities over time) along with a periodic 
archival/purge process and leave the deleted columns out of unique 
constraints on the tables.

For entities with "Yes" answers to all three, we could use hard-delete 
in the main table along with audit tables and remove the 
deleted_at/deleted/created_at/updated_at from tables (since the entire 
history of the entity is contained in the audit table.


More information about the OpenStack-dev mailing list