From c985333684695d0091f633fa04d7e85944de64d7 Mon Sep 17 00:00:00 2001 From: miker Date: Wed, 13 Aug 2008 01:06:40 +0000 Subject: [PATCH] Moving metabib.full_rec out of the way, replacing it with a suitably constrained and ruled-up view. This addresses an indexing issue caused by the length of values stored in the, um, value column. The purpose is to allow extremely large note and ToC fields without adding explicit support for over-long indexed fields directly to Postgres. While possible, it can be a pain. git-svn-id: svn://svn.open-ils.org/ILS/trunk@10344 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- .../src/extras/import/parallel_pg_loader.pl | 3 + Open-ILS/src/extras/import/pg_loader.pl | 3 + Open-ILS/src/sql/Pg/030.schema.metabib.sql | 63 +++++++++-- .../sql/Pg/1.4-shadow_full_rec-upgade-db.sql | 100 ++++++++++++++++++ Open-ILS/src/sql/Pg/800.fkeys.sql | 2 +- Open-ILS/src/sql/Pg/reporter-schema.sql | 6 +- 6 files changed, 165 insertions(+), 12 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/1.4-shadow_full_rec-upgade-db.sql diff --git a/Open-ILS/src/extras/import/parallel_pg_loader.pl b/Open-ILS/src/extras/import/parallel_pg_loader.pl index 5d16f4b126..5e586aa1df 100755 --- a/Open-ILS/src/extras/import/parallel_pg_loader.pl +++ b/Open-ILS/src/extras/import/parallel_pg_loader.pl @@ -80,6 +80,9 @@ while ( my $rec = <> ) { fields => \@cols, }; + #XXX it burnnnsssessss + $fieldcache{$hint}{table} =~ s/\.full_rec/.real_full_rec/o if ($hint eq 'mfr'); + my $fields = join(',', @{ $fieldcache{$hint}{fields} }); $main_out->print( "DELETE FROM $fieldcache{$hint}{table};\n" ) if (grep {$_ eq $hint } @wipe); # Speed up loading of bib records diff --git a/Open-ILS/src/extras/import/pg_loader.pl b/Open-ILS/src/extras/import/pg_loader.pl index ef07a05ce0..25b540ff2a 100755 --- a/Open-ILS/src/extras/import/pg_loader.pl +++ b/Open-ILS/src/extras/import/pg_loader.pl @@ -62,6 +62,9 @@ while ( my $rec = <> ) { pkey => $class->Identity, fields => \@cols, }; + + #XXX it burnnnsssessss + $fieldcache{$hint}{table} =~ s/\.full_rec/.real_full_rec/o if ($hint eq 'mfr'); } push @{ $lineset{$hint} }, [map { $row->$_ } @{ $fieldcache{$hint}{fields} }]; diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql index a57f30579d..45de8934fb 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -136,9 +136,11 @@ CREATE INDEX metabib_rec_descriptor_vr_format_idx ON metabib.rec_descriptor (vr_ */ +-- Use a sequence that matches previous version, for easier upgrading. +CREATE SEQUENCE metabib.full_rec_id_seq; -CREATE TABLE metabib.full_rec ( - id BIGSERIAL PRIMARY KEY, +CREATE TABLE metabib.real_full_rec ( + id BIGINT NOT NULL DEFAULT NEXTVAL('metabib.full_rec_id_seq'::REGCLASS), record BIGINT NOT NULL, tag CHAR(3) NOT NULL, ind1 TEXT, @@ -147,15 +149,60 @@ CREATE TABLE metabib.full_rec ( value TEXT NOT NULL, index_vector tsvector NOT NULL ); -CREATE INDEX metabib_full_rec_tag_subfield_idx ON metabib.full_rec (tag,subfield); ---CREATE INDEX metabib_full_rec_value_idx ON metabib.full_rec (value); -CREATE INDEX metabib_full_rec_record_idx ON metabib.full_rec (record); +ALTER TABLE metabib.real_full_rec ADD PRIMARY KEY (id); + +CREATE INDEX metabib_full_rec_tag_subfield_idx ON metabib.real_full_rec (tag,subfield); +CREATE INDEX metabib_full_rec_value_idx ON metabib.real_full_rec (substring(value,1,1024)); +CREATE INDEX metabib_full_rec_record_idx ON metabib.real_full_rec (record); +CREATE INDEX metabib_full_rec_index_vector_idx ON metabib.real_full_rec USING GIST (index_vector); + CREATE TRIGGER metabib_full_rec_fti_trigger - BEFORE UPDATE OR INSERT ON metabib.full_rec + BEFORE UPDATE OR INSERT ON metabib.real_full_rec FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('default'); -CREATE INDEX metabib_full_rec_index_vector_idx ON metabib.full_rec USING GIST (index_vector); - +CREATE OR REPLACE VIEW metabib.full_rec AS + SELECT id, + record, + tag, + ind1, + ind2, + subfield, + SUBSTRING(value,1,1024) AS value, + index_vector + FROM metabib.real_full_rec; + +CREATE OR REPLACE RULE metabib_full_rec_insert_rule + AS ON INSERT TO metabib.full_rec + DO INSTEAD + INSERT INTO metabib.real_full_rec VALUES ( + COALESCE(NEW.id, NEXTVAL('metabib.full_rec_id_seq'::REGCLASS)), + NEW.record, + NEW.tag, + NEW.ind1, + NEW.ind2, + NEW.subfield, + NEW.value, + NEW.index_vector + ); + +CREATE OR REPLACE RULE metabib_full_rec_update_rule + AS ON UPDATE TO metabib.full_rec + DO INSTEAD + UPDATE metabib.real_full_rec SET + id = NEW.id, + record = NEW.record, + tag = NEW.tag, + ind1 = NEW.ind1, + ind2 = NEW.ind2, + subfield = NEW.subfield, + value = NEW.value, + index_vector = NEW.index_vector + WHERE id = OLD.id; + +CREATE OR REPLACE RULE metabib_full_rec_delete_rule + AS ON DELETE TO metabib.full_rec + DO INSTEAD + DELETE FROM metabib.real_full_rec WHERE id = OLD.id; CREATE TABLE metabib.metarecord_source_map ( id BIGSERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/1.4-shadow_full_rec-upgade-db.sql b/Open-ILS/src/sql/Pg/1.4-shadow_full_rec-upgade-db.sql new file mode 100644 index 0000000000..bf449345e2 --- /dev/null +++ b/Open-ILS/src/sql/Pg/1.4-shadow_full_rec-upgade-db.sql @@ -0,0 +1,100 @@ +/* + * Copyright (C) 2008 Equinox Software, Inc. + * Mike Rylander + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU General Public License + * as published by the Free Software Foundation; either version 2 + * of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + */ + + +BEGIN; + +-- To avoid any updates while we're doin' our thing... +SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; + +-- This index, right here, is the reason for this change. +DROP INDEX metabib.metabib_full_rec_value_idx; + +-- So, on to it. +-- Move the table out of the way ... +ALTER TABLE metabib.full_rec RENAME TO real_full_rec; + +-- ... and let the trigger management functions know about the change ... +CREATE OR REPLACE FUNCTION reporter.disable_materialized_simple_record_trigger () RETURNS VOID AS $$ + DROP TRIGGER zzz_update_materialized_simple_record_tgr ON metabib.real_full_rec; +$$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION reporter.enable_materialized_simple_record_trigger () RETURNS VOID AS $$ + + TRUNCATE TABLE 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 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(); + +$$ LANGUAGE SQL; + +-- ... replace the table with a suitable view, which applies the index contstraint we'll use ... +CREATE OR REPLACE VIEW metabib.full_rec AS + SELECT id, + record, + tag, + ind1, + ind2, + subfield, + SUBSTRING(value,1,1024) AS value, + index_vector + FROM metabib.real_full_rec; + +-- ... now some rules to transform DML against the view into DML against the underlying table ... +CREATE OR REPLACE RULE metabib_full_rec_insert_rule + AS ON INSERT TO metabib.full_rec + DO INSTEAD + INSERT INTO metabib.real_full_rec VALUES ( + COALESCE(NEW.id, NEXTVAL('metabib.full_rec_id_seq'::REGCLASS)), + NEW.record, + NEW.tag, + NEW.ind1, + NEW.ind2, + NEW.subfield, + NEW.value, + NEW.index_vector + ); + +CREATE OR REPLACE RULE metabib_full_rec_update_rule + AS ON UPDATE TO metabib.full_rec + DO INSTEAD + UPDATE metabib.real_full_rec SET + id = NEW.id, + record = NEW.record, + tag = NEW.tag, + ind1 = NEW.ind1, + ind2 = NEW.ind2, + subfield = NEW.subfield, + value = NEW.value, + index_vector = NEW.index_vector + WHERE id = OLD.id; + +CREATE OR REPLACE RULE metabib_full_rec_delete_rule + AS ON DELETE TO metabib.full_rec + DO INSTEAD + DELETE FROM metabib.real_full_rec WHERE id = OLD.id; + +-- ... and last, but not least, create a fore-shortened index on the value column. +CREATE INDEX metabib_full_rec_value_idx ON metabib.real_full_rec (substring(value,1,1024)); + +-- Wheeee... +COMMIT; + diff --git a/Open-ILS/src/sql/Pg/800.fkeys.sql b/Open-ILS/src/sql/Pg/800.fkeys.sql index 2679d69b69..baa918589f 100644 --- a/Open-ILS/src/sql/Pg/800.fkeys.sql +++ b/Open-ILS/src/sql/Pg/800.fkeys.sql @@ -60,7 +60,7 @@ ALTER TABLE metabib.keyword_field_entry ADD CONSTRAINT metabib_keyword_field_ent ALTER TABLE metabib.rec_descriptor ADD CONSTRAINT metabib_rec_descriptor_record_fkey FOREIGN KEY (record) REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE metabib.full_rec ADD CONSTRAINT metabib_full_rec_record_fkey FOREIGN KEY (record) REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE metabib.real_full_rec ADD CONSTRAINT metabib_full_rec_record_fkey FOREIGN KEY (record) REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; ALTER TABLE metabib.metarecord_source_map ADD CONSTRAINT metabib_metarecord_source_map_source_fkey FOREIGN KEY (source) REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; ALTER TABLE metabib.metarecord_source_map ADD CONSTRAINT metabib_metarecord_source_map_metarecord_fkey FOREIGN KEY (metarecord) REFERENCES metabib.metarecord (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; diff --git a/Open-ILS/src/sql/Pg/reporter-schema.sql b/Open-ILS/src/sql/Pg/reporter-schema.sql index f2101ef96e..27a7aa2f3c 100644 --- a/Open-ILS/src/sql/Pg/reporter-schema.sql +++ b/Open-ILS/src/sql/Pg/reporter-schema.sql @@ -199,11 +199,11 @@ END; $$ LANGUAGE PLPGSQL; CREATE TRIGGER zzz_update_materialized_simple_record_tgr - AFTER INSERT OR UPDATE OR DELETE ON metabib.full_rec + 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.disable_materialized_simple_record_trigger () RETURNS VOID AS $$ - DROP TRIGGER zzz_update_materialized_simple_record_tgr ON metabib.full_rec; + DROP TRIGGER zzz_update_materialized_simple_record_tgr ON metabib.real_full_rec; $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION reporter.enable_materialized_simple_record_trigger () RETURNS VOID AS $$ @@ -215,7 +215,7 @@ CREATE OR REPLACE FUNCTION reporter.enable_materialized_simple_record_trigger () 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.full_rec + AFTER INSERT OR UPDATE OR DELETE ON metabib.real_full_rec FOR EACH ROW EXECUTE PROCEDURE reporter.simple_rec_sync(); $$ LANGUAGE SQL; -- 2.43.2