Mercurial > gemma
changeset 5391:1dd63a2405bc extented-report
Fixed remaining TODO for refreshing materialized views within a procedure with owner rights.
| author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
|---|---|
| date | Sat, 03 Jul 2021 00:43:25 +0200 |
| parents | 90fc90148ef8 |
| children | 014dc3219bdb |
| files | schema/reports.sql schema/updates/1453/01.update_dqr_stats.sql schema/version.sql |
| diffstat | 3 files changed, 36 insertions(+), 13 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/reports.sql Fri Jul 02 18:56:59 2021 +0200 +++ b/schema/reports.sql Sat Jul 03 00:43:25 2021 +0200 @@ -68,18 +68,22 @@ WHERE bid.objnam = bn.objnam GROUP BY bn.cc, d.ym, bid.objnam; - --- Config update statement - --- TODO --- --- INSERT INTO sys_admin.stats_updates --- VALUES ('Data quality report', --- 'REFRESH MATERIALIZED VIEW waterway.dqr_bottleneck_stats; --- REFRESH MATERIALIZED VIEW waterway.dqr_gauge_stats;'); --- --- THIS DOES _NOT_ WORK! --- To make this work we need a wrapper function with owner rights for +-- We need a wrapper procedure with owner rights for -- the refresh, as (from the PGSQL manual): "REFRESH MATERIALIZED VIEW -- completely replaces the contents of a materialized view. To execute -- this command you must be the owner of the materialized view."" + +CREATE OR REPLACE PROCEDURE sys_admin.update_dqr_stats() +LANGUAGE plpgsql AS $$ +BEGIN + EXECUTE 'REFRESH MATERIALIZED VIEW waterway.dqr_bottleneck_stats'; + EXECUTE 'REFRESH MATERIALIZED VIEW waterway.dqr_gauge_stats'; +END; +$$ SECURITY DEFINER; + +GRANT EXECUTE ON PROCEDURE sys_admin.update_dqr_stats() TO sys_admin; + +-- Config update statement +INSERT INTO sys_admin.stats_updates + VALUES ('Data quality report', + 'CALL sys_admin.update_dqr_stats();');
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1453/01.update_dqr_stats.sql Sat Jul 03 00:43:25 2021 +0200 @@ -0,0 +1,19 @@ +-- We need a wrapper procedure with owner rights for +-- the refresh, as (from the PGSQL manual): "REFRESH MATERIALIZED VIEW +-- completely replaces the contents of a materialized view. To execute +-- this command you must be the owner of the materialized view."" + +CREATE OR REPLACE PROCEDURE sys_admin.update_dqr_stats() +LANGUAGE plpgsql AS $$ +BEGIN + EXECUTE 'REFRESH MATERIALIZED VIEW waterway.dqr_bottleneck_stats'; + EXECUTE 'REFRESH MATERIALIZED VIEW waterway.dqr_gauge_stats'; +END; +$$ SECURITY DEFINER; + +GRANT EXECUTE ON PROCEDURE sys_admin.update_dqr_stats() TO sys_admin; + +-- Config update statement +INSERT INTO sys_admin.stats_updates + VALUES ('Data quality report', + 'CALL sys_admin.update_dqr_stats();');