From 300acbf91ea60ddd3fc9e76eecb6aac7f64e98fe Mon Sep 17 00:00:00 2001 From: Jason Stephenson Date: Thu, 24 Sep 2020 12:31:23 -0400 Subject: [PATCH] LP#1482757: Speed Up the Delete of Orphaned URIs in upgrade script Modify the code that deletes orphaned asset.uri table entries in the upgrade script to use IN logic on the subquery, because IN is much faster than NOT IN. Also take into account the link between serial.item and asset.uri so that we don't accidentally delete URIs used by serial items. Signed-off-by: Jason Stephenson Signed-off-by: Jessica Woolford Signed-off-by: Michele Morgan --- .../Pg/upgrade/XXXX.function.biblio.extract_located_uris.sql | 12 +++++++++--- 1 file changed, 9 insertions(+), 3 deletions(-) diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.function.biblio.extract_located_uris.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.biblio.extract_located_uris.sql index 2e82c2e2a4..edcb152494 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.function.biblio.extract_located_uris.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.biblio.extract_located_uris.sql @@ -147,10 +147,16 @@ $func$ LANGUAGE PLPGSQL; -- Remove existing orphaned URIs from the database. DELETE FROM asset.uri -WHERE id NOT IN +WHERE id IN ( -SELECT uri -FROM asset.uri_call_number_map +SELECT uri.id +FROM asset.uri +LEFT JOIN asset.uri_call_number_map +ON uri_call_number_map.uri = uri.id +LEFT JOIN serial.item +ON item.uri = uri.id +WHERE uri_call_number_map IS NULL +AND item IS NULL ); COMMIT; -- 2.11.0