Eli Mesika has uploaded a new change for review. Change subject: core:Add a script that displays async ...(#868672) ......................................................................
core:Add a script that displays async ...(#868672) Adding a fn_db_get_async_tasks function to the database. Displays DC id , DC name, SPM Host id , SPM Host name and number of async tasks awaiting. Example: ovirt=# select * from fn_db_get_async_tasks(); -[ RECORD 1 ]-+------------------------------------- dc_id | 64600e6a-ef8b-11e1-838c-0fc062f03f3a dc_name | Default spm_host_id | b021eada-ef8b-11e1-8e97-579ccb995b3c spm_host_name | pluto-vdsa task_count | 2 The functions does the following steps to gather the required information: 1) create a temporary table with DC name, DC id, SPM host id, SPM host name and tasks count 2) get all distinct DC ids from the parameters Json representation 3) Run a cursor for each result in 2) a) get DC name b) get SPM Host id & name if available c) get count of async tasks insert to temp table in 1) 4) return select * from the temporary table regarding the required compensation information: use the following query: select command_type, entity type from business_entity_snapshot please note: command_type is a fully qualified name , consider to take only its suffix. entity type is a string identifying the entity (like vm_static etc.) Change-Id: Ia9f021279d761344c3d9fb3bedef80637d5a7855 Signed-off-by: Eli Mesika <emes...@redhat.com> Bug-Url:https://bugzilla.redhat.com/868672 Signed-off-by: Eli Mesika <emes...@redhat.com> --- M backend/manager/dbscripts/common_sp.sql 1 file changed, 76 insertions(+), 2 deletions(-) git pull ssh://gerrit.ovirt.org:29418/ovirt-engine refs/changes/36/8736/1 diff --git a/backend/manager/dbscripts/common_sp.sql b/backend/manager/dbscripts/common_sp.sql index 454d887..f486736 100644 --- a/backend/manager/dbscripts/common_sp.sql +++ b/backend/manager/dbscripts/common_sp.sql @@ -149,7 +149,7 @@ -- or this could be in a config table -- NOTE: We should look at checking error codes as not all are suitable for exceptions some are just notice or info -declare +declare result boolean := false; prop text; begin @@ -161,7 +161,7 @@ return result; exception when others then -result = true; -- default to log if not specified +result = true; -- default to log if not specified return result; end; $BODY$ @@ -400,3 +400,77 @@ end if; END; $procedure$ LANGUAGE plpgsql; + +/* Displays DC id , DC name, SPM Host id , SPM Host name and number of async tasks awaiting. + +1) create a temporary table with DC name, DC id, SPM host id, SPM host name, count + +2) get all distinct DC ids from the parameters Json representation + +3) Run a cursor for each result in 2) + + a) get DC name + b) get SPM Host id & name if available + c) get count of tasks + + insert to temp table in 1) + +4) return select * from the temporray table +*/ + +DROP TYPE IF EXISTS async_tasks_info_rs CASCADE; +CREATE TYPE async_tasks_info_rs AS ( + dc_id UUID, dc_name CHARACTER VARYING, spm_host_id UUID, spm_host_name CHARACTER VARYING, task_count integer); + + +create or replace FUNCTION fn_db_get_async_tasks() +returns SETOF async_tasks_info_rs +AS $procedure$ +DECLARE + v_dc_id UUID; + v_dc_name CHARACTER VARYING; + v_spm_host_id UUID; + v_spm_host_name CHARACTER VARYING; + v_count integer; + + -- selects all UUID values after the storage_pool_id and uuid found in serialized parameters + v_tasks_cursor cursor for select distinct + substring(--storage_pool_id + substring(--uuid + substring (action_parameters from 'storage_pool_id.*')--uuid value + ,'uuid.*') + ,'........-....-....-....-............') from async_tasks; +begin + + DROP TABLE IF EXISTS tt_async_tasks_info; + CREATE GLOBAL TEMPORARY TABLE tt_async_tasks_info( + dc_id UUID, + dc_name CHARACTER VARYING, + spm_host_id UUID, + spm_host_name CHARACTER VARYING, + task_count integer); + + OPEN v_tasks_cursor; + FETCH v_tasks_cursor into v_dc_id; + WHILE FOUND LOOP + -- get dc_name and SPM Host id + v_dc_name := name from storage_pool where id = v_dc_id; + v_spm_host_id := spm_vds_id from storage_pool where id = v_dc_id; + -- get Host name if we have non NULL SPM Host + if (v_spm_host_id IS NOT NULL) then + v_spm_host_name := vds_name from vds_static where vds_id = v_spm_host_id; + else + v_spm_host_name:=''; + end if; + -- get tasks count for this DC + v_count := count(*) from async_tasks where position (cast(v_dc_id as varchar) in action_parameters) > 0; + -- insert a record to the temporary table + insert into tt_async_tasks_info(dc_id,dc_name,spm_host_id,spm_host_name,task_count) + values(v_dc_id,v_dc_name,v_spm_host_id,v_spm_host_name,v_count); + FETCH v_tasks_cursor into v_dc_id; + END LOOP; + CLOSE v_tasks_cursor; + RETURN QUERY select * from tt_async_tasks_info; +END; $procedure$ +LANGUAGE plpgsql; + -- To view, visit http://gerrit.ovirt.org/8736 To unsubscribe, visit http://gerrit.ovirt.org/settings Gerrit-MessageType: newchange Gerrit-Change-Id: Ia9f021279d761344c3d9fb3bedef80637d5a7855 Gerrit-PatchSet: 1 Gerrit-Project: ovirt-engine Gerrit-Branch: master Gerrit-Owner: Eli Mesika <emes...@redhat.com> _______________________________________________ Engine-patches mailing list Engine-patches@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-patches
