From cccf8938f544d82a47b6df5c8c918a53e1aff728 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Fri, 28 Feb 2014 16:45:40 -0500 Subject: [PATCH] LP#1253163: Materialize authority headings There seems to be a bug in Postgres 9.3+ that does not like the circuitous route we take to indexing authority headings. So, we get around this by storing the actual heading values instead of indexing a function over the MARC. Signed-off-by: Mike Rylander Signed-off-by: Dan Wells Signed-off-by: Ben Shum --- .../OpenILS/Application/Search/Authority.pm | 7 ++- .../sql/Pg/000.english.pg93.fts-config.sql | 1 + Open-ILS/src/sql/Pg/011.schema.authority.sql | 19 ++++++-- Open-ILS/src/sql/Pg/800.fkeys.sql | 4 +- ...XXXX.schema.authority.in-line-headings.sql | 45 +++++++++++++++++++ 5 files changed, 67 insertions(+), 9 deletions(-) create mode 120000 Open-ILS/src/sql/Pg/000.english.pg93.fts-config.sql create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.authority.in-line-headings.sql diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Search/Authority.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Search/Authority.pm index d0e52aa315..d1f42d7225 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Search/Authority.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Search/Authority.pm @@ -71,10 +71,9 @@ sub search_authority_by_simple_normalize_heading { from => 'are', where => { deleted => 'f', - marc => { 'startwith' => { - transform => 'authority.simple_normalize_heading', - value => [ 'authority.simple_normalize_heading' => $marcxml ] - }}, + simple_heading => { + 'startwith' => [ 'authority.simple_normalize_heading' => $marcxml ] + }, defined($controlset) ? ( control_set => $controlset ) : () } }; diff --git a/Open-ILS/src/sql/Pg/000.english.pg93.fts-config.sql b/Open-ILS/src/sql/Pg/000.english.pg93.fts-config.sql new file mode 120000 index 0000000000..0b24fd90f5 --- /dev/null +++ b/Open-ILS/src/sql/Pg/000.english.pg93.fts-config.sql @@ -0,0 +1 @@ +000.english.pg91.fts-config.sql \ No newline at end of file diff --git a/Open-ILS/src/sql/Pg/011.schema.authority.sql b/Open-ILS/src/sql/Pg/011.schema.authority.sql index d3527f293f..99f21ea8a1 100644 --- a/Open-ILS/src/sql/Pg/011.schema.authority.sql +++ b/Open-ILS/src/sql/Pg/011.schema.authority.sql @@ -131,7 +131,9 @@ CREATE TABLE authority.record_entry ( control_set INT REFERENCES authority.control_set (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, marc TEXT NOT NULL, last_xact_id TEXT NOT NULL, - owner INT + owner INT, + heading TEXT, + simple_heading TEXT ); CREATE INDEX authority_record_entry_creator_idx ON authority.record_entry ( creator ); CREATE INDEX authority_record_entry_editor_idx ON authority.record_entry ( editor ); @@ -207,7 +209,7 @@ CREATE RULE protect_authority_rec_delete AS ON DELETE TO authority.record_entry -- Intended to be used in a unique index on authority.record_entry like so: -- CREATE UNIQUE INDEX unique_by_heading_and_thesaurus --- ON authority.record_entry (authority.normalize_heading(marc)) +-- ON authority.record_entry (heading) -- WHERE deleted IS FALSE or deleted = FALSE; CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT, no_thesaurus BOOL ) RETURNS TEXT AS $func$ DECLARE @@ -409,6 +411,17 @@ index to defend against duplicated authority records from the same thesaurus. $$; +-- Store these in line with the MARC for easier indexing +CREATE OR REPLACE FUNCTION authority.normalize_heading_for_upsert () RETURNS TRIGGER AS $f$ +BEGIN + NEW.heading := authority.normalize_heading( NEW.marc ); + NEW.simple_heading := authority.simple_normalize_heading( NEW.marc ); + RETURN NEW; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE TRIGGER update_headings_tgr BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE authority.normalize_heading_for_upsert(); + -- Adding indexes using oils_xpath_string() for the main entry tags described in -- authority.control_set_authority_field would speed this up, if we ever want to use it, though -- the existing index on authority.normalize_heading() helps already with a record in hand @@ -424,7 +437,7 @@ CREATE OR REPLACE VIEW authority.tracing_links AS link.id AS link_id, link.tag AS link_tag, oils_xpath_string('//*[@tag="'||link.tag||'"]/*[local-name()="subfield"]', are.marc) AS link_value, - authority.normalize_heading(are.marc) AS normalized_main_value + are.heading AS normalized_main_value FROM authority.full_rec main JOIN authority.record_entry are ON (main.record = are.id) JOIN authority.control_set_authority_field main_entry diff --git a/Open-ILS/src/sql/Pg/800.fkeys.sql b/Open-ILS/src/sql/Pg/800.fkeys.sql index 151cacb332..ee9886d8f4 100644 --- a/Open-ILS/src/sql/Pg/800.fkeys.sql +++ b/Open-ILS/src/sql/Pg/800.fkeys.sql @@ -128,8 +128,8 @@ ALTER TABLE config.org_unit_setting_type ADD CONSTRAINT update_perm_fkey FOREIGN ALTER TABLE config.barcode_completion ADD CONSTRAINT config_barcode_completion_org_unit_fkey FOREIGN KEY (org_unit) REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; -CREATE INDEX by_heading_and_thesaurus ON authority.record_entry (authority.normalize_heading(marc)) WHERE deleted IS FALSE or deleted = FALSE; -CREATE INDEX by_heading ON authority.record_entry (authority.simple_normalize_heading(marc)) WHERE deleted IS FALSE or deleted = FALSE; +CREATE INDEX by_heading_and_thesaurus ON authority.record_entry (heading) WHERE deleted IS FALSE or deleted = FALSE; +CREATE INDEX by_heading ON authority.record_entry (simple_heading) WHERE deleted IS FALSE or deleted = FALSE; ALTER TABLE config.z3950_source ADD CONSTRAINT use_perm_fkey FOREIGN KEY (use_perm) REFERENCES permission.perm_list (id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.authority.in-line-headings.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.authority.in-line-headings.sql new file mode 100644 index 0000000000..47fb7f2c11 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.authority.in-line-headings.sql @@ -0,0 +1,45 @@ + +ALTER TABLE authority.record_entry DISABLE TRIGGER a_marcxml_is_well_formed; +ALTER TABLE authority.record_entry DISABLE TRIGGER aaa_auth_ingest_or_delete; +ALTER TABLE authority.record_entry DISABLE TRIGGER b_maintain_901; +ALTER TABLE authority.record_entry DISABLE TRIGGER c_maintain_control_numbers; +ALTER TABLE authority.record_entry DISABLE TRIGGER map_thesaurus_to_control_set; + +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +ALTER TABLE authority.record_entry ADD COLUMN heading TEXT, ADD COLUMN simple_heading TEXT; + +DROP INDEX IF EXISTS authority.unique_by_heading_and_thesaurus; +DROP INDEX IF EXISTS authority.by_heading_and_thesaurus; +DROP INDEX IF EXISTS authority.by_heading; + +-- Update without indexes for HOT update +UPDATE authority.record_entry + SET heading = authority.normalize_heading( marc ), + simple_heading = authority.simple_normalize_heading( marc ); + +CREATE INDEX by_heading_and_thesaurus ON authority.record_entry (heading) WHERE deleted IS FALSE or deleted = FALSE; +CREATE INDEX by_heading ON authority.record_entry (simple_heading) WHERE deleted IS FALSE or deleted = FALSE; + +-- Add the trigger +CREATE OR REPLACE FUNCTION authority.normalize_heading_for_upsert () RETURNS TRIGGER AS $f$ +BEGIN + NEW.heading := authority.normalize_heading( NEW.marc ); + NEW.simple_heading := authority.simple_normalize_heading( NEW.marc ); + RETURN NEW; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE TRIGGER update_headings_tgr BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE authority.normalize_heading_for_upsert(); + +COMMIT; + +ALTER TABLE authority.record_entry ENABLE TRIGGER a_marcxml_is_well_formed; +ALTER TABLE authority.record_entry ENABLE TRIGGER aaa_auth_ingest_or_delete; +ALTER TABLE authority.record_entry ENABLE TRIGGER b_maintain_901; +ALTER TABLE authority.record_entry ENABLE TRIGGER c_maintain_control_numbers; +ALTER TABLE authority.record_entry ENABLE TRIGGER map_thesaurus_to_control_set; + + -- 2.43.2