Bug 534320 (RHQ-1128)

These bulk deletes need to have their configurations bulk deleted first:

        q = entityManager.createNamedQuery(PackageInstallationStep.QUERY_DELETE_BY_RESOURCES);
        q = entityManager.createNamedQuery(InstalledPackageHistory.QUERY_DELETE_BY_RESOURCES);
        q = entityManager.createNamedQuery(ResourceOperationHistory.QUERY_DELETE_BY_RESOURCES);
        q = entityManager.createNamedQuery(CreateResourceHistory.QUERY_DELETE_BY_RESOURCES);
        q = entityManager.createNamedQuery(ResourceConfigurationUpdate.QUERY_DELETE_BY_RESOURCES);
        q = entityManager.createNamedQuery(PluginConfigurationUpdate.QUERY_DELETE_BY_RESOURCES);

There is a difficulty here.

I thought all I needed to do was:

// bulk delete: Config update and its config (added the first 3 lines here)
q = entityManager.createNamedQuery(ResourceConfigurationUpdate.QUERY_DELETE_CONFIG_BY_RESOURCES);
q.setParameter("resources", resources);
q.executeUpdate();
q = entityManager.createNamedQuery(ResourceConfigurationUpdate.QUERY_DELETE_BY_RESOURCES);
q.setParameter("resources", resources);
q.executeUpdate();

Where:

@NamedQuery(name = ResourceConfigurationUpdate.QUERY_DELETE_CONFIG_BY_RESOURCES, query = "DELETE FROM Configuration doomed WHERE doomed.id IN (SELECT rcu.configuration FROM ResourceConfigurationUpdate rcu WHERE rcu.resource IN (:resources)))"),

But it is a chicken-and-egg problem.

You can't delete the config because of the constraint violation it would cause (resource has foreign keys to config)
You can't delete the resource first, because once you do, that QUERY_DELETE_CONFIG query's subquery results in 0 rows

SELECT rcu.configuration FROM ResourceConfigurationUpdate rcu WHERE rcu.resource IN (:resources)))

results in 0 rows, hence the IN() clause is empty and nothing is deleted from config

Right, the issue here is that the FK relationships to config are such that you can't remove the config prior to removing the object having that config.  The reason we're in good shape for a resource's resource and plugin config is that we still have hibernate cascade delete in place and we remove the resource via the entity manager, not bulk delete.  To do this at the code level would require one of the follwoing two strategies, neither being very pleasant:

1) bulk delete as we do today and then perform our own search for orphaned config.
This would be pretty inefficient as we'd have to search the 6 different relationships, none of which are indexed, I think.

2) prior to the bulk delete gather the config ids being referenced by the 6 FK's in play, and then delete config by id.
This is not fun as we don't want to have to fetch info just to perform deletes.

There may be other solutions but the best approach here may be to perform db-level cascade delete on the FK.  So, when the bulk delete takes out the referencing rows the db uses the FK relationship to kill the config. All under the covers.  This would be my recommendation at the moment.

Note that putting cascade delete on the config FKs for rhq_config_update and rhq_operation_history handles most of the issues.

Never mind, the cascade delete approach won't work for the same reason that makes this tricky, CD will not allow us to delete the parent in the FK relationship.  Just because, say, a referring row in rhq_config_update  is deleted, that won't trigger a cascade delete.  back to the drawing board.

Here's an idea that's related.  Still add the cascade deletes on the FKs.  So, for example, set it up such that rhq_config_update is deleted by the db if it's referenced rhq_configuration is deleted.  Then, change the bulk delete query for deleting rhq_config_update such that it actually deletes the related *config* as opposed to the config update.  The killing of the config would then cascade and kill the config_update.

So, for example, this query:

  PluginConfigurationUpdate.QUERY_DELETE_BY_RESOURCES, query = "DELETE FROM PluginConfigurationUpdate pcu WHERE pcu.resource IN (:resources))"

Would change to this query:

  PluginConfigurationUpdate.QUERY_DELETE_BY_RESOURCES, query = "DELETE FROM Configuration c WHERE c IN ( SELECT pcu.configuration WHERE pcu.resource IN (:resources))"

Even the previous suggestion isn't quite sufficient because of the complex graph in place.  For example, it's possible that Config C1 can be referenced by both Resource R1 and ConfigUpdate U1. And, at the same time R1 can be referenced by U1.

        R1 --> C1 
        U1 --> C1
        U1 --> R1

So, in an attempt to delete U1 by cascade delete of C1 you violate the constraint between R1 and C1.  If you leave U1 you violate the the constraint between U1 and R1 when you try to remove R1.  As far as I can tell, to get this to work you must perform two queries:

part 1 - remove all the configuration and, by cascade, associated plugin config updates with the exception of configurations in the scenario above. In other words, remove the configurations that would have been orphaned by the query in part 2.

   DELETE FROM Configuration c WHERE c IN ( SELECT pcu.configuration WHERE pcu.resource IN (:resources) AND NOT pcu.configuration = pcu.resource.pluginConfiguration )

part 2 - Now that we've protected against orphan configs, bulk delete remaining config updates (same as what we had originally)
   
   DELETE FROM PluginConfigurationUpdate pcu WHERE pcu.resource IN (:resources))

This needs to be done for pluginconfigupdate and resourceconfigupdate.  This is working locally.  It adds 2 more queries to bulk delete.

Note, just for fun, it seems that this still doesn't clean all the confis because, at least in the test code, config update is generating orphaned configs *during the update process*.  I have to see whether this happens outside of the test code as well.  In that case we've got a bug generating orphaned configs.

r2150 adds the db-support (schema 2.31) for the approach in the above comment for rhq_config_update.
r2151 adds the queries and related logic.

Assuming these changes work well we can revisit the above approach for the remaining orphaned configs.

This bug was previously known as http://jira.rhq-project.org/browse/RHQ-1128
This bug is related to RHQ-1125

Temporarily adding the keyword "SubBug" so we can be sure we have accounted for all the bugs.

keyword:
new = Tracking + FutureFeature + SubBug

making sure we're not missing any bugs in rhq_triage

jay -- what's left to do here?

Reading this through perhaps we need to revisit and see if the logic was applied anywhere other than rhq_config_update.

Given the list in the description we would need to check the following as well:

PackageInstallationStep
InstalledPackageHistory
ResourceOperationHistory
CreateResourceHistory
PluginConfigurationUpdate

As far as I know we still leave various config items. This should be revisited if and when we re-implement config storage design.