<div dir="ltr"><div>Hi,</div><div>my experience is that "soft delete" is important to keep record of deleted instances and its characteristics. </div><div>In fact in my organization we are obliged to keep these records for several months.</div><div>However, it would be nice that after few months we were able to purge the DB with a nova tool.</div><div><br></div><div>In the particular case of this cells table my major concern is that having a "delete" field maybe means that top and children databases need to be synchronized. Looking into the current cells design having duplicated information in different databases is one of the main issues.</div><div><br></div><div>Belmiro</div><div><br></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Wed, Nov 26, 2014 at 4:40 PM, Andrew Laski <span dir="ltr"><<a href="mailto:andrew.laski@rackspace.com" target="_blank">andrew.laski@rackspace.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="HOEnZb"><div class="h5"><br>
On 11/25/2014 11:54 AM, Solly Ross wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
I can't comment on other projects, but Nova definitely needs the soft<br>
delete in the main nova database. Perhaps not for every table, but<br>
there is definitely code in the code base which uses it right now.<br>
Search for read_deleted=True if you're curious.<br>
</blockquote>
Just to save people a bit of time, it's actually `read_deleted='yes'`<br>
or `read_deleted="yes"` for many cases.<br>
<br>
Just to give people a quick overview:<br>
<br>
A cursory glance (no pun intended) seems to indicate that quite a few of<br>
these are reading potentially deleted flavors. For this case, it makes<br>
sense to keep things in one table (as we do).<br>
<br>
There are also quite a few that seem to be making sure deleted "things"<br>
are properly cleaned up. In this case, 'deleted' acts as a "CLEANUP"<br>
state, so it makes just as much sense to keep the deleted rows in a<br>
separate table.<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
For this case in particular, the concern is that operators might need<br>
to find where an instance was running once it is deleted to be able to<br>
diagnose issues reported by users. I think that's a valid use case of<br>
this particular data.<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
This is a new database, so its our big chance to get this right. So,<br>
ideas welcome...<br>
<br>
Some initial proposals:<br>
<br>
- we do what we do in the current nova database -- we have a deleted<br>
column, and we set it to true when we delete the instance.<br>
<br>
- we have shadow tables and we move delete rows to a shadow table.<br>
</blockquote>
<br>
Both approaches are viable, but as the soft-delete column is widespread, it<br>
would be thorny for this new app to use some totally different scheme,<br>
unless the notion is that all schemes should move to the audit table<br>
approach (which I wouldn’t mind, but it would be a big job). FTR, the<br>
audit table approach is usually what I prefer for greenfield development,<br>
if all that’s needed is forensic capabilities at the database inspection<br>
level, and not as much active GUI-based “deleted” flags. That is, if you<br>
really don’t need to query the history tables very often except when<br>
debugging an issue offline. The reason its preferable is because those<br>
rows are still “deleted” from your main table, and they don’t get in the<br>
way of querying. But if you need to refer to these history rows in<br>
context of the application, that means you need to get them mapped in such<br>
a way that they behave like the primary rows, which overall is a more<br>
difficult approach than just using the soft delete column.<br>
</blockquote></blockquote>
I think it does really come down here to how you intend to use the soft-delete<br>
functionality in Cells. If you just are using it to debug or audit, then I think<br>
the right way to go would be either the audit table (potentially can store more<br>
lifecycle data, but could end up taking up more space) or a separate shadow<br>
table (takes up less space).<br>
<br>
If you are going to use the soft delete for application functionality, I would<br>
consider differentiating between "deleted" and "we still have things left to<br>
clean up", since this seems to be mixing two different requirements into one.<br>
</blockquote>
<br></div></div>
The case that spawned this discussion is one where deleted rows are not needed for application functionality. So I'm going to update the proposed schema there to not include a 'deleted' column. Fortunately there's still some time before the question of how to handle deletes needs to be fully sorted out.<div class="HOEnZb"><div class="h5"><br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
That said, I have a lot of plans to send improvements down the way of the<br>
existing approach of “soft delete column” into projects, from the querying<br>
POV, so that criteria to filter out soft delete can be done in a much more<br>
robust fashion (see<br>
<a href="https://bitbucket.org/zzzeek/sqlalchemy/issue/3225/query-heuristic-inspector-event" target="_blank">https://bitbucket.org/zzzeek/<u></u>sqlalchemy/issue/3225/query-<u></u>heuristic-inspector-event</a>).<br>
But this is still more complex and less performant than if the rows are<br>
just gone totally, off in a history table somewhere (again, provided you<br>
really don’t need to look at those history rows in an application context,<br>
otherwise it gets all complicated again).<br>
</blockquote>
Interesting. I hadn't seen consistency between the two databases as<br>
trumping doing this less horribly, but it sounds like its more of a<br>
thing that I thought.<br>
<br>
Thanks,<br>
Michael<br>
<br>
--<br>
Rackspace Australia<br>
<br>
______________________________<u></u>_________________<br>
OpenStack-dev mailing list<br>
<a href="mailto:OpenStack-dev@lists.openstack.org" target="_blank">OpenStack-dev@lists.openstack.<u></u>org</a><br>
<a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev" target="_blank">http://lists.openstack.org/<u></u>cgi-bin/mailman/listinfo/<u></u>openstack-dev</a><br>
<br>
</blockquote>
______________________________<u></u>_________________<br>
OpenStack-dev mailing list<br>
<a href="mailto:OpenStack-dev@lists.openstack.org" target="_blank">OpenStack-dev@lists.openstack.<u></u>org</a><br>
<a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev" target="_blank">http://lists.openstack.org/<u></u>cgi-bin/mailman/listinfo/<u></u>openstack-dev</a><br>
</blockquote>
<br>
<br>
______________________________<u></u>_________________<br>
OpenStack-dev mailing list<br>
<a href="mailto:OpenStack-dev@lists.openstack.org" target="_blank">OpenStack-dev@lists.openstack.<u></u>org</a><br>
<a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev" target="_blank">http://lists.openstack.org/<u></u>cgi-bin/mailman/listinfo/<u></u>openstack-dev</a><br>
</div></div></blockquote></div><br></div>