From 77f5851552b65637855449e79b6ab166498ac291 Mon Sep 17 00:00:00 2001 From: Jason Stephenson Date: Wed, 20 Oct 2021 12:38:46 -0400 Subject: [PATCH] Lp 1947595: Remove the array_accum aggregate function Remove the array_accum aggregate from Evergreen because it will need to be dropped and redefined when upgrading to PostgreSQL 14. Replace any uses of array_accum with array_agg in the code. Signed-off-by: Jason Stephenson Signed-off-by: Jason Boyer Signed-off-by: Jane Sandberg --- Open-ILS/src/perlmods/lib/OpenILS/Application/Booking.pm | 4 ++-- Open-ILS/src/sql/Pg/002.functions.aggregate.sql | 8 -------- Open-ILS/src/sql/Pg/t/search_limit_facet_fetch.pg | 2 +- .../src/sql/Pg/upgrade/XXXX.function.remove-array_accum.sql | 7 +++++++ docs/RELEASE_NOTES_NEXT/API/array-accum-removed.adoc | 10 ++++++++++ 5 files changed, 20 insertions(+), 11 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.function.remove-array_accum.sql create mode 100644 docs/RELEASE_NOTES_NEXT/API/array-accum-removed.adoc diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Booking.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Booking.pm index 8b48d7c38b..ca556fb44f 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Booking.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Booking.pm @@ -351,7 +351,7 @@ sub resource_list_by_attrs { if (!ref($filters->{attribute_values})); $query->{having}->{'+bram'}->{value}->{'@>'} = { - transform => 'array_accum', + transform => 'array_agg', value => '$_' . $$ . '${' . join(',', @{$filters->{attribute_values}}) . '}$_' . $$ . '$' @@ -554,7 +554,7 @@ sub reservation_list_by_filters { if (!ref($filters->{attribute_values})); $query->{having}->{'+bravm'}->{attr_value}->{'@>'} = { - transform => 'array_accum', + transform => 'array_agg', value => '$_' . $$ . '${' . join(',', @{$filters->{attribute_values}}) . '}$_' . $$ . '$' diff --git a/Open-ILS/src/sql/Pg/002.functions.aggregate.sql b/Open-ILS/src/sql/Pg/002.functions.aggregate.sql index cb42cd7b06..49eda408f0 100644 --- a/Open-ILS/src/sql/Pg/002.functions.aggregate.sql +++ b/Open-ILS/src/sql/Pg/002.functions.aggregate.sql @@ -17,18 +17,10 @@ BEGIN; -DROP AGGREGATE IF EXISTS array_accum(anyelement) CASCADE; DROP AGGREGATE IF EXISTS public.first(anyelement) CASCADE; DROP AGGREGATE IF EXISTS public.last(anyelement) CASCADE; DROP AGGREGATE IF EXISTS public.agg_text(text) CASCADE; -CREATE AGGREGATE array_accum ( - sfunc = array_append, - basetype = anyelement, - stype = anyarray, - initcond = '{}' -); - CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement ) RETURNS anyelement AS $$ SELECT CASE WHEN $1 IS NULL THEN $2 ELSE $1 END; $$ LANGUAGE SQL STABLE; diff --git a/Open-ILS/src/sql/Pg/t/search_limit_facet_fetch.pg b/Open-ILS/src/sql/Pg/t/search_limit_facet_fetch.pg index 86023ce969..a302d1e08b 100644 --- a/Open-ILS/src/sql/Pg/t/search_limit_facet_fetch.pg +++ b/Open-ILS/src/sql/Pg/t/search_limit_facet_fetch.pg @@ -64,7 +64,7 @@ SELECT is( SELECT is( ( SELECT COUNT(*) FROM ( - SELECT search.facets_for_metarecord_set('{}', array_accum(metarecord)) + SELECT search.facets_for_metarecord_set('{}', array_agg(metarecord)) FROM metabib.metarecord_source_map WHERE source = 999999998 ) x diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.function.remove-array_accum.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.remove-array_accum.sql new file mode 100644 index 0000000000..864f23f42c --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.remove-array_accum.sql @@ -0,0 +1,7 @@ +BEGIN; + +--SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +DROP AGGREGATE IF EXISTS array_accum(anyelement) CASCADE; + +COMMIT; diff --git a/docs/RELEASE_NOTES_NEXT/API/array-accum-removed.adoc b/docs/RELEASE_NOTES_NEXT/API/array-accum-removed.adoc new file mode 100644 index 0000000000..dd5a99625b --- /dev/null +++ b/docs/RELEASE_NOTES_NEXT/API/array-accum-removed.adoc @@ -0,0 +1,10 @@ +== array_accum Aggregate Removed == + +The custom `array_accum` aggregate function has been removed from the +PostgreSQL database because it will need to be dropped and recreated +with a different definition when upgrading to PostgreSQL version 14 or +later. Its functionality is also redundant with PostgreSQL's own +`array_agg` function. + +Sites that have custom code using Evergreen's `array_accum` function +should alter their code to use `array_agg` instead. -- 2.11.0