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

[Rogan Hamby]

Um, yeah.  A hundred times, yes.  Don't disable triggers that you don't
hard code in re-enabling afterwards.  And then make sure you have.  Twice.

If you're paranoid like I am, then three times.


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:26 PM, Kathy Lussier <klussier at masslnc.org> wrote:

> 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>
> 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=O
>> pen-ILS/src/sql/Pg/version-upgrade/2.12.5-3.0-beta1-upgrade-
>> db.sql;h=7fc9b51936854db32a1a09a20ea276bb1a16747e;hp=97ca7fa
>> 5fff4bd301dc021cf5a0bac0112a2463b;hb=d388f7019a90a5809514407
>> d7139eb1ed1843432;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_visibilit
>> y_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
>>
>> 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-0128klussier at masslnc.org
>> Twitter: http://www.twitter.com/kmlussier
>>
>>
>
> --
> Kathy Lussier
> Project Coordinator
> Massachusetts Library Network Cooperative(508) 343-0128klussier 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/5abf841e/attachment-0001.html>