[openstack-dev] [oslo.db] Proposal: Get rid of deleted column
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