OpenStack Compute (nova)

quota reservations fail w/ postgres (SELECT FOR UPDATE w/ a join)

Bug #1003756 reported by Dan Prince on 2012-05-24

This bug affects 1 person

Affects		Status	Importance	Assigned to	Milestone
	OpenStack Compute (nova)	Fix Released	High	Dan Prince	OpenStack Compute (nova) 2012.2 "folsom"

Bug Description

Using Nova Folsom. I'm seeing the following exceptions as of today when using the new quota reservations w/ PostgreSQL:

2012-05-24 02:04:20 ERROR nova.quota [req-f9dc9781-4312-41bd-8d1e-7a030534a504 d757be92fe4c42b095a1a8e670cef2ce 3de3af312bb149128129480b88ad3c9d] Failed to commit reservations ['7f713579-6156-43a8-a3ab-9afa03af725e', '8ccf952a-241d-4725-9f29-51c8c57dd5b9', '18935244-ef28-4436-b7c6-1bd1c835ba03']
2012-05-24 02:04:20 TRACE nova.quota Traceback (most recent call last):
2012-05-24 02:04:20 TRACE nova.quota File "/usr/lib/python2.7/site-packages/nova/quota.py", line 893, in commit
2012-05-24 02:04:20 TRACE nova.quota self._driver.commit(context, reservations)
2012-05-24 02:04:20 TRACE nova.quota File "/usr/lib/python2.7/site-packages/nova/quota.py", line 531, in commit
2012-05-24 02:04:20 TRACE nova.quota db.reservation_commit(context, reservations)
2012-05-24 02:04:20 TRACE nova.quota File "/usr/lib/python2.7/site-packages/nova/db/api.py", line 1012, in reservation_commit
2012-05-24 02:04:20 TRACE nova.quota return IMPL.reservation_commit(context, reservations)
2012-05-24 02:04:20 TRACE nova.quota File "/usr/lib/python2.7/site-packages/nova/db/sqlalchemy/api.py", line 129, in wrapper
2012-05-24 02:04:20 TRACE nova.quota return f(*args, **kwargs)
2012-05-24 02:04:20 TRACE nova.quota File "/usr/lib/python2.7/site-packages/nova/db/sqlalchemy/api.py", line 2679, in reservation_commit
2012-05-24 02:04:20 TRACE nova.quota for reservation in _quota_reservations(session, context, reservations):
2012-05-24 02:04:20 TRACE nova.quota File "/usr/lib/python2.7/site-packages/nova/db/sqlalchemy/api.py", line 2671, in _quota_reservations
2012-05-24 02:04:20 TRACE nova.quota with_lockmode('update').\
2012-05-24 02:04:20 TRACE nova.quota File "/usr/lib64/python2.7/site-packages/sqlalchemy/orm/query.py", line 2066, in all
2012-05-24 02:04:20 TRACE nova.quota return list(self)
2012-05-24 02:04:20 TRACE nova.quota File "/usr/lib64/python2.7/site-packages/sqlalchemy/orm/query.py", line 2176, in __iter__
2012-05-24 02:04:20 TRACE nova.quota return self._execute_and_instances(context)
2012-05-24 02:04:20 TRACE nova.quota File "/usr/lib64/python2.7/site-packages/sqlalchemy/orm/query.py", line 2191, in _execute_and_instances
2012-05-24 02:04:20 TRACE nova.quota result = conn.execute(querycontext.statement, self._params)
2012-05-24 02:04:20 TRACE nova.quota File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1450, in execute
2012-05-24 02:04:20 TRACE nova.quota params)
2012-05-24 02:04:20 TRACE nova.quota File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1583, in _execute_clauseelement
2012-05-24 02:04:20 TRACE nova.quota compiled_sql, distilled_params
2012-05-24 02:04:20 TRACE nova.quota File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1697, in _execute_context
2012-05-24 02:04:20 TRACE nova.quota context)
2012-05-24 02:04:20 TRACE nova.quota File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1690, in _execute_context
2012-05-24 02:04:20 TRACE nova.quota context)
2012-05-24 02:04:20 TRACE nova.quota File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/default.py", line 335, in do_execute
2012-05-24 02:04:20 TRACE nova.quota cursor.execute(statement, parameters)
2012-05-24 02:04:20 TRACE nova.quota NotSupportedError: (NotSupportedError) SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join
2012-05-24 02:04:20 TRACE nova.quota 'SELECT reservations.created_at AS reservations_created_at, reservations.updated_at AS reservations_updated_at, reservations.deleted_at AS reservations_deleted_at, reservations.deleted AS reservations_deleted, reservations.id AS reservations_id, reservations.uuid AS reservations_uuid, reservations.usage_id AS reservations_usage_id, reservations.project_id AS reservations_project_id, reservations.resource AS reservations_resource, reservations.delta AS reservations_delta, reservations.expire AS reservations_expire, quota_usages_1.created_at AS quota_usages_1_created_at, quota_usages_1.updated_at AS quota_usages_1_updated_at, quota_usages_1.deleted_at AS quota_usages_1_deleted_at, quota_usages_1.deleted AS quota_usages_1_deleted, quota_usages_1.id AS quota_usages_1_id, quota_usages_1.project_id AS quota_usages_1_project_id, quota_usages_1.resource AS quota_usages_1_resource, quota_usages_1.in_use AS quota_usages_1_in_use, quota_usages_1.reserved AS quota_usages_1_reserved, quota_usages_1.until_refresh AS quota_usages_1_until_refresh \nFROM reservations LEFT OUTER JOIN quota_usages AS quota_usages_1 ON reservations.usage_id = quota_usages_1.id AND reservations.deleted = %(deleted_1)s \nWHERE reservations.deleted = %(deleted_2)s AND reservations.uuid IN (%(uuid_1)s, %(uuid_2)s, %(uuid_3)s) FOR UPDATE' {'uuid_2': '8ccf952a-241d-4725-9f29-51c8c57dd5b9', 'uuid_3': '18935244-ef28-4436-b7c6-1bd1c835ba03', 'uuid_1': '7f713579-6156-43a8-a3ab-9afa03af725e', 'deleted_2': False, 'deleted_1': False}

--------------

PostgreSQL doesn't support SELECT FOR UPDATE when using a join like we do in _quota_reservations so we should implement this query in another manner.

Dan Prince (dan-prince) on 2012-05-24

Changed in nova:
assignee:	nobody → Dan Prince (dan-prince)
status:	New → In Progress
importance:	Undecided → High

Revision history for this message

OpenStack Infra (hudson-openstack) wrote on 2012-05-25: Fix proposed to nova (master)

Fix proposed to branch: master
Review: https://review.openstack.org/7801

Revision history for this message

OpenStack Infra (hudson-openstack) wrote on 2012-05-30: Fix merged to nova (master)

Reviewed: https://review.openstack.org/7801
Committed: http://github.com/openstack/nova/commit/d26ed3aee130646be0e042f29f3af64f1d73608e
Submitter: Jenkins
Branch: master

commit d26ed3aee130646be0e042f29f3af64f1d73608e
Author: Dan Prince <email address hidden>
Date: Fri May 25 12:25:23 2012 -0400

Fix reservation_commit so it works w/ PostgreSQL.

    Update the Reservation model so that the 'usage' relationship is
    explicitly forced to use an inner join. This fixes an issue on
    PostgreSQL which doesn't support 'SELECT FOR UPDATE' on outer
    joined queries.

Fixes LP Bug #1003756.

Change-Id: I3c40bd8481855a18391e12d7411762e91b1ef8b0