/* * 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;