From d48a4495756a8bbb5c21acb64af94f1e9ceae7a7 Mon Sep 17 00:00:00 2001 From: gmc Date: Tue, 30 Nov 2010 13:49:51 +0000 Subject: [PATCH] straighten out triggers that maintain rmsr In particular, make sure that the old zzz_ trigger on metabib.real_full_rec is completely removed, as it needlessly slows down bib ingest. Signed-off-by: Galen Charlton git-svn-id: svn://svn.open-ils.org/ILS/trunk@18869 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/1.6.1-2.0-upgrade-db.sql | 19 +++++++- Open-ILS/src/sql/Pg/999.functions.global.sql | 2 +- Open-ILS/src/sql/Pg/reporter-schema.sql | 55 ++-------------------- .../Pg/upgrade/0468.schema.simple_rec_triggers.sql | 34 +++++++++++++ 5 files changed, 57 insertions(+), 55 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0468.schema.simple_rec_triggers.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 7fe22b4239..22e3e0866c 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -70,7 +70,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0467'); -- gmc +INSERT INTO config.upgrade_log (version) VALUES ('0468'); -- gmc CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/1.6.1-2.0-upgrade-db.sql b/Open-ILS/src/sql/Pg/1.6.1-2.0-upgrade-db.sql index 16139edbda..61649529d6 100644 --- a/Open-ILS/src/sql/Pg/1.6.1-2.0-upgrade-db.sql +++ b/Open-ILS/src/sql/Pg/1.6.1-2.0-upgrade-db.sql @@ -11204,6 +11204,7 @@ $func$ LANGUAGE PLPGSQL; CREATE TRIGGER fingerprint_tgr BEFORE INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE biblio.fingerprint_trigger ('eng','BKS'); CREATE TRIGGER aaa_indexing_ingest_or_delete AFTER INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE biblio.indexing_ingest_or_delete (); +DROP TRIGGER IF EXISTS zzz_update_materialized_simple_record_tgr ON metabib.real_full_rec; DROP TRIGGER IF EXISTS zzz_update_materialized_simple_rec_delete_tgr ON biblio.record_entry; CREATE OR REPLACE FUNCTION oils_xpath_table ( key TEXT, document_field TEXT, relation_name TEXT, xpaths TEXT, criteria TEXT ) RETURNS SETOF RECORD AS $func$ @@ -18540,7 +18541,21 @@ SELECT r.id, GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14; CREATE OR REPLACE FUNCTION reporter.disable_materialized_simple_record_trigger () RETURNS VOID AS $$ - DROP TRIGGER IF EXISTS zzz_update_materialized_simple_record_tgr ON metabib.real_full_rec; + DROP TRIGGER IF EXISTS bbb_simple_rec_trigger ON biblio.record_entry; +$$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION reporter.enable_materialized_simple_record_trigger () RETURNS VOID AS $$ + + DELETE FROM reporter.materialized_simple_record; + + INSERT INTO reporter.materialized_simple_record + (id,fingerprint,quality,tcn_source,tcn_value,title,author,publisher,pubdate,isbn,issn) + SELECT DISTINCT ON (id) * FROM reporter.old_super_simple_record; + + CREATE TRIGGER bbb_simple_rec_trigger + AFTER INSERT OR UPDATE OR DELETE ON biblio.record_entry + FOR EACH ROW EXECUTE PROCEDURE reporter.simple_rec_trigger(); + $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION reporter.simple_rec_trigger () RETURNS TRIGGER AS $func$ @@ -18555,7 +18570,7 @@ BEGIN END; $func$ LANGUAGE PLPGSQL; -CREATE TRIGGER bbb_simple_rec_trigger AFTER INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE reporter.simple_rec_trigger (); +CREATE TRIGGER bbb_simple_rec_trigger AFTER INSERT OR UPDATE OR DELETE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE reporter.simple_rec_trigger (); ALTER TABLE extend_reporter.legacy_circ_count DROP CONSTRAINT legacy_circ_count_id_fkey; diff --git a/Open-ILS/src/sql/Pg/999.functions.global.sql b/Open-ILS/src/sql/Pg/999.functions.global.sql index ba4f373321..865d55230e 100644 --- a/Open-ILS/src/sql/Pg/999.functions.global.sql +++ b/Open-ILS/src/sql/Pg/999.functions.global.sql @@ -1452,7 +1452,7 @@ $func$ LANGUAGE PLPGSQL; -- Ingest triggers CREATE TRIGGER fingerprint_tgr BEFORE INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE biblio.fingerprint_trigger ('eng','BKS'); CREATE TRIGGER aaa_indexing_ingest_or_delete AFTER INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE biblio.indexing_ingest_or_delete (); -CREATE TRIGGER bbb_simple_rec_trigger AFTER INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE reporter.simple_rec_trigger (); +CREATE TRIGGER bbb_simple_rec_trigger AFTER INSERT OR UPDATE OR DELETE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE reporter.simple_rec_trigger (); CREATE TRIGGER aaa_auth_ingest_or_delete AFTER INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE authority.indexing_ingest_or_delete (); diff --git a/Open-ILS/src/sql/Pg/reporter-schema.sql b/Open-ILS/src/sql/Pg/reporter-schema.sql index 806b8fdfab..d8f614ebe7 100644 --- a/Open-ILS/src/sql/Pg/reporter-schema.sql +++ b/Open-ILS/src/sql/Pg/reporter-schema.sql @@ -208,55 +208,8 @@ BEGIN END; $func$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION reporter.simple_rec_sync () RETURNS TRIGGER AS $$ -DECLARE - r_id BIGINT; - deleted BOOL; -BEGIN - IF TG_OP IN ('DELETE') THEN - r_id := OLD.record; - deleted := TRUE; - ELSE - r_id := NEW.record; - deleted := FALSE; - END IF; - - PERFORM reporter.simple_rec_update(r_id, deleted); - - IF deleted THEN - RETURN OLD; - ELSE - RETURN NEW; - END IF; - -END; -$$ LANGUAGE PLPGSQL; - --- --- Disabling this by default for now, but we'll keep it around --- ---CREATE TRIGGER zzz_update_materialized_simple_record_tgr --- AFTER INSERT OR UPDATE OR DELETE ON metabib.real_full_rec --- FOR EACH ROW EXECUTE PROCEDURE reporter.simple_rec_sync(); - -CREATE OR REPLACE FUNCTION reporter.simple_rec_bib_sync () RETURNS TRIGGER AS $$ -BEGIN - IF NEW.deleted THEN - DELETE FROM reporter.materialized_simple_record WHERE id = NEW.id; - RETURN NEW; - ELSE - RETURN NEW; - END IF; -END; -$$ LANGUAGE PLPGSQL; - ---CREATE TRIGGER zzz_update_materialized_simple_rec_delete_tgr --- AFTER UPDATE ON biblio.record_entry --- FOR EACH ROW EXECUTE PROCEDURE reporter.simple_rec_bib_sync(); - - CREATE OR REPLACE FUNCTION reporter.disable_materialized_simple_record_trigger () RETURNS VOID AS $$ - DROP TRIGGER IF EXISTS zzz_update_materialized_simple_record_tgr ON metabib.real_full_rec; + DROP TRIGGER IF EXISTS bbb_simple_rec_trigger ON biblio.record_entry; $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION reporter.enable_materialized_simple_record_trigger () RETURNS VOID AS $$ @@ -267,9 +220,9 @@ CREATE OR REPLACE FUNCTION reporter.enable_materialized_simple_record_trigger () (id,fingerprint,quality,tcn_source,tcn_value,title,author,publisher,pubdate,isbn,issn) SELECT DISTINCT ON (id) * FROM reporter.old_super_simple_record; - CREATE TRIGGER zzz_update_materialized_simple_record_tgr - AFTER INSERT OR UPDATE OR DELETE ON metabib.real_full_rec - FOR EACH ROW EXECUTE PROCEDURE reporter.simple_rec_sync(); + CREATE TRIGGER bbb_simple_rec_trigger + AFTER INSERT OR UPDATE OR DELETE ON biblio.record_entry + FOR EACH ROW EXECUTE PROCEDURE reporter.simple_rec_trigger(); $$ LANGUAGE SQL; diff --git a/Open-ILS/src/sql/Pg/upgrade/0468.schema.simple_rec_triggers.sql b/Open-ILS/src/sql/Pg/upgrade/0468.schema.simple_rec_triggers.sql new file mode 100644 index 0000000000..13e982b9d5 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0468.schema.simple_rec_triggers.sql @@ -0,0 +1,34 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0468'); -- gmc + +DROP TRIGGER IF EXISTS zzz_update_materialized_simple_record_tgr ON metabib.real_full_rec; +DROP TRIGGER IF EXISTS zzz_update_materialized_simple_rec_delete_tgr ON biblio.record_entry; +DROP TRIGGER IF EXISTS bbb_simple_rec_trigger ON biblio.record_entry; + +DROP FUNCTION IF EXISTS reporter.simple_rec_sync(); +DROP FUNCTION IF EXISTS reporter.simple_rec_bib_sync(); + +CREATE TRIGGER bbb_simple_rec_trigger + AFTER INSERT OR UPDATE OR DELETE ON biblio.record_entry + FOR EACH ROW EXECUTE PROCEDURE reporter.simple_rec_trigger(); + +CREATE OR REPLACE FUNCTION reporter.disable_materialized_simple_record_trigger () RETURNS VOID AS $$ + DROP TRIGGER IF EXISTS bbb_simple_rec_trigger ON biblio.record_entry; +$$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION reporter.enable_materialized_simple_record_trigger () RETURNS VOID AS $$ + + DELETE FROM reporter.materialized_simple_record; + + INSERT INTO reporter.materialized_simple_record + (id,fingerprint,quality,tcn_source,tcn_value,title,author,publisher,pubdate,isbn,issn) + SELECT DISTINCT ON (id) * FROM reporter.old_super_simple_record; + + CREATE TRIGGER bbb_simple_rec_trigger + AFTER INSERT OR UPDATE OR DELETE ON biblio.record_entry + FOR EACH ROW EXECUTE PROCEDURE reporter.simple_rec_trigger(); + +$$ LANGUAGE SQL; + +COMMIT; -- 2.11.0