From 45d30ad4edb23c8aa92a75b8f3088b9d9a8b3a6c Mon Sep 17 00:00:00 2001 From: Galen Charlton Date: Fri, 17 Mar 2017 11:47:46 -0400 Subject: [PATCH] LP#1673799: new acqedim index to speed up duplicate file check This patch adds a new index on acq.edi_message to speed up the check for duplicate EDI messages that edi_fetcher.pl runs, replacing use of "ilike" with invocations of the evergreen.lowercase() stored procedure. To test ------- [1] Arrange to create or simulate an EDI message that failed processing. [2] Run edi_fetcher.pl to have it attempt to download the failed message in step #1; verify that the file is /not/ downloaded again and that no additonal acq.edi_message rows are created for it. [3] (Extra credit) Try steps 1 and 2 in a database that has a very large number of rows in acq.edi_message. Signed-off-by: Galen Charlton Signed-off-by: Bill Erickson --- Open-ILS/src/perlmods/lib/OpenILS/Application/Acq/EDI.pm | 5 ++++- Open-ILS/src/sql/Pg/200.schema.acq.sql | 1 + .../upgrade/XXXX.schema.index_edi_message_remote_file.sql | 7 +++++++ 3 files changed, 12 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.index_edi_message_remote_file.sql diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Acq/EDI.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Acq/EDI.pm index 8676a6bdb1..49feb96098 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Acq/EDI.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Acq/EDI.pm @@ -135,7 +135,10 @@ sub retrieve_core { password => $account->password, in_dir => $account->in_dir }, - remote_file => {ilike => $remote_file}, + remote_file => {'=' => { + transform => 'evergreen.lowercase', + value => ['evergreen.lowercase', $remote_file] + }}, status => {'in' => [qw/ processed proc_error trans_error /]}, }, { join => {"acqedi" => {}}, limit => 1 } diff --git a/Open-ILS/src/sql/Pg/200.schema.acq.sql b/Open-ILS/src/sql/Pg/200.schema.acq.sql index 309e80f1ca..6391e68bc9 100644 --- a/Open-ILS/src/sql/Pg/200.schema.acq.sql +++ b/Open-ILS/src/sql/Pg/200.schema.acq.sql @@ -786,6 +786,7 @@ CREATE TABLE acq.edi_message ( ); CREATE INDEX edi_message_account_status_idx ON acq.edi_message (account,status); CREATE INDEX edi_message_po_idx ON acq.edi_message (purchase_order); +CREATE INDEX edi_message_remote_file_idx ON acq.edi_message (evergreen.lowercase(remote_file)); -- Note below that the primary key is NOT a SERIAL type. We will periodically truncate and rebuild -- the table, assigning ids programmatically instead of using a sequence. diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.index_edi_message_remote_file.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.index_edi_message_remote_file.sql new file mode 100644 index 0000000000..41691618ba --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.index_edi_message_remote_file.sql @@ -0,0 +1,7 @@ +BEGIN; + +-- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +CREATE INDEX edi_message_remote_file_idx ON acq.edi_message (evergreen.lowercase(remote_file)); + +COMMIT; -- 2.43.2