[Evergreen-admin] Upgrade 2.2.0 to latest... Database error on 2.3-2.4.0 script

[Jeff Green]

Hi all.  I wanted to follow up for posterity.  I had several challenges
with the 2.3-2.4.0-upgrade and culled info from IRC, listserv, and direct
troubleshooting.

1. Error on "DROP EXTENSION tsearch2" resolved by
  a. Wrong search_path.  Fixed by running ALTER DATABASE evergreen SET
search_path TO evergreen, public, pg_catalog;
  b. Adding 2 commands prior to the DROP EXTENSION command:  DROP AGGREGATE
agg_tsvector(tsvector); DROP FUNCTION public.tsvector_concat(tsvector,
tsvector);

After I resolved those two issues, I still had the following error:
2014-05-07 10:51:21 PDT ERROR:  cannot drop extension tsearch2 because
other objects depend on it
2014-05-07 10:51:21 PDT DETAIL:  index
authority.authority_full_rec_index_vector_idx depends on operator class
gist_tsvector_ops for access method gist
        index metabib.metabib_full_rec_index_vector_idx depends on operator
class gist_tsvector_ops for access method gist
        index metabib.metabib_author_field_entry_index_vector_idx depends
on operator class gist_tsvector_ops for access method gist
        index metabib.metabib_keyword_field_entry_index_vector_idx depends
on operator class gist_tsvector_ops for access method gist
        index metabib.metabib_series_field_entry_index_vector_idx depends
on operator class gist_tsvector_ops for access method gist
        index metabib.metabib_subject_field_entry_index_vector_idx depends
on operator class gist_tsvector_ops for access method gist
        index metabib.metabib_title_field_entry_index_vector_idx depends on
operator class gist_tsvector_ops for access method gist

I resolved this by dropping the old indexes (looks like they are superseded
by a different index naming scheme).
  c. Dropping some old additional indexes (added around line 81)


DROP INDEX authority.authority_full_rec_index_vector_idx;
DROP INDEX metabib.metabib_full_rec_index_vector_idx;
DROP INDEX metabib.metabib_author_field_entry_index_vector_idx;
DROP INDEX metabib.metabib_keyword_field_entry_index_vector_idx;
DROP INDEX metabib.metabib_series_field_entry_index_vector_idx;
DROP INDEX metabib.metabib_subject_field_entry_index_vector_idx;
DROP INDEX metabib.metabib_title_field_entry_index_vector_idx;


2. A second error was encountered, ERROR: cannot drop type
search.search_result because other objects depend on it DETAIL:
 function search.staged_fts

As it turns out, I had several overloaded function definitions, so although
the upgrade script drops staged_fts it missed two of the overloaded
functions.  I resolved by adding at about line 890

DROP FUNCTION
search.staged_fts(integer,integer,text,integer[],text[],text[],text[],text[],text[],text[],text,real,text,boolean,boolean,boolean,integer,integer,integer);
DROP FUNCTION
search.staged_fts(integer,integer,text,integer[],integer[],text[],text[],text[],text[],text[],text[],text,real,text,boolean,boolean,boolean,integer,integer,integer);

At this point I was able to complete the upgrade to 2.4.0 (so far, so good).




On Tue, Apr 8, 2014 at 10:05 AM, Lazar, Alexey Vladimirovich <
alexey.lazar at mnsu.edu> wrote:

> Hi, Jeff.
>
> I do not have a specific answer, but a few things I would do to
> troubleshoot:
>
> * Review all the schema patches from the upgrade log table in the database
> and cross-reference with patch numbers in the upgrade and/or
> version-upgrade script folders. Anything missing?
> * Grep through the schema upgrade folder and version-upgrade folder for
> the keywords that come up in your errors to see what/when was added or
> could be missing
> * You may want to search through bugs and git repo
>
> Generally, I found that is it more reliable to use minor version schema
> upgrade scripts up to the max “minor" version and then upgrading to the
> next “major” version.
>
> Also, I found that when I encountered a “weird" error that is not
> generally known in the community, it was a local error. The two I have
> encountered were related to missing schema patches.
>
> Just FYI, over the past couple years I have upgraded data all the way from
> 2.0 to 2.5 now and from 2.2 to 2.5 and have not seen this error.
>
> I know this does not specifically answer your question directly, but I
> hope this helps a little.
>
> Aleksey
>
> On 2014-03-30, at 11:42 , Jeff Green <jeff.green.ca at gmail.com> wrote:
>
> > Hi. I'm running into an issue upgrading EG 2.2.0 to the latest, and
> could use some help on what to try next. The upgrade crashes during the
> 2.3-2.4.0 script, on the DROP EXTENSION tsearch2 command.
> >
> > There was an IRC chat (
> http://evergreen-ils.org/irc_logs/evergreen/2013-06/%23evergreen.27-Thu-2013.log)
> about adding a couple statements. However, still got the same error.
> >
> > I also tried DROP...CASCADE; but that created other errors later.
> >
> > So, I rolled back to my previous snapshot (VM) and am asking for some
> advice on how to proceed!  Any suggestions on what to do next?
> >
> > Here's the error:
> > 2014-02-28 13:48:30 PST ERROR:  cannot drop extension tsearch2 because
> other objects depend on it
> >
> > Here's the statements I added immediately before the DROP EXTENSION
> tsearch2:
> > DROP AGGREGATE agg_tsvector(tsvector);
> > DROP FUNCTION tsvector_concat(tsvector, tsvector);
> >
> > Script is also throwing these errors:
> > psql:2.3-2.4.0-upgrade-db.sql:3996: ERROR:  text search configuration
> "english_nostop" does not exist
> > psql:2.3-2.4.0-upgrade-db.sql:3997: ERROR:  text search configuration
> "english_nostop" does not exist
> > psql:2.3-2.4.0-upgrade-db.sql:3998: ERROR:  text search configuration
> "english_nostop" does not exist
> > psql:2.3-2.4.0-upgrade-db.sql:3999: ERROR:  text search configuration
> "english_nostop" does not exist
> > psql:2.3-2.4.0-upgrade-db.sql:4000: ERROR:  text search configuration
> "english_nostop" does not exist
> >
> >
> > Thanks!
> >
> > --
> > Jeff Green
> >
> > _______________________________________________
> > Evergreen-admin mailing list
> > Evergreen-admin at list.evergreen-ils.org
> > http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-admin
>
> Aleksey Lazar
> IS Developer and Integrator - PALS
> http://www.mnpals.org/
>
>


-- 
Jeff Green
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://list.evergreen-ils.org/pipermail/evergreen-admin/attachments/20140512/c035f95e/attachment.html>