Allon Mureinik has submitted this change and it was merged. Change subject: core: storage_domain_shared_status SQL calculation ...................................................................... core: storage_domain_shared_status SQL calculation A storage domain's shared status was calculated by the fn_get_storage_domain_shared_status_by_domain_id function, which posed several performance issues, as it forced the switching between pgplsql and sql contexts several times, not to mention performing DML and even potentially DDL operations as part of the flow. Additionally, this function was phrased in a convoluted and hard to understand fashion, making it hard to maintain. The old logic, before this patch, was as follows: IF (domain.type != ISO) THEN IF (domain not attached to a pool) THEN RETURN Unattached ELSE IF (domain.status == Active) THEN RETURN Active ELSE RETURN Inactive ENDIF ELSE IF (num of distinct statuses == 0) THEN RETURN Unattached ELSE IF (num of distinct statuses == 1) THEN IF (domain.status == Active) THEN RETURN Active ELSE RETURN Inactive ENDIF ELSE IF (domain is active in at lest one pool) THEN RETURN Mixed ELSE RETURN Inactive END END END This logic can clearly be simplified. First and foremost, the special treatment for an ISO domain should be removed. Instead, a general treatment for a domain with multiple pools should be set in place, where ISO just happens to be the only type that supports this. The refactored logic should handle two variables: the number of distinct statuses a domain has and the number of active statuses it has: IF (num_distinct_statuses) == 0 THEN RETURN Unattached ELSE IF (num_active_statuses > 0) THEN IF (num_distinct_statuses > 1) THEN RETURN Mixed ELSE RETURN Active ELSE RETURN Inactive END This logic can easily be expressed as an SQL query which can be joined on and save the context switches associated with a pgplsql function. Change-Id: I9c1695a41e7a9ca3fb32cbbbfc8fc8e4ae80f6ef Signed-off-by: Allon Mureinik <amureini@redhat.com> --- M backend/manager/modules/dal/src/test/java/org/ovirt/engine/core/dao/StorageDomainDaoTest.java M packaging/dbscripts/create_dwh_views.sql M packaging/dbscripts/create_functions.sql M packaging/dbscripts/create_views.sql 4 files changed, 33 insertions(+), 81 deletions(-) Approvals: Eli Mesika: Looks good to me, approved Allon Mureinik: Verified; Passed CI tests Idan Shaby: Looks good to me, but someone else must approve -- To view, visit https://gerrit.ovirt.org/53687 To unsubscribe, visit https://gerrit.ovirt.org/settings Gerrit-MessageType: merged Gerrit-Change-Id: I9c1695a41e7a9ca3fb32cbbbfc8fc8e4ae80f6ef Gerrit-PatchSet: 6 Gerrit-Project: ovirt-engine Gerrit-Branch: master Gerrit-Owner: Allon Mureinik <amureini@redhat.com> Gerrit-Reviewer: Allon Mureinik <amureini@redhat.com> Gerrit-Reviewer: Daniel Erez <derez@redhat.com> Gerrit-Reviewer: Eli Mesika <emesika@redhat.com> Gerrit-Reviewer: Idan Shaby <ishaby@redhat.com> Gerrit-Reviewer: Jenkins CI Gerrit-Reviewer: Shirly Radco <sradco@redhat.com> Gerrit-Reviewer: Tal Nisan <tnisan@redhat.com> Gerrit-Reviewer: Yaniv Dary <ydary@redhat.com> Gerrit-Reviewer: gerrit-hooks <automation@ovirt.org>