Comment 2 for bug 1730726

I recently did an upgrade of a 3.1.6 test system to PostgreSQL 10. Here's what I've found:

[1] The issue Ben ran into in comment #1 can be fixed by changing evergreen.org_top to not be set-returning, since we know that there should be exactly one root OU:

CREATE OR REPLACE FUNCTION evergreen.org_top()
RETURNS actor.org_unit AS $$
    SELECT * FROM actor.org_unit WHERE parent_ou IS NULL LIMIT 1;
$$ LANGUAGE SQL STABLE;

[2] The test database in question was ancient vintage (it started at Evergreen 1.4), and still had some tables using the tsearch2 extension rather than Pg's built-in full-text search. Since Pg 10 does away with the tsearch2 compatibility contrib entirely, the following steps were needed to make a pg_upgradecluster to Pg 10 work:

alter table asset.copy_tag alter column index_vector type pg_catalog.tsvector;
drop view metabib.combined_all_field_entry;
alter table metabib.combined_author_field_entry alter column index_vector type pg_catalog.tsvector;
alter table metabib.combined_identifier_field_entry alter column index_vector type pg_catalog.tsvector;
alter table metabib.combined_keyword_field_entry alter column index_vector type pg_catalog.tsvector;
alter table metabib.combined_series_field_entry alter column index_vector type pg_catalog.tsvector;
alter table metabib.combined_subject_field_entry alter column index_vector type pg_catalog.tsvector;
alter table metabib.combined_title_field_entry alter column index_vector type pg_catalog.tsvector;
drop index authority.authority_full_rec_index_vector_idx ;
CREATE INDEX authority_full_rec_index_vector_idx ON authority.full_rec USING GIST (index_vector);

CREATE VIEW metabib.combined_all_field_entry AS
    SELECT * FROM metabib.combined_title_field_entry
        UNION ALL
    SELECT * FROM metabib.combined_author_field_entry
        UNION ALL
    SELECT * FROM metabib.combined_subject_field_entry
        UNION ALL
    SELECT * FROM metabib.combined_keyword_field_entry
        UNION ALL
    SELECT * FROM metabib.combined_identifier_field_entry
        UNION ALL
    SELECT * FROM metabib.combined_series_field_entry;

DROP AGGREGATE IF EXISTS public.agg_tsvector(tsvector) CASCADE;
drop function public.tsvector_concat(tsvector, tsvector);
drop extension tsearch2;

The above probably won't work exactly as listed for all vintage databases, but it would be possible to write a script to automate most of the conversion. (And that conversion can be done /before/ upgrading to Pg 10).