array_accum Aggregate and PostgreSQL 14
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Evergreen |
Fix Released
|
Medium
|
Unassigned |
Bug Description
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(
pg_restore: error: could not execute query: ERROR: function array_append(
Command was: CREATE AGGREGATE evergreen.
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.
n;
Trying to recreate the aggregate after a failed load, gives the same error: ERROR: function array_append(
Changing array_accum's definition to:
CREATE AGGREGATE evergreen.
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.
Changed in evergreen: | |
milestone: | none → 3.9-beta |
tags: | added: database |
Changed in evergreen: | |
assignee: | nobody → Jason Stephenson (jstephenson) |
status: | New → In Progress |
Changed in evergreen: | |
assignee: | Jason Stephenson (jstephenson) → nobody |
Changed in evergreen: | |
status: | In Progress → New |
Changed in evergreen: | |
importance: | Undecided → Medium |
Changed in evergreen: | |
status: | Confirmed → Fix Committed |
Changed in evergreen: | |
status: | Fix Committed → Fix Released |
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