From e3337408549eae093aff3b76c8d00125257d09a2 Mon Sep 17 00:00:00 2001 From: Chris Sharp Date: Mon, 20 May 2019 16:14:00 -0400 Subject: [PATCH] LP#1768141 - Mark permission.grp_descendants STABLE. Quoting Galen Charlton's comments on the bug report: The database function permission.grp_descendants should be marked stable; it qualifies, and doing so should allow PostgreSQL to improve query plans. One example of a query that would benefit is searching by patron profile (a la bug 1497322) Signed-off-by: Chris Sharp Signed-off-by: Galen Charlton Signed-off-by: Jason Boyer --- .../src/sql/Pg/006.schema.permissions.sql | 2 +- ...chema.mark_perm_grp_descendants_stable.sql | 19 +++++++++++++++++++ 2 files changed, 20 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.mark_perm_grp_descendants_stable.sql diff --git a/Open-ILS/src/sql/Pg/006.schema.permissions.sql b/Open-ILS/src/sql/Pg/006.schema.permissions.sql index 564f7852f9..05d38a6243 100644 --- a/Open-ILS/src/sql/Pg/006.schema.permissions.sql +++ b/Open-ILS/src/sql/Pg/006.schema.permissions.sql @@ -133,7 +133,7 @@ CREATE OR REPLACE FUNCTION permission.grp_descendants( INT ) RETURNS SETOF permi FROM permission.grp_tree gr JOIN descendant_depth dd ON (dd.id = gr.parent) ) SELECT gr.* FROM permission.grp_tree gr JOIN descendant_depth USING (id); -$$ LANGUAGE SQL ROWS 1; +$$ LANGUAGE SQL STABLE ROWS 1; CREATE OR REPLACE FUNCTION permission.grp_tree_full_path ( INT ) RETURNS SETOF permission.grp_tree AS $$ SELECT * diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.mark_perm_grp_descendants_stable.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.mark_perm_grp_descendants_stable.sql new file mode 100644 index 0000000000..35244339a4 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.mark_perm_grp_descendants_stable.sql @@ -0,0 +1,19 @@ +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +CREATE OR REPLACE FUNCTION permission.grp_descendants( INT ) RETURNS SETOF permission.grp_tree AS $$ + WITH RECURSIVE descendant_depth AS ( + SELECT gr.id, + gr.parent + FROM permission.grp_tree gr + WHERE gr.id = $1 + UNION ALL + SELECT gr.id, + gr.parent + FROM permission.grp_tree gr + JOIN descendant_depth dd ON (dd.id = gr.parent) + ) SELECT gr.* FROM permission.grp_tree gr JOIN descendant_depth USING (id); +$$ LANGUAGE SQL STABLE ROWS 1; + +COMMIT; -- 2.43.2