From b95949a1489e1d761b9b11bfa7c3fb4e9d6e386a Mon Sep 17 00:00:00 2001 From: Jeff Godin Date: Thu, 22 Jun 2017 20:03:29 -0400 Subject: [PATCH] LP#1671150 fix unqualified unaccent call Fix index creation failures when using pg_restore by qualifying the unaccent() function call in evergreen.unaccent_and_squash() Signed-off-by: Jeff Godin Signed-off-by: Jason Stephenson --- Open-ILS/src/sql/Pg/000.functions.general.sql | 2 +- .../XXXX.schema.qualify_unaccent_refs.sql | 32 +++++++++++++++++++ 2 files changed, 33 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.qualify_unaccent_refs.sql diff --git a/Open-ILS/src/sql/Pg/000.functions.general.sql b/Open-ILS/src/sql/Pg/000.functions.general.sql index 8b0dd63cc2..e0aae8d6db 100644 --- a/Open-ILS/src/sql/Pg/000.functions.general.sql +++ b/Open-ILS/src/sql/Pg/000.functions.general.sql @@ -89,7 +89,7 @@ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION evergreen.unaccent_and_squash ( IN arg text) RETURNS text IMMUTABLE STRICT AS $$ BEGIN - RETURN evergreen.lowercase(unaccent(regexp_replace(arg, '[\s[:punct:]]','','g'))); + RETURN evergreen.lowercase(public.unaccent('public.unaccent', regexp_replace(arg, '[\s[:punct:]]','','g'))); END; $$ LANGUAGE PLPGSQL; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.qualify_unaccent_refs.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.qualify_unaccent_refs.sql new file mode 100644 index 0000000000..5a73f4b5f7 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.qualify_unaccent_refs.sql @@ -0,0 +1,32 @@ +-- Evergreen DB patch XXXX.schema.qualify_unaccent_refs.sql +-- +-- LP#1671150 Fix unaccent() function call in evergreen.unaccent_and_squash() +-- +BEGIN; + + +-- check whether patch can be applied +-- FIXME: uncomment when we have an upgrade number +-- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +CREATE OR REPLACE FUNCTION evergreen.unaccent_and_squash ( IN arg text) RETURNS text + IMMUTABLE STRICT AS $$ + BEGIN + RETURN evergreen.lowercase(public.unaccent('public.unaccent', regexp_replace(arg, '[\s[:punct:]]','','g'))); + END; +$$ LANGUAGE PLPGSQL; + +-- Drop indexes if present, so that we can re-create them +DROP INDEX IF EXISTS actor.actor_usr_first_given_name_unaccent_idx; +DROP INDEX IF EXISTS actor.actor_usr_second_given_name_unaccent_idx; +DROP INDEX IF EXISTS actor.actor_usr_family_name_unaccent_idx; +DROP INDEX IF EXISTS actor.actor_usr_usrname_unaccent_idx; + +-- Create (or re-create) indexes -- they may be missing if pg_restore failed to create +-- them due to the previously unqualified call to unaccent() +CREATE INDEX actor_usr_first_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(first_given_name)); +CREATE INDEX actor_usr_second_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(second_given_name)); +CREATE INDEX actor_usr_family_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(family_name)); +CREATE INDEX actor_usr_usrname_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(usrname)); + +COMMIT; -- 2.43.2