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 <gmc@esilibrary.com>

git-svn-id: svn://svn.open-ils.org/ILS/trunk@18869 dcc99617-32d9-48b4-a31d-7c20da2025e4

diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql
index 7fe22b4..22e3e08 100644 (file)
--- 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 16139ed..6164952 100644 (file)
--- 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 ba4f373..865d552 100644 (file)
--- 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 806b8fd..d8f614e 100644 (file)
--- 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 (file)
index 0000000..13e982b
--- /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;