postgres incompatibility in InstanceGroup.get_hosts()

[Chris Friesen]

When running InstanceGroup.get_hosts() on a havana installation that uses postgres I get the following error:

RemoteError: Remote error: ProgrammingError (ProgrammingError) operator does not exist: timestamp without time zone ~ unknown
2014-03-14 09:58:57.193 8164 TRACE nova.compute.manager [instance: 83439206-3a88-495b-b6c7-6aea1287109f] LINE 3: ....uuid != instances.uuid AND (instances.deleted_at ~ 'None') ...
2014-03-14 09:58:57.193 8164 TRACE nova.compute.manager [instance: 83439206-3a88-495b-b6c7-6aea1287109f] ^
2014-03-14 09:58:57.193 8164 TRACE nova.compute.manager [instance: 83439206-3a88-495b-b6c7-6aea1287109f] HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

I'm not a database expert, but after doing some digging, it seems that the problem is this line in get_hosts():

filters = {'uuid': filter_uuids, 'deleted_at': None}

It seems that current postgres doesn't allow implicit casts. If I change the line to:

filters = {'uuid': filter_uuids, 'deleted': 0}

Then it seems to work.

[OpenStack Infra (hudson-openstack)]

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

[Russell Bryant (russellb)]

Since this isn't actually a regression, I'm going to move this to the icehouse-rc-potential list instead of listing it as a blocker for RC1.

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/82869
Committed: https://git.openstack.org/cgit/openstack/nova/commit/?id=0aecc86987794e3cb433723afbec744a11e1e720
Submitter: Jenkins
Branch: master

commit 0aecc86987794e3cb433723afbec744a11e1e720
Author: Chris Friesen <email address hidden>
Date: Tue Mar 25 08:29:58 2014 -0600

    Add missing test for None in sqlalchemy query filter

    In sqlalchemy a comparison against a non-null value will not match
    null values, so the existing code at the end of
    db.sqlalchemy.api.instance_get_all_by_filters() will not return
    instances where vm_state is NULL.

    This would be fine if the vm_state could never be null, but it
    is declared as "nullable" in the Instance object. In many cases
    "vm_state" will in fact have a value, but not all--in
    get_test_instance() in test/utils.py the value of "vm_state" is not
    specified. There may be other similar cases.

    Accordingly, this commit updates the test to explicitly check for None.

    Without this fix the unit tests for bug 1292963 will fail because
    the test instances have a vm_state of None.

    Closes-Bug: 1294756
    Related-Bug: 1292963
    Change-Id: I271cff22dec160fd0e76abadefd0fe06d32c3227

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/80808
Committed: https://git.openstack.org/cgit/openstack/nova/commit/?id=8538dd42d0c20e400e040a1a3349b07c6978c428
Submitter: Jenkins
Branch: master

commit 8538dd42d0c20e400e040a1a3349b07c6978c428
Author: Chris Friesen <email address hidden>
Date: Sat Mar 15 17:20:09 2014 -0600

    postgres incompatibility in InstanceGroup.get_hosts()

    The original code fails in postgresql with:

    "No operator matches the given name and argument type(s).
    You might need to add explicit type casts."

    It looks like the problem is that postgres doesn't do
    implicit casts, so I changed the code to check a different column
    that didn't require any.

    Change-Id: I9342ec2bfbdd991d5f7a238eca59af003c7e7eac
    Closes-bug: 1292963