From d171d708e5f3aae10952912a063db95048f28f28 Mon Sep 17 00:00:00 2001 From: Jason Stephenson Date: Wed, 23 Nov 2022 15:38:00 -0500 Subject: [PATCH] LP1997098: Add Support for PostgreSQL 15 Add Makefile targets to install the PostgreSQL 15 server prerequisites. Make postgresql-client-15 the default client version. Add full text search configuration for PostgreSQL 15. Modify config.ccmm_once_per_paramset and config.chmm_once_per_paramset indexes for the interval casting function change from immutable to stable. Add a database upgrade script for the above. Update the server installation instruction (README) for PostgreSQL 15. Signed-off-by: Jason Stephenson Signed-off-by: Bill Erickson --- Open-ILS/src/extras/Makefile.install | 8 ++++++++ Open-ILS/src/extras/install/Makefile.debian-bullseye | 14 +++++++++++++- Open-ILS/src/extras/install/Makefile.debian-buster | 14 +++++++++++++- Open-ILS/src/extras/install/Makefile.ubuntu-focal | 14 +++++++++++++- Open-ILS/src/extras/install/Makefile.ubuntu-jammy | 14 +++++++++++++- Open-ILS/src/sql/Pg/000.english.pg15.fts-config.sql | 1 + Open-ILS/src/sql/Pg/100.circ_matrix.sql | 2 +- Open-ILS/src/sql/Pg/110.hold_matrix.sql | 2 +- .../sql/Pg/upgrade/XXXX.schema.fix-interval-indexes.sql | 13 +++++++++++++ docs/modules/installation/pages/server_installation.adoc | 11 +++++++++++ 10 files changed, 87 insertions(+), 6 deletions(-) create mode 120000 Open-ILS/src/sql/Pg/000.english.pg15.fts-config.sql create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fix-interval-indexes.sql diff --git a/Open-ILS/src/extras/Makefile.install b/Open-ILS/src/extras/Makefile.install index c2872ab1e2..eaefbdd7e0 100644 --- a/Open-ILS/src/extras/Makefile.install +++ b/Open-ILS/src/extras/Makefile.install @@ -91,6 +91,8 @@ postgres-server-debian-bullseye-13: @make -f $(DIR)/Makefile.debian-bullseye install_postgres_server_13 postgres-server-debian-bullseye-14: @make -f $(DIR)/Makefile.debian-bullseye install_postgres_server_14 +postgres-server-debian-bullseye-15: + @make -f $(DIR)/Makefile.debian-bullseye install_postgres_server_15 postgres-server-debian-buster-10: @make -f $(DIR)/Makefile.debian-buster install_postgres_server_10 postgres-server-debian-buster-11: @@ -101,6 +103,8 @@ postgres-server-debian-buster-13: @make -f $(DIR)/Makefile.debian-buster install_postgres_server_13 postgres-server-debian-buster-14: @make -f $(DIR)/Makefile.debian-buster install_postgres_server_14 +postgres-server-debian-buster-15: + @make -f $(DIR)/Makefile.debian-buster install_postgres_server_15 postgres-server-ubuntu-focal-10: @make -f $(DIR)/Makefile.ubuntu-focal install_postgres_server_10 postgres-server-ubuntu-focal-11: @@ -111,6 +115,8 @@ postgres-server-ubuntu-focal-13: @make -f $(DIR)/Makefile.ubuntu-focal install_postgres_server_13 postgres-server-ubuntu-focal-14: @make -f $(DIR)/Makefile.ubuntu-focal install_postgres_server_14 +postgres-server-ubuntu-focal-15: + @make -f $(DIR)/Makefile.ubuntu-focal install_postgres_server_15 postgres-server-ubuntu-jammy-10: @make -f $(DIR)/Makefile.ubuntu-jammy install_postgres_server_10 postgres-server-ubuntu-jammy-11: @@ -121,6 +127,8 @@ postgres-server-ubuntu-jammy-13: @make -f $(DIR)/Makefile.ubuntu-jammy install_postgres_server_13 postgres-server-ubuntu-jammy-14: @make -f $(DIR)/Makefile.ubuntu-jammy install_postgres_server_14 +postgres-server-ubuntu-jammy-15: + @make -f $(DIR)/Makefile.ubuntu-jammy install_postgres_server_15 postgres-server-fedora: @make -f $(DIR)/Makefile.fedora install_postgres_server diff --git a/Open-ILS/src/extras/install/Makefile.debian-bullseye b/Open-ILS/src/extras/install/Makefile.debian-bullseye index b8da0991ac..3327b17970 100644 --- a/Open-ILS/src/extras/install/Makefile.debian-bullseye +++ b/Open-ILS/src/extras/install/Makefile.debian-bullseye @@ -75,7 +75,7 @@ export DEBS = \ libuniversal-require-perl\ libunix-syslog-perl\ libyaz-dev\ - postgresql-client-14\ + postgresql-client-15\ libsoap-lite-perl\ libbz2-dev\ libparse-recdescent-perl\ @@ -147,6 +147,12 @@ PGSQL_SERVER_DEBS_14 = \ postgresql-plperl-14 \ postgresql-server-dev-14 +PGSQL_SERVER_DEBS_15 = \ + $(DEB_PGSQL_COMMON_MODS) \ + postgresql-15 \ + postgresql-plperl-15 \ + postgresql-server-dev-15 + # note: some prereqs are repeated in the developer/packager # sections to support building Evergreen packages on servers # where Evergreen and its prereqs are not installed. @@ -213,6 +219,12 @@ install_postgres_server_14: make -f $(DIR)/Makefile.debian DEBS="$(PGSQL_SERVER_DEBS_14)" make -f $(DIR)/Makefile.common install_cpan_pgsql +install_postgres_server_15: + make -f $(DIR)/Makefile.debian DEBS="$(PGSQL_APT_REPO_DEBS)" + make -f $(DIR)/Makefile.debian debian_postgresql_repo + make -f $(DIR)/Makefile.debian DEBS="$(PGSQL_SERVER_DEBS_15)" + make -f $(DIR)/Makefile.common install_cpan_pgsql + # note: if/when grunt-cli is available as a # package, use the packaged version instead. install_developer: diff --git a/Open-ILS/src/extras/install/Makefile.debian-buster b/Open-ILS/src/extras/install/Makefile.debian-buster index 719eefdfc9..849fa3bfa8 100644 --- a/Open-ILS/src/extras/install/Makefile.debian-buster +++ b/Open-ILS/src/extras/install/Makefile.debian-buster @@ -75,7 +75,7 @@ export DEBS = \ libuniversal-require-perl\ libunix-syslog-perl\ libyaz-dev\ - postgresql-client-14\ + postgresql-client-15\ libsoap-lite-perl\ libbz2-dev\ libparse-recdescent-perl\ @@ -147,6 +147,12 @@ PGSQL_SERVER_DEBS_14 = \ postgresql-plperl-14 \ postgresql-server-dev-14 +PGSQL_SERVER_DEBS_15 = \ + $(DEB_PGSQL_COMMON_MODS) \ + postgresql-15 \ + postgresql-plperl-15 \ + postgresql-server-dev-15 + # note: some prereqs are repeated in the developer/packager # sections to support building Evergreen packages on servers # where Evergreen and its prereqs are not installed. @@ -213,6 +219,12 @@ install_postgres_server_14: make -f $(DIR)/Makefile.debian DEBS="$(PGSQL_SERVER_DEBS_14)" make -f $(DIR)/Makefile.common install_cpan_pgsql +install_postgres_server_15: + make -f $(DIR)/Makefile.debian DEBS="$(PGSQL_APT_REPO_DEBS)" + make -f $(DIR)/Makefile.debian debian_postgresql_repo + make -f $(DIR)/Makefile.debian DEBS="$(PGSQL_SERVER_DEBS_15)" + make -f $(DIR)/Makefile.common install_cpan_pgsql + # note: if/when grunt-cli is available as a # package, use the packaged version instead. install_developer: diff --git a/Open-ILS/src/extras/install/Makefile.ubuntu-focal b/Open-ILS/src/extras/install/Makefile.ubuntu-focal index 85da870669..e907b7fe1b 100644 --- a/Open-ILS/src/extras/install/Makefile.ubuntu-focal +++ b/Open-ILS/src/extras/install/Makefile.ubuntu-focal @@ -72,7 +72,7 @@ export DEBS = \ libuniversal-require-perl\ libunix-syslog-perl\ libyaz-dev\ - postgresql-client-14\ + postgresql-client-15\ libsoap-lite-perl\ libbz2-dev\ libparse-recdescent-perl\ @@ -150,6 +150,12 @@ PGSQL_SERVER_DEBS_14 = \ postgresql-plperl-14 \ postgresql-server-dev-14 +PGSQL_SERVER_DEBS_15 = \ + $(DEB_PGSQL_COMMON_MODS) \ + postgresql-15 \ + postgresql-plperl-15 \ + postgresql-server-dev-15 + # note: some prereqs are repeated in the developer/packager # sections to support building Evergreen packages on servers # where Evergreen and its prereqs are not installed. @@ -217,6 +223,12 @@ install_postgres_server_14: make -f $(DIR)/Makefile.debian DEBS="$(PGSQL_SERVER_DEBS_14)" make -f $(DIR)/Makefile.common install_cpan_pgsql +install_postgres_server_15: + make -f $(DIR)/Makefile.debian DEBS="$(PGSQL_APT_REPO_DEBS)" + make -f $(DIR)/Makefile.debian debian_postgresql_repo + make -f $(DIR)/Makefile.debian DEBS="$(PGSQL_SERVER_DEBS_15)" + make -f $(DIR)/Makefile.common install_cpan_pgsql + # note: if/when grunt-cli is available as a # package, use the packaged version instead. install_developer: diff --git a/Open-ILS/src/extras/install/Makefile.ubuntu-jammy b/Open-ILS/src/extras/install/Makefile.ubuntu-jammy index 0e1014ac3e..01c42aa30c 100644 --- a/Open-ILS/src/extras/install/Makefile.ubuntu-jammy +++ b/Open-ILS/src/extras/install/Makefile.ubuntu-jammy @@ -75,7 +75,7 @@ export DEBS = \ libuniversal-require-perl\ libunix-syslog-perl\ libyaz-dev\ - postgresql-client-14\ + postgresql-client-15\ libsoap-lite-perl\ libbz2-dev\ libparse-recdescent-perl\ @@ -150,6 +150,12 @@ PGSQL_SERVER_DEBS_14 = \ postgresql-plperl-14 \ postgresql-server-dev-14 +PGSQL_SERVER_DEBS_15 = \ + $(DEB_PGSQL_COMMON_MODS) \ + postgresql-15 \ + postgresql-plperl-15 \ + postgresql-server-dev-15 + # note: some prereqs are repeated in the developer/packager # sections to support building Evergreen packages on servers # where Evergreen and its prereqs are not installed. @@ -217,6 +223,12 @@ install_postgres_server_14: make -f $(DIR)/Makefile.debian DEBS="$(PGSQL_SERVER_DEBS_14)" make -f $(DIR)/Makefile.common install_cpan_pgsql +install_postgres_server_15: + make -f $(DIR)/Makefile.debian DEBS="$(PGSQL_APT_REPO_DEBS)" + make -f $(DIR)/Makefile.debian debian_postgresql_repo + make -f $(DIR)/Makefile.debian DEBS="$(PGSQL_SERVER_DEBS_15)" + make -f $(DIR)/Makefile.common install_cpan_pgsql + # note: if/when grunt-cli is available as a # package, use the packaged version instead. install_developer: diff --git a/Open-ILS/src/sql/Pg/000.english.pg15.fts-config.sql b/Open-ILS/src/sql/Pg/000.english.pg15.fts-config.sql new file mode 120000 index 0000000000..38504b54c7 --- /dev/null +++ b/Open-ILS/src/sql/Pg/000.english.pg15.fts-config.sql @@ -0,0 +1 @@ +000.english.pg94.fts-config.sql \ No newline at end of file diff --git a/Open-ILS/src/sql/Pg/100.circ_matrix.sql b/Open-ILS/src/sql/Pg/100.circ_matrix.sql index 1badabe07c..4e9771566b 100644 --- a/Open-ILS/src/sql/Pg/100.circ_matrix.sql +++ b/Open-ILS/src/sql/Pg/100.circ_matrix.sql @@ -89,7 +89,7 @@ CREATE TABLE config.circ_matrix_matchpoint ( ); -- Nulls don't count for a constraint match, so we have to coalesce them into something that does. -CREATE UNIQUE INDEX ccmm_once_per_paramset ON config.circ_matrix_matchpoint (org_unit, grp, COALESCE(circ_modifier, ''), COALESCE(copy_location::TEXT, ''), COALESCE(marc_type, ''), COALESCE(marc_form, ''), COALESCE(marc_bib_level,''), COALESCE(marc_vr_format, ''), COALESCE(copy_circ_lib::TEXT, ''), COALESCE(copy_owning_lib::TEXT, ''), COALESCE(user_home_ou::TEXT, ''), COALESCE(ref_flag::TEXT, ''), COALESCE(juvenile_flag::TEXT, ''), COALESCE(is_renewal::TEXT, ''), COALESCE(usr_age_lower_bound::TEXT, ''), COALESCE(usr_age_upper_bound::TEXT, ''), COALESCE(item_age::TEXT, '')) WHERE active; +CREATE UNIQUE INDEX ccmm_once_per_paramset ON config.circ_matrix_matchpoint (org_unit, grp, COALESCE(circ_modifier, ''), COALESCE(copy_location::TEXT, ''), COALESCE(marc_type, ''), COALESCE(marc_form, ''), COALESCE(marc_bib_level,''), COALESCE(marc_vr_format, ''), COALESCE(copy_circ_lib::TEXT, ''), COALESCE(copy_owning_lib::TEXT, ''), COALESCE(user_home_ou::TEXT, ''), COALESCE(ref_flag::TEXT, ''), COALESCE(juvenile_flag::TEXT, ''), COALESCE(is_renewal::TEXT, ''), COALESCE(usr_age_lower_bound, '0 seconds'), COALESCE(usr_age_upper_bound, '0 seconds'), COALESCE(item_age, '0 seconds')) WHERE active; -- Limit groups for circ counting CREATE TABLE config.circ_limit_group ( diff --git a/Open-ILS/src/sql/Pg/110.hold_matrix.sql b/Open-ILS/src/sql/Pg/110.hold_matrix.sql index 248460aad1..1fc85462d4 100644 --- a/Open-ILS/src/sql/Pg/110.hold_matrix.sql +++ b/Open-ILS/src/sql/Pg/110.hold_matrix.sql @@ -60,7 +60,7 @@ CREATE TABLE config.hold_matrix_matchpoint ( ); -- Nulls don't count for a constraint match, so we have to coalesce them into something that does. -CREATE UNIQUE INDEX chmm_once_per_paramset ON config.hold_matrix_matchpoint (COALESCE(user_home_ou::TEXT, ''), COALESCE(request_ou::TEXT, ''), COALESCE(pickup_ou::TEXT, ''), COALESCE(item_owning_ou::TEXT, ''), COALESCE(item_circ_ou::TEXT, ''), COALESCE(usr_grp::TEXT, ''), COALESCE(requestor_grp::TEXT, ''), COALESCE(circ_modifier, ''), COALESCE(marc_type, ''), COALESCE(marc_form, ''), COALESCE(marc_bib_level, ''), COALESCE(marc_vr_format, ''), COALESCE(juvenile_flag::TEXT, ''), COALESCE(ref_flag::TEXT, ''), COALESCE(item_age::TEXT, '')) WHERE active; +CREATE UNIQUE INDEX chmm_once_per_paramset ON config.hold_matrix_matchpoint (COALESCE(user_home_ou::TEXT, ''), COALESCE(request_ou::TEXT, ''), COALESCE(pickup_ou::TEXT, ''), COALESCE(item_owning_ou::TEXT, ''), COALESCE(item_circ_ou::TEXT, ''), COALESCE(usr_grp::TEXT, ''), COALESCE(requestor_grp::TEXT, ''), COALESCE(circ_modifier, ''), COALESCE(marc_type, ''), COALESCE(marc_form, ''), COALESCE(marc_bib_level, ''), COALESCE(marc_vr_format, ''), COALESCE(juvenile_flag::TEXT, ''), COALESCE(ref_flag::TEXT, ''), COALESCE(item_age, '0 seconds')) WHERE active; CREATE OR REPLACE FUNCTION action.find_hold_matrix_matchpoint(pickup_ou integer, request_ou integer, match_item bigint, match_user integer, match_requestor integer) RETURNS integer AS diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fix-interval-indexes.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fix-interval-indexes.sql new file mode 100644 index 0000000000..c956cf8e96 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fix-interval-indexes.sql @@ -0,0 +1,13 @@ +BEGIN; + +--SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +DROP INDEX config.ccmm_once_per_paramset; + +CREATE UNIQUE INDEX ccmm_once_per_paramset ON config.circ_matrix_matchpoint (org_unit, grp, COALESCE(circ_modifier, ''), COALESCE(copy_location::TEXT, ''), COALESCE(marc_type, ''), COALESCE(marc_form, ''), COALESCE(marc_bib_level,''), COALESCE(marc_vr_format, ''), COALESCE(copy_circ_lib::TEXT, ''), COALESCE(copy_owning_lib::TEXT, ''), COALESCE(user_home_ou::TEXT, ''), COALESCE(ref_flag::TEXT, ''), COALESCE(juvenile_flag::TEXT, ''), COALESCE(is_renewal::TEXT, ''), COALESCE(usr_age_lower_bound, '0 seconds'), COALESCE(usr_age_upper_bound, '0 seconds'), COALESCE(item_age, '0 seconds')) WHERE active; + +DROP INDEX config.chmm_once_per_paramset; + +CREATE UNIQUE INDEX chmm_once_per_paramset ON config.hold_matrix_matchpoint (COALESCE(user_home_ou::TEXT, ''), COALESCE(request_ou::TEXT, ''), COALESCE(pickup_ou::TEXT, ''), COALESCE(item_owning_ou::TEXT, ''), COALESCE(item_circ_ou::TEXT, ''), COALESCE(usr_grp::TEXT, ''), COALESCE(requestor_grp::TEXT, ''), COALESCE(circ_modifier, ''), COALESCE(marc_type, ''), COALESCE(marc_form, ''), COALESCE(marc_bib_level, ''), COALESCE(marc_vr_format, ''), COALESCE(juvenile_flag::TEXT, ''), COALESCE(ref_flag::TEXT, ''), COALESCE(item_age, '0 seconds')) WHERE active; + +COMMIT; diff --git a/docs/modules/installation/pages/server_installation.adoc b/docs/modules/installation/pages/server_installation.adoc index 361a643c35..e7534d0278 100644 --- a/docs/modules/installation/pages/server_installation.adoc +++ b/docs/modules/installation/pages/server_installation.adoc @@ -498,6 +498,17 @@ make -f Open-ILS/src/extras/Makefile.install postgres-server-ubuntu-focal-14 make -f Open-ILS/src/extras/Makefile.install postgres-server-ubuntu-jammy-14 ------------------------------------------------------------------------------ +To install PostgreSQL version 15, use the following command for your operating +system: + +[source, bash] +------------------------------------------------------------------------------ +make -f Open-ILS/src/extras/Makefile.install postgres-server-debian-bullseye-15 +make -f Open-ILS/src/extras/Makefile.install postgres-server-debian-buster-15 +make -f Open-ILS/src/extras/Makefile.install postgres-server-ubuntu-focal-15 +make -f Open-ILS/src/extras/Makefile.install postgres-server-ubuntu-jammy-15 +------------------------------------------------------------------------------ + For a standalone PostgreSQL server, install the following Perl modules for your distribution as the *root* Linux account: -- 2.11.0