Postgres 10 support: Vandelay Edition

Bug #1820339 reported by Jason Boyer on 2019-03-15
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Evergreen
Medium
Unassigned

Bug Description

While doing some local testing on PG 10 a batch of records was run through the Vandelay importer and we were presented with this lovely log entry:

...ERROR: set-returning functions are not allowed in CASE at character 554
...HINT: You might be able to move the set-returning function into a LATERAL FROM item.
...
...CONTEXT: PL/pgSQL function vandelay.flatten_marc_hstore(text) line 3 at RETURN

And if you look at vandelay.flatten_marc_hstore(text) you see a couple uses of regexp_matches, which returns a set of arrays. Changing this to just regexp_match avoids the error and allowed the import to work as expected. Since I don't know Vandelay well enough to say with certainty "There's no way this would ever cause a problem" I'd appreciate as much testing as people can spare for the branch I'm about to post.

Jason Boyer (jboyer) wrote :

Speaking of:
https://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/jboyer/lp1820339_pg10_vandelay / working/user/jboyer/lp1820339_pg10_vandelay

To test: install Evergreen on a system running Pg 10, try to import a record using a match set.

Pre-patch: Sadness.

Apply patch and repeat

Post patch: Less sadness as the import process completes successfully depending on selections made.

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

Hmm, this solution appears to not be backwards compatible with less than PG 10 (like if someone was running PG 9.4-9.6) because REGEXP_MATCH() only exists in PG10+ according to PG documentation. They included a tip for changing these to some sort of sub-select for REGEXP_MATCHES() - https://www.postgresql.org/docs/current/functions-matching.html

Have to test further, or we need to decide to make a new Evergreen minimum requirement of PG10+ for next release?

Changed in evergreen:
assignee: Jason Stephenson (jstephenson) → nobody
milestone: none → 3.3.1
importance: Undecided → Medium
status: New → Confirmed
Jason Stephenson (jstephenson) wrote :

Ben Shum and I found a solution that works on Pg 9.4 through Pg 10. We use the REGEXP_MATCHES function that Pg 10 complains about, but by moving it into a subselect it returns an array instead of a set of rows.

A rebased, modified version of Jason Boyer's branch is here:

https://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/dyrcona/lp1820339_pg10_vandelay

Jason Boyer (jboyer) wrote :

Tested and pushed to master and rel_3_3, thanks Jason and Ben!

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