placement resource provider filtering does not work with postgres

[Chris Dent]

Telemetry tests with postgres found a bug in the sql used to filter resource providers that is breaking their gate:

http://logs.openstack.org/82/405682/8/check/gate-ceilometer-dsvm-tempest-plugin-postgresql-ubuntu-xenial/02f896f/logs/apache/placement-api.txt.gz?level=ERROR

The fix appears to be adding to the group_by on the usage join:

         usage = usage.group_by(_ALLOC_TBL.c.resource_provider_id,
- _ALLOC_TBL.c.resource_class_id)
+ _ALLOC_TBL.c.resource_class_id,
+ _ALLOC_TBL.c.consumer_id)

Not sure about the ordering.

(full log example below)

2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler [req-f0c425b6-bd71-44ae-ae33-46ce688d53dd service placement] Uncaught exception
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler Traceback (most recent call last):
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler File "/opt/stack/new/nova/nova/api/openstack/placement/handler.py", line 195, in __call__
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler return dispatch(environ, start_response, self._map)
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler File "/opt/stack/new/nova/nova/api/openstack/placement/handler.py", line 122, in dispatch
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler return handler(environ, start_response)
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler File "/usr/local/lib/python2.7/dist-packages/webob/dec.py", line 130, in __call__
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler resp = self.call_func(req, *args, **self.kwargs)
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler File "/usr/local/lib/python2.7/dist-packages/webob/dec.py", line 195, in call_func
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler return self.func(req, *args, **kwargs)
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler File "/opt/stack/new/nova/nova/api/openstack/placement/util.py", line 55, in decorated_function
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler return f(req)
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler File "/opt/stack/new/nova/nova/api/openstack/placement/handlers/resource_provider.py", line 305, in list_resource_providers
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler context, filters)
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler File "/opt/stack/new/nova/nova/objects/resource_provider.py", line 695, in get_all_by_filters
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler resource_providers = cls._get_all_by_filters_from_db(context, filters)
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler File "/usr/local/lib/python2.7/dist-packages/oslo_db/sqlalchemy/enginefacade.py", line 894, in wrapper
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler return fn(*args, **kwargs)
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler File "/opt/stack/new/nova/nova/objects/resource_provider.py", line 675, in _get_all_by_filters_from_db
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler return query.all()
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2613, in all
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler return list(self)
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2761, in __iter__
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler return self._execute_and_instances(context)
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2776, in _execute_and_instances
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler result = conn.execute(querycontext.statement, self._params)
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 914, in execute
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler return meth(self, multiparams, params)
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler return connection._execute_clauseelement(self, multiparams, params)
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler compiled_sql, distilled_params
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler context)
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1337, in _handle_dbapi_exception
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler util.raise_from_cause(newraise, exc_info)
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler reraise(type(exception), exception, tb=exc_tb, cause=cause)
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler context)
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 450, in do_execute
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler cursor.execute(statement, parameters)
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler DBError: (psycopg2.ProgrammingError) column "allocations.consumer_id" must appear in the GROUP BY clause or be used in an aggregate function
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler LINE 2: ...ons.resource_provider_id AS resource_provider_id, allocation...
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler ^
2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler [SQL: 'SELECT resource_providers.created_at AS resource_providers_created_at, resource_providers.updated_at AS resource_providers_updated_at, resource_providers.id AS resource_providers_id, resource_providers.uuid AS resource_providers_uuid, resource_providers.name AS resource_providers_name, resource_providers.generation AS resource_providers_generation, resource_providers.can_host AS resource_providers_can_host \\nFROM resource_providers JOIN inventories ON resource_providers.id = inventories.resource_provider_id LEFT OUTER JOIN (SELECT allocations.resource_provider_id AS resource_provider_id, allocations.consumer_id AS consumer_id, allocations.resource_class_id AS resource_class_id, sum(allocations.used) AS used \\nFROM allocations \\nWHERE allocations.resource_class_id IN (%(resource_class_id_1)s, %(resource_class_id_2)s) GROUP BY allocations.resource_provider_id, allocations.resource_class_id) AS usage ON usage.resource_provider_id = inventories.resource_provider_id AND usage.resource_class_id = inventories.resource_class_id \\nWHERE resource_providers.can_host = %(can_host_1)s AND (inventories.resource_class_id = %(resource_class_id_3)s AND coalesce(usage.used, %(param_1)s) + %(coalesce_1)s <= (inventories.total - inventories.reserved) * inventories.allocation_ratio AND inventories.min_unit <= %(min_unit_1)s AND inventories.max_unit >= %(max_unit_1)s AND %(step_size_1)s %% inventories.step_size = %(param_2)s OR inventories.resource_class_id = %(resource_class_id_4)s AND coalesce(usage.used, %(param_3)s) + %(coalesce_2)s <= (inventories.total - inventories.reserved) * inventories.allocation_ratio AND inventories.min_unit <= %(min_unit_2)s AND inventories.max_unit >= %(max_unit_2)s AND %(step_size_2)s %% inventories.step_size = %(param_4)s) GROUP BY resource_providers.uuid \\nHAVING count(DISTINCT inventories.resource_class_id) = %(count_1)s'] [parameters: {'coalesce_2': 64, 'step_size_1': 1, 'count_1': 2, 'coalesce_1': 1, 'param_4': 0, 'step_size_2': 64, 'param_1': 0, 'param_3': 0, 'param_2': 0, 'can_host_1': 0, 'max_unit_2': 64, 'max_unit_1': 1, 'resource_class_id_1': 0, 'resource_class_id_3': 0, 'resource_class_id_2': 1, 'min_unit_2': 64, 'resource_class_id_4': 1, 'min_unit_1': 1}]

[Chris Dent (cdent)]

Adding link to in progress code: https://review.openstack.org/#/c/427667/

The bug link in the commit wasn't in the first go, so didn't register.

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/427667
Committed: https://git.openstack.org/cgit/openstack/nova/commit/?id=03eced19f5d6665724a4fa432401be742383f8cf
Submitter: Jenkins
Branch: master

commit 03eced19f5d6665724a4fa432401be742383f8cf
Author: Mehdi Abaakouk <email address hidden>
Date: Wed Feb 1 13:31:38 2017 +0100

    placement-api: fix ResourceProviderList query

    The ResourceProviderList query use groupby without all grouped columns.
    This works on mysql with unpredicable result, but don't for other RDBMS.

    For example, postgresql gating jobs dsvm that use nova are currently
    broken.

    This change removes the unused consumer_id on first query,
    and uses the primary key 'id' instead of 'uuid' the second groupby.
    (Because groupby in postgresql requires a PK or all non-primary columns)

    The fix is tested by gate-ceilometer-dsvm-tempest-plugin-postgresql-ubuntu-xenial job
    here: https://review.openstack.org/#/c/427668/

    closes-bug: #1660959
    Change-Id: I6cc93ba0dd569d56696c9210d38dd2d77b4157c1

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/nova 15.0.0.0rc1 release candidate.