[OPEN-ILS-GENERAL] 3.0.2-3.0.3 Upgrade DB script takes over a week to finish

[Kathy Lussier]

Yes, absolutely, and please note that you do need to reenable those 
triggers when the recalculation is done. :)

Kathy


On 03/23/2018 04:22 PM, Rogan Hamby wrote:
> Just to follow up on this the essence of the triggers Kathy is 
> pointing to are triggers that mostly are there to maintain the MARC 
> XML or tables derived from the MARC in various ways so when only 
> altering the visibility row it's probably safe to disable them but I'd 
> be hesitant to,  make that as a blanket statement.  I do agree with 
> Kathy that this is probably a similar scenario though.
>
> Rogan Hamby, MLIS
>
> Data and Project Analyst
>
> Equinox Open Library Initiative
>
> phone:  1-877-OPEN-ILS (673-6457)
>
> email:  rogan at EquinoxInitiative.org
>
> web: http://EquinoxInitiative.org
>
> On Fri, Mar 23, 2018 at 4:13 PM, Kathy Lussier <klussier at masslnc.org 
> <mailto:klussier at masslnc.org>> wrote:
>
>     Hi Jesse,
>
>     Yes, the recalculation at the end of that upgrade script is
>     necessary. In 3.0, we made some changes to the way catalog
>     searches determine record visibility, and this part of the script
>     recalculates visibility to fix a few search issues that were
>     discovered in the 3.0 release. Without recalculating visibility,
>     you'll find that some records for electronic resources or those
>     that have a bib source (which cover almost all records in our
>     system) will appear in searches when they shouldn't.
>
>     Having said that, I think we can speed up that upgrade script. We
>     had a similar calculation in the 2.12 to 3.0 upgrade script, and I
>     one point we made a change to disable various triggers before
>     performing the calculation. My understanding is that the
>     calculations perform much more quickly with those triggers
>     disabled. See the changes at:
>
>     http://git.evergreen-ils.org/?p=Evergreen.git;a=blobdiff;f=Open-ILS/src/sql/Pg/version-upgrade/2.12.5-3.0-beta1-upgrade-db.sql;h=7fc9b51936854db32a1a09a20ea276bb1a16747e;hp=97ca7fa5fff4bd301dc021cf5a0bac0112a2463b;hb=d388f7019a90a5809514407d7139eb1ed1843432;hpb=0b749e554c3a5c8a93ca36e06e8b587991ab70a3
>     <http://git.evergreen-ils.org/?p=Evergreen.git;a=blobdiff;f=Open-ILS/src/sql/Pg/version-upgrade/2.12.5-3.0-beta1-upgrade-db.sql;h=7fc9b51936854db32a1a09a20ea276bb1a16747e;hp=97ca7fa5fff4bd301dc021cf5a0bac0112a2463b;hb=d388f7019a90a5809514407d7139eb1ed1843432;hpb=0b749e554c3a5c8a93ca36e06e8b587991ab70a3>
>
>     I'm going to file a bug to see if we can make a similar change for
>     the 3.0.2-3.0.3 upgrade script.
>
>     Kathy
>
>
>     On 03/23/2018 03:16 PM, Jesse McCarty wrote:
>>
>>     Hello Everyone,
>>
>>     During my last test cycle we ran into an issue upgrading from
>>     2.10 to a newer version with an update script that was setting
>>     the 901$sfor bib records. This took an extended amount of time to
>>     complete. Well now, in testing our upgrade to the 3.0 series part
>>     of the 3.0.2-3.0.3 version upgrade script took over a week to
>>     finish in testing, which is a big issue for updating production.
>>
>>
>>     Is it possible to comment out/remove the offending part of the
>>     upgrade script and not have any issues with the new system after
>>     the upgrade? Could it be the last part of the script in lines
>>     277-291 of the upgrade script taking this long (line 290 perhaps)?
>>
>>
>>     277 UPDATE  biblio.record_entry
>>
>>     278   SET   vis_attr_vector =
>>     biblio.calculate_bib_visibility_attribute_set(id)
>>
>>     279   WHERE id IN (
>>
>>     280             SELECT DISTINCT cn.record
>>
>>     281               FROM asset.call_number cn
>>
>>     282               WHERE NOT cn.deleted
>>
>>     283                     AND cn.label = '##URI##'
>>
>>     284                     AND EXISTS (
>>
>>     285       SELECT  1
>>
>>     286 FROM  asset.uri_call_number_map m
>>
>>     287 WHERE m.call_number = cn.id <http://cn.id>
>>
>>     288                     )
>>
>>     289                 UNION
>>
>>     290             SELECT id FROM biblio.record_entry WHERE source
>>     IS NOT NULL
>>
>>     291         );
>>
>>     Wondering if others have met something similar and how they dealt
>>     with it so as not to cause issues upgrading a production system
>>     and minimizing down time. We typically run our upgrades on a
>>     Sunday morning and all Evergreen related services are only down
>>     for about half a day and usually back up before 10am Monday worst
>>     case.
>>
>>     Thanks in advance,
>>
>>     Jesse McCarty
>>
>>     City of Burlington
>>
>>     Information Systems Technician
>>
>
>     -- 
>     Kathy Lussier
>     Project Coordinator
>     Massachusetts Library Network Cooperative
>     (508) 343-0128 <tel:%28508%29%20343-0128>
>     klussier at masslnc.org <mailto:klussier at masslnc.org>
>     Twitter:http://www.twitter.com/kmlussier <http://www.twitter.com/kmlussier>
>
>

-- 
Kathy Lussier
Project Coordinator
Massachusetts Library Network Cooperative
(508) 343-0128
klussier at masslnc.org
Twitter: http://www.twitter.com/kmlussier

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://libmail.georgialibraries.org/pipermail/open-ils-general/attachments/20180323/86212e08/attachment.html>