From f209a46de5455aa923058af471f47d60139dee57 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Fri, 2 Feb 2018 09:04:22 -0800 Subject: [PATCH] JBAS-1804 2.12 SQL upgrade testing additions DROP tablefunc as suggested by script. Avoid creating the full reporter.hold_request_table twice. Avoid creating various user name indexes twice. Adds some more timing logs. Signed-off-by: Bill Erickson --- KCLS/sql/schema/deploy/2.10-to-2.12-upgrade.sql | 29 +++++++++++++++++++++---- 1 file changed, 25 insertions(+), 4 deletions(-) diff --git a/KCLS/sql/schema/deploy/2.10-to-2.12-upgrade.sql b/KCLS/sql/schema/deploy/2.10-to-2.12-upgrade.sql index 9314e5bee2..0d188606f9 100644 --- a/KCLS/sql/schema/deploy/2.10-to-2.12-upgrade.sql +++ b/KCLS/sql/schema/deploy/2.10-to-2.12-upgrade.sql @@ -386,6 +386,11 @@ $$ LANGUAGE 'plpgsql' ROWS 1; \qecho want to run the following command in the database to drop it: \qecho DROP EXTENSION tablefunc; +SELECT 'dropping tablefunc'; + +DROP EXTENSION tablefunc; + +SELECT 'done dropping tablefunc'; -- DEPS 0983 @@ -440,6 +445,7 @@ CREATE TEMP TABLE precalc_bib_list ( -- rhrr needs to be a real table, so it can be fast. To that end, we use -- a materialized view updated via a trigger. +SELECT 'Dropping hold req view'; DROP VIEW reporter.hold_request_record; CREATE TABLE reporter.hold_request_record AS @@ -460,13 +466,23 @@ SELECT id, WHEN hold_type = 'P' THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = ahr.target) END AS bib_record - FROM action.hold_request ahr; + FROM action.hold_request ahr + LIMIT 1; -- TABLE WILL BE REBUILT WITH FULL DATA BELOW! + +SELECT 'Creating hold indexes'; CREATE UNIQUE INDEX reporter_hold_request_record_pkey_idx ON reporter.hold_request_record (id); CREATE INDEX reporter_hold_request_record_bib_record_idx ON reporter.hold_request_record (bib_record); ALTER TABLE reporter.hold_request_record ADD PRIMARY KEY USING INDEX reporter_hold_request_record_pkey_idx; +-- Added by Bill to help keep this table clean +ALTER TABLE reporter.hold_request_record + ADD CONSTRAINT ahrfk FOREIGN KEY (id) + REFERENCES action.hold_request (id) ON DELETE CASCADE; + +SELECT 'Done creating hold indexes'; + CREATE FUNCTION reporter.hold_request_record_mapper () RETURNS TRIGGER AS $$ BEGIN IF TG_OP = 'INSERT' THEN @@ -2435,9 +2451,10 @@ CREATE OR REPLACE FUNCTION evergreen.unaccent_and_squash ( IN arg text) RETURNS $$ LANGUAGE PLPGSQL; -- The unaccented indices for patron name fields -CREATE INDEX actor_usr_first_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(first_given_name)); -CREATE INDEX actor_usr_second_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(second_given_name)); -CREATE INDEX actor_usr_family_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(family_name)); +-- THESE ARE RECREATED BELOW +-- CREATE INDEX actor_usr_first_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(first_given_name)); +-- CREATE INDEX actor_usr_second_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(second_given_name)); +-- CREATE INDEX actor_usr_family_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(family_name)); -- DB setting to control behavior; true by default DO $INSERT$ @@ -3473,6 +3490,10 @@ REINDEX TABLE reporter.hold_request_record; ANALYZE reporter.hold_request_record; +-- TEST LOGS +SELECT COUNT(*) FROM reporter.hold_request_record; +SELECT COUNT(DISTINCT(bib_record)) FROM reporter.hold_request_record; + SELECT 'Done with reporter.hold_request_record rebuild: ', CLOCK_TIMESTAMP(); -- 2.11.0