From 789352a23075a6689385757eccf7c62ab854ee5a Mon Sep 17 00:00:00 2001 From: Ray Lee Date: Thu, 19 Sep 2024 13:37:35 -0400 Subject: [PATCH] DRYD-1487: Fix cspace_english text search config not found during db restore on RDS. --- .../unaccent_text_search_configuration.sql | 25 +++++++++++++++++++ 1 file changed, 25 insertions(+) diff --git a/services/common/src/main/resources/db/postgresql/unaccent_text_search_configuration.sql b/services/common/src/main/resources/db/postgresql/unaccent_text_search_configuration.sql index 62f59d1bc..72ec6968a 100644 --- a/services/common/src/main/resources/db/postgresql/unaccent_text_search_configuration.sql +++ b/services/common/src/main/resources/db/postgresql/unaccent_text_search_configuration.sql @@ -1,6 +1,12 @@ /* * If the unaccent extension is installed, modify the cspace_english text search configuration to * be accent-insensitive. + * + * Also replace the nx_to_tsvector function generated by nuxeo. This is identical to nuxeo's + * implementation, except that it searches all namespaces for the cspace_english text search + * configuration, instead of using "public.cspace_english", as configured in + * proto-repo-config.xml. This is to work around Amazon RDS renaming the public namespace to + * something random during database restores. */ DO $$ @@ -11,6 +17,25 @@ BEGIN ALTER TEXT SEARCH CONFIGURATION cspace_english ALTER MAPPING FOR asciihword, asciiword, hword_asciipart, hword, hword_part, word WITH unaccent, english_stem; + + CREATE OR REPLACE FUNCTION nx_to_tsvector(string VARCHAR) RETURNS TSVECTOR AS $func$ + DECLARE + search_namespace TEXT; + result TSVECTOR; + BEGIN + SELECT + nspname INTO search_namespace + FROM + pg_namespace + INNER JOIN pg_ts_config ON pg_namespace.oid = pg_ts_config.cfgnamespace + AND cfgname = 'cspace_english' :: text; + + PERFORM set_config('search_path', search_namespace, true); + + result := TO_TSVECTOR('cspace_english', SUBSTR($1, 1, 250000)); + RETURN result; + END; + $func$ LANGUAGE plpgsql IMMUTABLE; END IF; END IF; -- 2.47.3