Go to string_agg (again)

[Ben Shum]

Evergreen master

So, it seems that we have some older style ARRAY_TO_STRING(ARRAY_AGG()) (and similar) back in the base schemas and code. Let's change those over to native STRING_AGG() functions (present in PG 9+) if we can.

See old bug 874296 for original report regarding these kind of changes.

Try, try again. Working branch forthcoming.

[Ben Shum (bshum)]

Looks like we got it present in

030.schema.metabib.sql -- function metabib.reingest_record_attributes
990.schema.unapi.sql -- function unapi.mmr

Searching for other cases and will actually build a working branch to fix this, this time.

[Ben Shum (bshum)]

Noting there is also an array_accum() used in t/search_limit_facet_fetch.pg that should be changed with array_agg()

[Dan Scott (denials)]

We should probably also add a test for these and any other known problematic functions to prevent them from getting copy-pasted back into existence in the future...

[Ben Shum (bshum)]

Working branch to address the ones I've been able to find -- user/bshum/lp1441750

http://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/bshum/lp1441750

Tested with a clean DB, adding the upgrade scripts, and also using a refreshly rebuilt concerto, and search seems to work fine.

[Jason Stephenson (jstephenson)]

I added a signoff branch here:

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

working/user/dyrcona/lp1441750-signoff

I tested it and don't see any problems in search, but I think more eyes would be helpful. It might also be useful for release notes if someone could generate some performance metrics from before and after this patch.

[Ben Shum (bshum)]

Hmm, looks like we added a new array_to_string(array_agg()) in 999.functions.global.sql with the actor.usr_merge function with new components for 3.2. We'll need to change that as well moving forward.

[Jason Stephenson (jstephenson)]

I'm setting this to won't fix for 3.1 because I'm updating the upgrade script for name keywords in acrtor.usr_merge, and that feature does not exist in 3.1.

[Jason Stephenson (jstephenson)]

I pushed a rebased, updated branch to working/user/dyrcona/lp1441750-latest. This branch has an additional commit on top to update the actor.usr_merge function as mentioned by Ben in comment #6. I am also removing the signedoff tag, even though all pgtap and Perl tests pass.

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

[Jason Stephenson (jstephenson)]

CW MARS has been using this in production since April of 2019. I was just going to push this patch at last, but I see that Galen is assigned to this bug.

Galen, if you're not longer looking at this, please remove yourself. If you prefer, I'm OK with this going in, so feel free to push it.

[Jason Boyer (jboyer)]

I've taken a look at this and updated the upgrade script to account for some minor drift since it was initially put together. Pushed to master through rel_3_9 to put this behind us one more time (only putting it in master would potentially require different upgrade scripts for >= 3.11 and <= 3.10 which is no good). Thanks Ben and Jason!