Eli Mesika has uploaded a new change for review.
Change subject: core: Custom Materialized Views should be...
......................................................................
core: Custom Materialized Views should be...
Custom Materialized Views should be treated differently from
regular product Materialized Views
This patch addresses the following issues:
1) Do not fail the upgrade on custom MV file, rather, revert the custom
changes and generate only warnings
(need support from integration team)
2) Add custom column to MV table that will easily distinguish between
product MV and custom MV.
3) Add active column to temporary disable a MV
4) Refresh options:
Defining min refresh flood time in seconds (in the same
manner log flood is handled)
Change-Id: I6a6857a1691c1dafa1a7edcd58b292e70c78b318
Signed-off-by: Eli Mesika <[email protected]>
Bug-Url: https://bugzilla.redhat.com/show_bug.cgi?id=907232
---
M backend/manager/dbscripts/dbfunctions.sh
M backend/manager/dbscripts/materialized_views_sp.sql
A backend/manager/dbscripts/upgrade/03_02_0400_materialized_views_extensions.sql
3 files changed, 108 insertions(+), 9 deletions(-)
git pull ssh://gerrit.ovirt.org:29418/ovirt-engine refs/changes/69/11669/1
diff --git a/backend/manager/dbscripts/dbfunctions.sh
b/backend/manager/dbscripts/dbfunctions.sh
index f09ef7a..0f210cb 100755
--- a/backend/manager/dbscripts/dbfunctions.sh
+++ b/backend/manager/dbscripts/dbfunctions.sh
@@ -157,7 +157,12 @@
custom_materialized_views_file="upgrade/post_upgrade/custom/create_materialized_views.sql"
if [ -f ${custom_materialized_views_file} ]; then
echo "running custom materialized views from
${custom_materialized_views_file} ..."
- execute_file ${custom_materialized_views_file} ${DATABASE}
${SERVERNAME} ${PORT} > /dev/null
+ psql -U ${USERNAME} --pset=tuples_only=on --set ON_ERROR_STOP=1 -h
${SERVERNAME} -p ${PORT} -f "${custom_materialized_views_file}" ${DATABASE} >
/dev/null
+ if [ $? -ne 0 ] ; then
+ #drop all custom views
+ psql -U ${USERNAME} -h ${SERVERNAME} -p ${PORT} -c "select
DropAllCustomMaterializedViews();" ${DATABASE} > /dev/null
+ echo "Illegal syntax in custom Materialized Views, Custom
Materialized Views were dropped."
+ fi
fi
refresh_materialized_views
diff --git a/backend/manager/dbscripts/materialized_views_sp.sql
b/backend/manager/dbscripts/materialized_views_sp.sql
index ccb926b..832971d 100644
--- a/backend/manager/dbscripts/materialized_views_sp.sql
+++ b/backend/manager/dbscripts/materialized_views_sp.sql
@@ -57,7 +57,8 @@
$$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
-- CreateMaterializedViewAsCreates a new Materialized View
-CREATE OR REPLACE FUNCTION CreateMaterializedView(v_matview NAME, v_view_name
NAME, v_refresh_rate_in_sec INTEGER)
+CREATE OR REPLACE FUNCTION CreateMaterializedView(v_matview NAME, v_view_name
NAME, v_refresh_rate_in_sec INTEGER,
+ v_custom BOOLEAN,
v_min_refresh_rate_in_sec INTEGER)
RETURNS VOID
AS $procedure$
DECLARE
@@ -80,18 +81,28 @@
EXECUTE 'GRANT SELECT ON ' || v_matview || ' TO PUBLIC';
- INSERT INTO materialized_views (mv_name, v_name, refresh_rate_in_sec,
last_refresh)
- VALUES (v_matview, v_view_name, v_refresh_rate_in_sec,
CURRENT_TIMESTAMP);
+ INSERT INTO materialized_views (mv_name, v_name, refresh_rate_in_sec,
last_refresh, custom, min_refresh_rate_in_sec)
+ VALUES (v_matview, v_view_name, v_refresh_rate_in_sec,
CURRENT_TIMESTAMP, v_custom, v_min_refresh_rate_in_sec);
RETURN;
END; $procedure$
LANGUAGE plpgsql;
+CREATE OR REPLACE FUNCTION CreateMaterializedView(v_matview NAME, v_view_name
NAME, v_refresh_rate_in_sec INTEGER)
+ RETURNS VOID
+AS $procedure$
+BEGIN
+ PERFORM CreateMaterializedView(v_matview, v_view_name,
v_refresh_rate_in_sec, false, 0);
+END; $procedure$
+ LANGUAGE plpgsql;
+
+
-- Enables to create a New materialized view with a name of existing view
-- This is done in order to solve cases where we are forced to use the old
view name for the new createed
-- Materialized View because it is used from dynamic SQL and we have to send
only a DB patch without forcing
-- recompilation of engine code
-CREATE OR REPLACE FUNCTION CreateMaterializedViewAs(v_view_name NAME,
v_refresh_rate_in_sec INTEGER)
+CREATE OR REPLACE FUNCTION CreateMaterializedViewAs(v_view_name NAME,
v_refresh_rate_in_sec INTEGER,
+ v_custom BOOLEAN,
v_min_refesh_rate_in_sec INTEGER)
RETURNS VOID
AS $procedure$
DECLARE
@@ -105,10 +116,18 @@
v_renamed_view := v_view_name || '_mt_base';
EXECUTE 'ALTER VIEW ' || v_view_name || ' RENAME TO ' || v_renamed_view;
- perform CreateMaterializedView(v_view_name, v_renamed_view,
v_refresh_rate_in_sec);
+ perform CreateMaterializedView(v_view_name, v_renamed_view,
v_refresh_rate_in_sec, v_custom, v_min_refesh_rate_in_sec);
RETURN;
END; $procedure$
+ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION CreateMaterializedViewAs(v_view_name NAME,
v_refresh_rate_in_sec INTEGER)
+ RETURNS VOID
+AS $procedure$
+BEGIN
+ PERFORM CreateMaterializedViewAs(v_view_name, v_refresh_rate_in_sec,
false, 0);
+END; $procedure$
LANGUAGE plpgsql;
-- Drops a Materialized View
@@ -132,7 +151,7 @@
END; $procedure$
LANGUAGE plpgsql;
--- Drops a Materialized Views
+-- Drops all Materialized Views
CREATE OR REPLACE FUNCTION DropAllMaterializedViews()
RETURNS void
AS $procedure$
@@ -151,6 +170,26 @@
END; $procedure$
LANGUAGE plpgsql;
+-- Drops all custom Materialized Views
+CREATE OR REPLACE FUNCTION DropAllCustomMaterializedViews()
+RETURNS void
+AS $procedure$
+DECLARE
+ v_cur CURSOR FOR SELECT * FROM materialized_views where custom;
+ v_record materialized_views%ROWTYPE;
+BEGIN
+ OPEN v_cur;
+ -- loop on all entries in materialized_views
+ LOOP
+ FETCH v_cur INTO v_record;
+ EXIT WHEN NOT FOUND;
+ perform DropMaterializedView(v_record.mv_name);
+ END LOOP;
+ CLOSE v_cur;
+END; $procedure$
+LANGUAGE plpgsql;
+
+
-- Checks if Materialized View should be refreshed
CREATE OR REPLACE FUNCTION IsMaterializedViewRefreshed(v_matview NAME)
RETURNS boolean
@@ -165,7 +204,9 @@
END IF;
-- check if materialized View should refresh
- v_is_refreshed := (CURRENT_TIMESTAMP - to_interval(refresh_rate_in_sec))
<= last_refresh from materialized_views
+ v_is_refreshed := (((CURRENT_TIMESTAMP - to_interval(refresh_rate_in_sec))
<= last_refresh) and
+ ((CURRENT_TIMESTAMP -
to_interval(min_refresh_rate_in_sec)) <= last_refresh)) or
+ not active from materialized_views
where mv_name = v_matview;
RETURN v_is_refreshed;
END; $procedure$
@@ -243,7 +284,7 @@
LOOP
FETCH v_cur INTO v_record;
EXIT WHEN NOT FOUND;
- IF (v_force or not IsMaterializedViewRefreshed(v_record.mv_name))
THEN
+ IF ((v_force and v_record.min_refresh_rate_in_sec = 0) or not
IsMaterializedViewRefreshed(v_record.mv_name)) THEN
perform RefreshMaterializedView(v_record.mv_name);
END IF;
END LOOP;
@@ -296,3 +337,49 @@
END; $procedure$
LANGUAGE plpgsql;
+-- Updates a Materialized View min refresh rate
+CREATE OR REPLACE FUNCTION UpdateMaterializedViewMinRefreshRate(v_matview
NAME, v_min_refresh_rate INTEGER)
+ RETURNS VOID
+AS $procedure$
+DECLARE
+ v_entry materialized_views%ROWTYPE;
+ BEGIN
+ SELECT * INTO v_entry FROM materialized_views WHERE mv_name = v_matview;
+ IF NOT FOUND THEN
+ RAISE EXCEPTION 'Materialized view % does not exist.', v_matview;
+ END IF;
+
+ update materialized_views set min_refresh_rate_in_sec = v_min_refresh_rate
+ where mv_name = v_matview;
+ RETURN;
+ END; $procedure$
+ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION ActivateMaterializedView(v_matview NAME, v_active
BOOLEAN)
+ RETURNS VOID
+AS $procedure$
+DECLARE
+ v_entry materialized_views%ROWTYPE;
+ BEGIN
+ SELECT * INTO v_entry FROM materialized_views WHERE mv_name = v_matview;
+ IF NOT FOUND THEN
+ RAISE EXCEPTION 'Materialized view % does not exist.', v_matview;
+ END IF;
+
+ update materialized_views set active = v_active
+ where mv_name = v_matview;
+ RETURN;
+ END; $procedure$
+ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION ActivateAllMaterializedViews(v_active BOOLEAN)
+ RETURNS VOID
+AS $procedure$
+DECLARE
+ v_entry materialized_views%ROWTYPE;
+ BEGIN
+ update materialized_views set active = v_active;
+ RETURN;
+ END; $procedure$
+ LANGUAGE plpgsql;
+
diff --git
a/backend/manager/dbscripts/upgrade/03_02_0400_materialized_views_extensions.sql
b/backend/manager/dbscripts/upgrade/03_02_0400_materialized_views_extensions.sql
new file mode 100644
index 0000000..fef5f93
--- /dev/null
+++
b/backend/manager/dbscripts/upgrade/03_02_0400_materialized_views_extensions.sql
@@ -0,0 +1,7 @@
+-- Determines the minimum duration we will have to wait between 2 refreshes ,
0 means : no wait
+select fn_db_add_column('materialized_views', 'min_refresh_rate_in_sec', 'int
default 0');
+-- Indicates if this is a product view or custom view
+select fn_db_add_column('materialized_views', 'custom', 'boolean default
false');
+-- Indicates if this materialized_view is currently active
+select fn_db_add_column('materialized_views', 'active', 'boolean default
true');
+
--
To view, visit http://gerrit.ovirt.org/11669
To unsubscribe, visit http://gerrit.ovirt.org/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I6a6857a1691c1dafa1a7edcd58b292e70c78b318
Gerrit-PatchSet: 1
Gerrit-Project: ovirt-engine
Gerrit-Branch: master
Gerrit-Owner: Eli Mesika <[email protected]>
_______________________________________________
Engine-patches mailing list
[email protected]
http://lists.ovirt.org/mailman/listinfo/engine-patches
