Critical DB Error due to constraints in PostGreSQL Join-handing.

[mmnelemane]

"aimctl db-migration upgrade head" results in the following error when used against a postgresql backend. This is due to the inherent way postgres handles left outer joins.

The reason for this behavior from postgres is explained in the mailing list here: https://www.postgresql.org/message-id/10878.1131978292%40sss.pgh.pa.us

CRITI [aim] DBError: (psycopg2.NotSupportedError) FOR UPDATE cannot be applied to the nullable side of an outer join
[SQL: 'SELECT aim_tenant_trees.tenant_rn AS aim_tenant_trees_tenant_rn, aim_tenant_trees.epoch AS aim_tenant_trees_epoch, aim_tenant_trees.needs_reset AS aim_tenant_trees_needs_reset, aim_agent_to_tree_associations_1.tree_tenant_rn AS aim_agent_to_tree_associations_1_tree_tenant_rn, aim_agent_to_tree_associations_1.agent_id AS aim_agent_to_tree_associations_1_agent_id \nFROM aim_tenant_trees LEFT OUTER JOIN aim_agent_to_tree_associations AS aim_agent_to_tree_associations_1 ON aim_tenant_trees.tenant_rn = aim_agent_to_tree_associations_1.tree_tenant_rn FOR UPDATE']
Traceback (most recent call last):
File "/usr/bin/aimctl", line 10, in
sys.exit(aimctl())
File "/usr/lib/python2.7/site-packages/aim/tools/services/cli.py", line 21, in aimctl
shell.run()
File "/usr/lib/python2.7/site-packages/aim/tools/cli/shell.py", line 27, in run
aim(auto_envvar_prefix='AIM')
File "/usr/lib/python2.7/site-packages/click/core.py", line 716, in call
return self.main(*args, **kwargs)
File "/usr/lib/python2.7/site-packages/click/core.py", line 696, in main
rv = self.invoke(ctx)
File "/usr/lib/python2.7/site-packages/click/core.py", line 1060, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/usr/lib/python2.7/site-packages/click/core.py", line 1060, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/usr/lib/python2.7/site-packages/click/core.py", line 889, in invoke
return ctx.invoke(self.callback, **ctx.params)
File "/usr/lib/python2.7/site-packages/click/core.py", line 534, in invoke
return callback(*args, **kwargs)
File "/usr/lib/python2.7/site-packages/click/decorators.py", line 17, in new_func
return f(get_current_context(), *args, **kwargs)
File "/usr/lib/python2.7/site-packages/aim/tools/cli/commands/db_migration.py", line 75, in upgrade
_reset(aim_mgr)
File "/usr/lib/python2.7/site-packages/aim/common/utils.py", line 182, in inner
raise e
DBError: (psycopg2.NotSupportedError) FOR UPDATE cannot be applied to the nullable side of an outer join
[SQL: 'SELECT aim_tenant_trees.tenant_rn AS aim_tenant_trees_tenant_rn, aim_tenant_trees.epoch AS aim_tenant_trees_epoch, aim_tenant_trees.needs_reset AS aim_tenant_trees_needs_reset, aim_agent_to_tree_associations_1.tree_tenant_rn AS aim_agent_to_tree_associations_1_tree_tenant_rn, aim_agent_to_tree_associations_1.agent_id AS aim_agent_to_tree_associations_1_agent_id \nFROM aim_tenant_trees LEFT OUTER JOIN aim_agent_to_tree_associations AS aim_agent_to_tree_associations_1 ON aim_tenant_trees.tenant_rn = aim_agent_to_tree_associations_1.tree_tenant_rn FOR UPDATE']