Postgres 10 support: Vandelay Edition

Bug #1820339 reported by Jason Boyer
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Evergreen
Fix Released
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.

Revision history for this message
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)
Revision history for this message
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
Revision history for this message
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

Revision history for this message
Ben Shum (bshum) wrote :
Revision history for this message
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  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.