yahoo-eng-team team mailing list archive

Thread
Date
[Bug 1660959] [NEW] placement resource provider filtering does not work with postgres

To: yahoo-eng-team@xxxxxxxxxxxxxxxxxxx
From: Chris Dent <cdent+lp@xxxxxxxxxxxxx>
Date: Wed, 01 Feb 2017 12:36:21 -0000
Reply-to: Bug 1660959 <1660959@xxxxxxxxxxxxxxxxxx>
Sender: bounces@xxxxxxxxxxxxx
Public bug reported:

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}]

** Affects: nova
     Importance: Undecided
         Status: Triaged


** Tags: placement scheduler

-- 
You received this bug notification because you are a member of Yahoo!
Engineering Team, which is subscribed to OpenStack Compute (nova).
https://bugs.launchpad.net/bugs/1660959

Title:
  placement resource provider filtering does not work with postgres

Status in OpenStack Compute (nova):
  Triaged

Bug description:
  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}]

To manage notifications about this bug go to:
https://bugs.launchpad.net/nova/+bug/1660959/+subscriptions
Follow ups

[Bug 1660959] Re: placement resource provider filtering does not work with postgres
From: OpenStack Infra, 2017-02-02