From 7f061a5e02bae1010a0a61199fc3f6529c7b5af9 Mon Sep 17 00:00:00 2001 From: Jane Sandberg Date: Mon, 8 May 2023 19:43:04 -0700 Subject: [PATCH] LP2011434 (follow-up): use NOT VALID / VALIDATE CONSTRAINT pattern to minimize locking on action.hold_request Also simplify the WHERE clause in the UPDATE statement, making its intentions clearer to my eye at least Signed-off-by: Jane Sandberg --- .../sql/Pg/upgrade/xxxx.schema.function-selection-ou-fkey.sql | 9 ++++----- 1 file changed, 4 insertions(+), 5 deletions(-) diff --git a/Open-ILS/src/sql/Pg/upgrade/xxxx.schema.function-selection-ou-fkey.sql b/Open-ILS/src/sql/Pg/upgrade/xxxx.schema.function-selection-ou-fkey.sql index 2c8d2ee35d..e4b6c71802 100644 --- a/Open-ILS/src/sql/Pg/upgrade/xxxx.schema.function-selection-ou-fkey.sql +++ b/Open-ILS/src/sql/Pg/upgrade/xxxx.schema.function-selection-ou-fkey.sql @@ -4,12 +4,11 @@ SELECT evergreen.upgrade_deps_block_check('xxxx', :eg_version); UPDATE action.hold_request SET selection_ou = request_lib -WHERE id IN ( - SELECT ahr.id FROM action.hold_request ahr - LEFT JOIN actor.org_unit aou ON aou.id = ahr.selection_ou - WHERE aou.id IS NULL +WHERE selection_ou NOT IN ( + SELECT id FROM actor.org_unit ); -ALTER TABLE action.hold_request ADD CONSTRAINT hold_request_selection_ou_fkey FOREIGN KEY (selection_ou) REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE action.hold_request ADD CONSTRAINT hold_request_selection_ou_fkey FOREIGN KEY (selection_ou) REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED NOT VALID; +ALTER TABLE action.hold_request VALIDATE CONSTRAINT hold_request_selection_ou_fkey; COMMIT; -- 2.11.0