Support for PostgreSQL 10

Bug #1730726 reported by Ben Shum on 2017-11-07
This bug affects 1 person
Affects Status Importance Assigned to Milestone

Bug Description

Evergreen master

PostgreSQL 10.0 was released earlier in October 2017. This bug is an initial placeholder towards determining needs and requirements for getting it compatible with Evergreen moving forward.

Ben Shum (bshum) wrote :

After adding a symlink for "000.english.pg100.fts-config.sql", the database creation failed for unapi with this error:

psql:990.schema.unapi.sql:1508: ERROR: set-returning functions are not allowed in COALESCE
LINE 22: WITH aou AS (SELECT COALESCE(id, (evergreen....
HINT: You might be able to move the set-returning function into a LATERAL FROM item.

Galen Charlton (gmc) on 2018-11-01
Changed in evergreen:
importance: Undecided → Wishlist
Galen Charlton (gmc) wrote :

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;

[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).

Changed in evergreen:
status: New → Confirmed
Jason Stephenson (jstephenson) wrote :

I have pushed a collab branch to workgin/collab/dyrcona/lp1730726-postgresql10-support (;a=shortlog;h=refs/heads/collab/dyrcona/lp1730726-postgresql10-support)

This branch has 3 commits:

1. That modifies the evergreen.org_top() function according to Galen's suggestion in comment #2, updates the eg_db_config support script to better check for supported PostgreSQL versions, add the full text config for PostgreSQL 10.

2. Fixes a number of tests that fail on PostgreSQL 10 because doing \set ECHO without a value is now an error.

3. Fixes the tests for the evergreen.unaccent_and_squash() function for changes in the PostgreSQL unaccent function in version 10.

I am adding the pullrequest tag. This could probably use a few more eyes.

tags: added: pullrequest
Changed in evergreen:
milestone: none →
tags: added: signedoff
tags: removed: signedoff
Jason Stephenson (jstephenson) wrote :

I'm removing the pullrequest tag in order to add release notes and a few other amenities.

If anyone wants to look at what's there, that's fine and would be appreciated.

tags: removed: pullrequest
Changed in evergreen:
assignee: nobody → Jason Stephenson (jstephenson)
Jason Stephenson (jstephenson) wrote :

I rebased the branch, modified the unnaccent and squash test so that it also works on PostgreSQL 9.6, and added release notes.

I have tested this with PostgreSQL 9.5 and 10.6 version databases on Ubuntu 16.04 and Ubuntu 18.04 respectively. I have not tested it with PostgreSQL 9.6, so someone testing with that database version would be much appreciated.

Testing an upgrade from 9.5 to 10.6 or 9.6 would also be useful, though not as necessary.

Changed in evergreen:
assignee: Jason Stephenson (jstephenson) → nobody
milestone: → 3.3-beta1
tags: added: pullrequest
Ben Shum (bshum) wrote :

Pushed to Evergreen master

Changed in evergreen:
status: Confirmed → Fix Committed
Changed in evergreen:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers