array_accum Aggregate and PostgreSQL 14

[Jason Stephenson]

Evergreen Version: 3.5+ (including the master git branch)
PostgreSQL Version: 14.0+
OpenSRF Version: N/A
Linux Distro/Version: N/A

Evergreen creates an aggregate called array_accum using the array_append function. According the PostgreSQL 14 release notes:

Specifically, array_append(), array_prepend(), array_cat(), array_position(), array_positions(), array_remove(), array_replace(), and width_bucket() used to take anyarray arguments but now take anycompatiblearray. Therefore, user-defined objects like aggregates and operators that reference those array function signatures must be dropped before upgrading, and recreated once the upgrade completes.

If one tries to restore a dump of Evergreen to a Pg 14 server without first dropping the array_accum aggregate, the following errors occur:

pg_restore: from TOC entry 6945; 1255 17564 AGGREGATE array_accum(anyelement) evergreen
pg_restore: error: could not execute query: ERROR: function array_append(anyarray, anyelement) does not exist
Command was: CREATE AGGREGATE evergreen.array_accum(anyelement) (
    SFUNC = array_append,
    STYPE = anyarray,
    INITCOND = '{}'
);

pg_restore: error: could not execute query: ERROR: aggregate evergreen.array_ac
cum(anyelement) does not exist
Command was: ALTER AGGREGATE evergreen.array_accum(anyelement) OWNER TO evergree
n;

Trying to recreate the aggregate after a failed load, gives the same error: ERROR: function array_append(anyarray, anyelement) does not exist.

Changing array_accum's definition to:

CREATE AGGREGATE evergreen.array_accum(anycompatible) (
    SFUNC = array_append,
    STYPE = anycompatiblearray,
    INITCOND = '{}'
);

allows it to be recreated, but I have not yet tested if it works, nor do I know if that change works prior to Pg 14.

If we can make array_accum go away in favor of an existing aggregate function, that would be my preference for a fix.

[Jason Stephenson (jstephenson)]

I have pushed a branch to remove our array_accum aggregate and replace the 3 active uses of it with array_agg. All database and Perl tests pass. (NOTE: 1 database test is modified by my branch to use array_agg, as that was one of the 3 active uses of array_accum.)

The branch is user/dyrcona/lp1947595-remove-array_accum

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

[Jason Stephenson (jstephenson)]

I have rebased the branch from comment #1 on master and force pushed it.

I am also using this branch as my starting point for bug 1937294.

[Jason Boyer (jboyer)]

Finally checked this out and exercised both uses in Booking.pm and the pgtap test on 9.6 and there were no changes in functionality before vs after applying this patch. Signoff is here: https://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/jboyer/lp1947595-remove-array_accum_so / working/user/jboyer/lp1947595-remove-array_accum_so

[Jane Sandberg (sandbergja)]

Forgot to say: this works great, and it's nice to make things just a little less complex and custom. :-) Thanks, Jason and Jason! Pushed to master.