Oved Ourfali has uploaded a new change for review. Change subject: setup: checking if command_entities table exist ......................................................................
setup: checking if command_entities table exist oVirt 3.5 has introduced command_entities table. In addition to adding some new functions related to this table to the task clenaer utility, some functions got changed, but as it is run during setup and might run on top of version which preceeds oVirt 3.5, it should be checked in the relevant SPs that the table exists. Bug-Url: https://bugzilla.redhat.com/1161012 Bug-Url: https://bugzilla.redhat.com/1164771 Change-Id: I3152c4d3f6e02915053ada5019abeea7cb356751 Signed-off-by: Yair Zaslavsky <yzasl...@redhat.com> (cherry picked from commit 3ba84df3d9e6597e131c3c9882e6d7136a54e296) --- M packaging/setup/dbutils/taskcleaner.sh M packaging/setup/dbutils/taskcleaner_sp.sql A packaging/setup/dbutils/taskcleaner_sp_3_5.sql 3 files changed, 107 insertions(+), 81 deletions(-) git pull ssh://gerrit.ovirt.org:29418/ovirt-engine refs/changes/57/38157/1 diff --git a/packaging/setup/dbutils/taskcleaner.sh b/packaging/setup/dbutils/taskcleaner.sh index 3494c36..4d7522c 100755 --- a/packaging/setup/dbutils/taskcleaner.sh +++ b/packaging/setup/dbutils/taskcleaner.sh @@ -121,9 +121,9 @@ dbfunc_psql_die --command="select exists (select * from information_schema.tables where table_schema = 'public' and table_name = 'command_entities');" | grep "t" VERSION=$? if [[ $VERSION = 0 ]]; then - VERSION="3.5" + VERSION="post3.5" else - VERSION="3.4" + VERSION="pre3.5" fi if [ "${TASK_ID}" != "" -o "${COMMAND_ID}" != "" -o -n "${CLEAR_ALL}" -o -n "${CLEAR_COMPENSATION}" -o -n "${CLEAR_JOB_STEPS}" ]; then #delete operations block @@ -257,19 +257,19 @@ elif [ -n "${ZOMBIES_ONLY}" ]; then #only display operations block CMD1="SELECT ${TASKS_FIELDS} FROM GetAsyncTasksZombies();" elif [ -n "${ALL_COMMANDS}" ]; then #only display commands - if [[ $VERSION = "3.5" ]]; then + if [[ $VERSION = "post3.5" ]]; then CMD1="SELECT ${COMMANDS_FIELDS} FROM GetAllCommands();" else die "This option is available only from version 3.5" fi elif [ -n "${COMMANDS_WITH_RUNNING_TASKS_ONLY}" ]; then - if [[ $VERSION = "3.5" ]]; then + if [[ $VERSION = "post3.5" ]]; then CMD1="SELECT ${COMMANDS_FIELDS} FROM GetAllCommandsWithRunningTasks();" else die "This option is available only from version 3.5" fi elif [ -n "${CLEAR_COMMANDS}" ]; then - if [[ $VERSION = "3.5" ]]; then + if [[ $VERSION = "post3.5" ]]; then if [ -n "${COMMANDS_WITH_RUNNING_TASKS_ONLY}" ]; then CMD1="SELECT DeleteAllCommandsWithRunningTasks();" elif [ -n "${ZOMBIE_COMMANDS_ONLY}" ]; then @@ -281,7 +281,7 @@ die "This option is available only from version 3.5" fi elif [ -n "${ZOMBIE_COMMANDS_ONLY}" ]; then - if [[ $VERSION = "3.5" ]]; then + if [[ $VERSION = "post3.5" ]]; then CMD1="SELECT ${COMMANDS_FIELDS} FROM GetAllCommandsWithZombieTasks();" else die "This option is available only from version 3.5" @@ -292,6 +292,10 @@ # Install taskcleaner procedures dbfunc_psql_die --file="$(dirname "$0")/taskcleaner_sp.sql" > /dev/null +if [[ $VERSION = "post3.5" ]]; then + dbfunc_psql_die --file="$(dirname "$0")/taskcleaner_sp_3_5.sql" > /dev/null +fi # Execute dbfunc_psql_die --command="${CMD1}${CMD2}" + diff --git a/packaging/setup/dbutils/taskcleaner_sp.sql b/packaging/setup/dbutils/taskcleaner_sp.sql index f958bc7..779930e 100644 --- a/packaging/setup/dbutils/taskcleaner_sp.sql +++ b/packaging/setup/dbutils/taskcleaner_sp.sql @@ -31,15 +31,29 @@ AS $procedure$ DECLARE deleted_rows int; +root_command_id_of_deleted_cmds UUID; BEGIN - IF EXISTS (SELECT 1 FROM GetAsyncTasksZombies() WHERE command_id = v_command_id) THEN - DELETE FROM async_tasks WHERE command_id = v_command_id; + IF (fn_db_is_table_exists ('command_entities')) THEN + DELETE FROM command_entities c WHERE + c.command_id IN ( + SELECT command_id FROM GetAsyncTasksZombies() t WHERE t.command_id = v_command_id + ); + + DELETE FROM command_entities c WHERE + c.command_id IN ( + SELECT root_command_id FROM GetAsyncTasksZombies() t WHERE t.root_command_id = v_command_id + ); END IF; - DELETE FROM command_entities where command_id = v_command_id; - GET DIAGNOSTICS deleted_rows = ROW_COUNT; - IF deleted_rows > 0 THEN - DELETE FROM command_entities C WHERE command_id = root_command_id_of_deleted_cmds AND NOT EXISTS (SELECT * from COMMAND_ENTITIES WHERE root_command_id = C.command_id); - END IF; + + DELETE FROM async_tasks WHERE + root_command_id IN ( + SELECT root_command_id FROM GetAsyncTasksZombies() t WHERE t.root_command_id = v_command_id + ); + + DELETE FROM async_tasks WHERE + root_command_id IN ( + SELECT root_command_id FROM GetAsyncTasksZombies() t WHERE t.command_id = v_command_id + ); END; $procedure$ LANGUAGE plpgsql; @@ -50,11 +64,13 @@ root_command_id_of_deleted_cmds UUID; BEGIN DELETE FROM async_tasks WHERE command_id = v_command_id; - SELECT root_command_id into root_command_id_of_deleted_cmds FROM COMMAND_entities WHERE command_id = v_command_id; - DELETE FROM command_entities where command_id = v_command_id; - GET DIAGNOSTICS deleted_rows = ROW_COUNT; - IF deleted_rows > 0 THEN + IF (fn_db_is_table_exists ('command_entities')) THEN + SELECT root_command_id into root_command_id_of_deleted_cmds FROM COMMAND_entities WHERE command_id = v_command_id; + DELETE FROM command_entities where command_id = v_command_id; + GET DIAGNOSTICS deleted_rows = ROW_COUNT; + IF deleted_rows > 0 THEN DELETE FROM command_entities C WHERE command_id = root_command_id_of_deleted_cmds AND NOT EXISTS (SELECT * from COMMAND_ENTITIES WHERE root_command_id = C.command_id); + END IF; END IF; END; $procedure$ LANGUAGE plpgsql; @@ -135,67 +151,3 @@ END; $procedure$ LANGUAGE plpgsql; -Create or replace FUNCTION GetAllCommandsWithRunningTasks() RETURNS SETOF COMMAND_ENTITIES STABLE - AS $procedure$ -BEGIN - RETURN QUERY SELECT * - FROM COMMAND_ENTITIES C - WHERE EXISTS (SELECT * FROM ASYNC_TASKS A WHERE A.COMMAND_ID = C.COMMAND_ID); -END; $procedure$ -LANGUAGE plpgsql; - -Create or replace FUNCTION GetAllCommands() -RETURNS SETOF COMMAND_ENTITIES STABLE - AS $procedure$ -BEGIN - RETURN QUERY SELECT * - FROM COMMAND_ENTITIES; -END; $procedure$ -LANGUAGE plpgsql; - -Create or replace FUNCTION GetAllCommandsWithZombieTasks() -RETURNS SETOF COMMAND_ENTITIES STABLE - AS $procedure$ -BEGIN - RETURN QUERY SELECT * - FROM COMMAND_ENTITIES C - WHERE C.COMMAND_ID in (SELECT COMMAND_ID from GetAsyncTasksZombies()); -END; $procedure$ -LANGUAGE plpgsql; - -Create or replace FUNCTION DeleteAllCommands() -RETURNS integer - AS $procedure$ -DECLARE -deleted_rows int; -BEGIN - DELETE FROM COMMAND_ENTITIES; - GET DIAGNOSTICS deleted_rows = ROW_COUNT; - RETURN deleted_rows; - -END; $procedure$ -LANGUAGE plpgsql; - -Create or replace FUNCTION DeleteAllCommandsWithZombieTasks() -RETURNS integer - AS $procedure$ -DECLARE -deleted_rows int; -BEGIN - DELETE FROM COMMAND_ENTITIES C - WHERE C.COMMAND_ID in (SELECT COMMAND_ID from GetAsyncTasksZombies()); - GET DIAGNOSTICS deleted_rows = ROW_COUNT; - RETURN deleted_rows; - -END; $procedure$ -LANGUAGE plpgsql; - -Create or replace FUNCTION DeleteAllCommandsWithRunningTasks() -RETURNS integer - AS $procedure$ -DECLARE -deleted_rows int; -BEGIN - DELETE FROM COMMAND_ENTITIES C WHERE C.COMMAND_ID in (SELECT * FROM ASYNC_TASKS A WHERE A.COMMAND_ID = C.COMMAND_ID); -END; $procedure$ -LANGUAGE plpgsql; diff --git a/packaging/setup/dbutils/taskcleaner_sp_3_5.sql b/packaging/setup/dbutils/taskcleaner_sp_3_5.sql new file mode 100644 index 0000000..7d25792 --- /dev/null +++ b/packaging/setup/dbutils/taskcleaner_sp_3_5.sql @@ -0,0 +1,70 @@ +/************************************************************************************************ + The following are helper SP for taskcleaner utility and are not exposed to the application DAOs +/This script deals with command_entities related SP +************************************************************************************************/ + + +Create or replace FUNCTION GetAllCommandsWithRunningTasks() RETURNS SETOF COMMAND_ENTITIES STABLE + AS $procedure$ +BEGIN + RETURN QUERY SELECT * + FROM COMMAND_ENTITIES C + WHERE EXISTS (SELECT * FROM ASYNC_TASKS A WHERE A.COMMAND_ID = C.COMMAND_ID); +END; $procedure$ +LANGUAGE plpgsql; + +Create or replace FUNCTION GetAllCommands() +RETURNS SETOF COMMAND_ENTITIES STABLE + AS $procedure$ +BEGIN + RETURN QUERY SELECT * + FROM COMMAND_ENTITIES; +END; $procedure$ +LANGUAGE plpgsql; + +Create or replace FUNCTION GetAllCommandsWithZombieTasks() +RETURNS SETOF COMMAND_ENTITIES STABLE + AS $procedure$ +BEGIN + RETURN QUERY SELECT * + FROM COMMAND_ENTITIES C + WHERE C.COMMAND_ID in (SELECT COMMAND_ID from GetAsyncTasksZombies()); +END; $procedure$ +LANGUAGE plpgsql; + +Create or replace FUNCTION DeleteAllCommands() +RETURNS integer + AS $procedure$ +DECLARE +deleted_rows int; +BEGIN + DELETE FROM COMMAND_ENTITIES; + GET DIAGNOSTICS deleted_rows = ROW_COUNT; + RETURN deleted_rows; + +END; $procedure$ +LANGUAGE plpgsql; + +Create or replace FUNCTION DeleteAllCommandsWithZombieTasks() +RETURNS integer + AS $procedure$ +DECLARE +deleted_rows int; +BEGIN + DELETE FROM COMMAND_ENTITIES C + WHERE C.COMMAND_ID in (SELECT COMMAND_ID from GetAsyncTasksZombies()); + GET DIAGNOSTICS deleted_rows = ROW_COUNT; + RETURN deleted_rows; + +END; $procedure$ +LANGUAGE plpgsql; + +Create or replace FUNCTION DeleteAllCommandsWithRunningTasks() +RETURNS integer + AS $procedure$ +DECLARE +deleted_rows int; +BEGIN + DELETE FROM COMMAND_ENTITIES C WHERE C.COMMAND_ID in (SELECT * FROM ASYNC_TASKS A WHERE A.COMMAND_ID = C.COMMAND_ID); +END; $procedure$ +LANGUAGE plpgsql; -- To view, visit https://gerrit.ovirt.org/38157 To unsubscribe, visit https://gerrit.ovirt.org/settings Gerrit-MessageType: newchange Gerrit-Change-Id: I3152c4d3f6e02915053ada5019abeea7cb356751 Gerrit-PatchSet: 1 Gerrit-Project: ovirt-engine Gerrit-Branch: ovirt-engine-3.5.2 Gerrit-Owner: Oved Ourfali <oourf...@redhat.com> Gerrit-Reviewer: Yair Zaslavsky <wallaroo1...@gmail.com> _______________________________________________ Engine-patches mailing list Engine-patches@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-patches
