From 21581210e023cd399ed54d0117b3c7b29bb0960c Mon Sep 17 00:00:00 2001 From: Jason Stephenson Date: Sun, 14 Nov 2021 13:47:37 -0500 Subject: [PATCH] Lp1883171&1940663: Replace latest inventory view with rollup table Use a rollup table, what our community calls a materialized view, for the asset.latest_inventory data. This should be faster in large data environmnets than a traditional view. Signed-off-by: Jason Stephenson --- Open-ILS/src/sql/Pg/040.schema.asset.sql | 94 +++++++++++++++++++- .../upgrade/XXXX.schema.actor.copy_inventory.sql | 99 +++++++++++++++++++++- 2 files changed, 185 insertions(+), 8 deletions(-) diff --git a/Open-ILS/src/sql/Pg/040.schema.asset.sql b/Open-ILS/src/sql/Pg/040.schema.asset.sql index 8f19c8f99c..d1b33b1663 100644 --- a/Open-ILS/src/sql/Pg/040.schema.asset.sql +++ b/Open-ILS/src/sql/Pg/040.schema.asset.sql @@ -159,10 +159,96 @@ CREATE CONSTRAINT TRIGGER asset_copy_inventory_allowed_trig AFTER UPDATE OR INSERT ON asset.copy_inventory DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE asset.copy_may_float_to_inventory_workstation(); -CREATE VIEW asset.latest_inventory (id, inventory_workstation, inventory_date, copy) AS -SELECT DISTINCT ON (copy) id, inventory_workstation, inventory_date, copy -FROM asset.copy_inventory -ORDER BY copy, inventory_date DESC; +CREATE TABLE asset.latest_inventory ( + id BIGINT PRIMARY KEY, + inventory_workstation INTEGER REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED, + inventory_date TIMESTAMP WITH TIME ZONE NOT NULL, + copy BIGINT NOT NULL +); +CREATE UNIQUE INDEX asset_latest_inventory_once_per_copy ON asset.latest_inventory (copy); + +CREATE OR REPLACE FUNCTION asset.copy_inventory_latest_inventory_rollup() RETURNS TRIGGER AS $rollup$ +DECLARE + old_alci asset.latest_inventory%ROWTYPE; + old_aci asset.copy_inventory%ROWTYPE; +BEGIN +IF (TG_OP = 'TRUNCATE') THEN + TRUNCATE asset.latest_inventory; +ELSIF (TG_OP = 'INSERT') THEN + INSERT INTO asset.latest_inventory + (id, inventory_workstation, inventory_date, copy) + VALUES + (NEW.id, NEW.inventory_workstation, NEW.inventory_date, NEW.copy) + ON CONFLICT (copy) DO UPDATE + SET id = NEW.id, inventory_workstation = NEW.inventory_workstation, + inventory_date = NEW.inventory_date + WHERE latest_inventory.copy = NEW.copy; +ELSE + SELECT * INTO old_alci FROM asset.latest_inventory WHERE id = OLD.id; + IF FOUND THEN + IF (TG_OP = 'DELETE') THEN + SELECT * INTO old_aci + FROM asset.copy_inventory + WHERE inventory_date < OLD.inventory_date + AND copy = OLD.copy + ORDER BY inventory_date DESC + LIMIT 1; + IF FOUND THEN + UPDATE asset.latest_inventory + SET id = old_aci.id, inventory_workstation = old_aci.inventory_workstation, + inventory_date = old_aci.inventory_date + WHERE copy = old_aci.copy; + ELSE + DELETE FROM asset.latest_inventory WHERE id = OLD.id; + END IF; + ELSE + BEGIN + UPDATE asset.latest_inventory + SET inventory_workstation = NEW.inventory_workstation, + inventory_date = NEW.inventory_date, + copy = NEW.copy + WHERE id = NEW.id; + EXCEPTION + WHEN unique_violation THEN + SELECT * INTO old_aci + FROM asset.copy_inventory + WHERE copy = OLD.copy + AND inventory_date <> NEW.inventory_date + ORDER BY inventory_date DESC + LIMIT 1; + IF FOUND THEN + UPDATE asset.latest_inventory + SET id = old_aci.id, inventory_workstation = old_aci.inventory_workstation, + inventory_date = old_aci.inventory_date + WHERE copy = old_aci.copy; + ELSE + DELETE FROM asset.latest_inventory WHERE copy = OLD.copy; + END IF; + + SELECT * INTO old_alci + FROM asset.latest_inventory + WHERE copy = NEW.copy; + IF (old_alci.inventory_date < NEW.inventory_date) THEN + UPDATE asset.latest_inventory + SET id = NEW.id, inventory_workstation = NEW.inventory_workstation, + inventory_date = NEW.inventory_date + WHERE copy = NEW.copy; + END IF; + END; + END IF; + END IF; +END IF; +RETURN NULL; +END; +$rollup$ LANGUAGE PLPGSQL; + +CREATE TRIGGER asset_copy_inventory_rollup_trig + AFTER INSERT OR UPDATE OR DELETE ON asset.copy_inventory + FOR EACH ROW EXECUTE PROCEDURE asset.copy_inventory_latest_inventory_rollup(); + +CREATE TRIGGER asset_copy_inventory_truncate_trig + AFTER TRUNCATE ON asset.copy_inventory + EXECUTE PROCEDURE asset.copy_inventory_latest_inventory_rollup(); CREATE OR REPLACE FUNCTION asset.acp_status_changed() RETURNS TRIGGER AS $$ diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.actor.copy_inventory.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.actor.copy_inventory.sql index ff6af4544a..8e2cc14b0c 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.actor.copy_inventory.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.actor.copy_inventory.sql @@ -76,11 +76,102 @@ ORDER by inventory_date; DROP TABLE asset.latest_inventory; -CREATE VIEW asset.latest_inventory (id, inventory_workstation, inventory_date, copy) AS -SELECT DISTINCT ON (copy) id, inventory_workstation, inventory_date, copy +DROP FUNCTION evergreen.asset_latest_inventory_copy_inh_fkey(); + +CREATE TABLE asset.latest_inventory ( + id BIGINT PRIMARY KEY, + inventory_workstation INTEGER REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED, + inventory_date TIMESTAMP WITH TIME ZONE NOT NULL, + copy BIGINT NOT NULL +); +CREATE UNIQUE INDEX asset_latest_inventory_once_per_copy ON asset.latest_inventory (copy); + +CREATE OR REPLACE FUNCTION asset.copy_inventory_latest_inventory_rollup() RETURNS TRIGGER AS $rollup$ +DECLARE + old_alci asset.latest_inventory%ROWTYPE; + old_aci asset.copy_inventory%ROWTYPE; +BEGIN +IF (TG_OP = 'TRUNCATE') THEN + TRUNCATE asset.latest_inventory; +ELSIF (TG_OP = 'INSERT') THEN + INSERT INTO asset.latest_inventory + (id, inventory_workstation, inventory_date, copy) + VALUES + (NEW.id, NEW.inventory_workstation, NEW.inventory_date, NEW.copy) + ON CONFLICT (copy) DO UPDATE + SET id = NEW.id, inventory_workstation = NEW.inventory_workstation, + inventory_date = NEW.inventory_date + WHERE latest_inventory.copy = NEW.copy; +ELSE + SELECT * INTO old_alci FROM asset.latest_inventory WHERE id = OLD.id; + IF FOUND THEN + IF (TG_OP = 'DELETE') THEN + SELECT * INTO old_aci + FROM asset.copy_inventory + WHERE inventory_date < OLD.inventory_date + AND copy = OLD.copy + ORDER BY inventory_date DESC + LIMIT 1; + IF FOUND THEN + UPDATE asset.latest_inventory + SET id = old_aci.id, inventory_workstation = old_aci.inventory_workstation, + inventory_date = old_aci.inventory_date + WHERE copy = old_aci.copy; + ELSE + DELETE FROM asset.latest_inventory WHERE id = OLD.id; + END IF; + ELSE + BEGIN + UPDATE asset.latest_inventory + SET inventory_workstation = NEW.inventory_workstation, + inventory_date = NEW.inventory_date, + copy = NEW.copy + WHERE id = NEW.id; + EXCEPTION + WHEN unique_violation THEN + SELECT * INTO old_aci + FROM asset.copy_inventory + WHERE copy = OLD.copy + AND inventory_date <> NEW.inventory_date + ORDER BY inventory_date DESC + LIMIT 1; + IF FOUND THEN + UPDATE asset.latest_inventory + SET id = old_aci.id, inventory_workstation = old_aci.inventory_workstation, + inventory_date = old_aci.inventory_date + WHERE copy = old_aci.copy; + ELSE + DELETE FROM asset.latest_inventory WHERE copy = OLD.copy; + END IF; + + SELECT * INTO old_alci + FROM asset.latest_inventory + WHERE copy = NEW.copy; + IF (old_alci.inventory_date < NEW.inventory_date) THEN + UPDATE asset.latest_inventory + SET id = NEW.id, inventory_workstation = NEW.inventory_workstation, + inventory_date = NEW.inventory_date + WHERE copy = NEW.copy; + END IF; + END; + END IF; + END IF; +END IF; +RETURN NULL; +END; +$rollup$ LANGUAGE PLPGSQL; + +CREATE TRIGGER asset_copy_inventory_rollup_trig + AFTER INSERT OR UPDATE OR DELETE ON asset.copy_inventory + FOR EACH ROW EXECUTE PROCEDURE asset.copy_inventory_latest_inventory_rollup(); + +CREATE TRIGGER asset_copy_inventory_truncate_trig + AFTER TRUNCATE ON asset.copy_inventory + EXECUTE PROCEDURE asset.copy_inventory_latest_inventory_rollup(); + +INSERT INTO asset.latest_inventory +SELECT distinct on (copy) * FROM asset.copy_inventory ORDER BY copy, inventory_date DESC; -DROP FUNCTION evergreen.asset_latest_inventory_copy_inh_fkey(); - COMMIT; -- 2.11.0