From bad899b9927b1803e93471163421429f977444d5 Mon Sep 17 00:00:00 2001 From: Galen Charlton Date: Wed, 23 Sep 2020 11:48:58 -0400 Subject: [PATCH] LP#1786100: fix upgrade issue that can prevent unmapping item tags Depending on the history of an Evergreen database, it might have an incorrect trigger on asset.copy_tag_copy_map that prevents removing item tags fom items. This patch ensures that the table has the correct trigger. To test (cannot remove item tags) --------------------------------- [1] If you have a database from which item tags cannot be removed on items, check the asset.copy_tag_copy_map table to see if it has a trigger named inherit_copy_tag_copy_map_copy_fkey that fires on insert, update, or delete. If so, this part of the test plan applies to you. [2] Make a copy of the database. [3] Note that item tags cannot be unassigned from items in the holdings editor: saving a change will appear to stick, but opening the item back in the editor shows that the item tag is still there. [4] Apply the patch and run the schema update. Note that there be just one inh_fkey trigger named inherit_asset_copy_tag_copy_map_copy_fkey that fires only on insert or update. [5] Repeate step 3; this time, items tags can be removed from items. To test (can remove item tags) ------------------------------ [1] If your test system _can_ remove item tags, as would be the case with ones created from scratch, apply the patch and verify that item tags can be added or removed from items. Signed-off-by: Galen Charlton Signed-off-by: Rogan Hamby Signed-off-by: Galen Charlton --- .../upgrade/XXXX.schema.fix_actcm_inh_fkey_trigger.sql | 16 ++++++++++++++++ 1 file changed, 16 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fix_actcm_inh_fkey_trigger.sql diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fix_actcm_inh_fkey_trigger.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fix_actcm_inh_fkey_trigger.sql new file mode 100644 index 0000000000..987e2b889c --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fix_actcm_inh_fkey_trigger.sql @@ -0,0 +1,16 @@ +BEGIN; + +-- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +-- In some cases, asset.copy_tag_copy_map might have an inh_fkey() +-- trigger that fires on delete when it's not supposed to. This +-- update drops all inh_fkey triggers on that table and recreates +-- the known good version. +DROP TRIGGER IF EXISTS inherit_asset_copy_tag_copy_map_copy_fkey ON asset.copy_tag_copy_map; +DROP TRIGGER IF EXISTS inherit_copy_tag_copy_map_copy_fkey ON asset.copy_tag_copy_map; + +CREATE CONSTRAINT TRIGGER inherit_asset_copy_tag_copy_map_copy_fkey + AFTER UPDATE OR INSERT ON asset.copy_tag_copy_map + DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.asset_copy_tag_copy_map_copy_inh_fkey(); + +COMMIT; -- 2.11.0