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

[Rogan Hamby]

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=
> Open-ILS/src/sql/Pg/version-upgrade/2.12.5-3.0-beta1-upgrade-db.sql;h=
> 7fc9b51936854db32a1a09a20ea276bb1a16747e;hp=97ca7fa5fff4bd301dc021cf5a0bac
> 0112a2463b;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
>
> 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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://libmail.georgialibraries.org/pipermail/open-ils-general/attachments/20180323/8d81fa33/attachment-0001.html>