PG::UndefinedFunction: ERROR: function array_append(anyarray, anyelement) does not exist

Question

In my application we have few test cases which are configured with GitHub workflow,Even I do have only space related changes on file but still getting below error. Not sure why my specs are still failing it was working fine before.

An error occurred in a `before(:suite)` hook.
Failure/Error: ActiveMedian.create_function

ActiveRecord::StatementInvalid:
  PG::UndefinedFunction: ERROR:  function array_append(anyarray, anyelement) does not exist
  :       CREATE OR REPLACE FUNCTION median(anyarray)
           RETURNS float8 AS
        $$
          WITH q AS
          (
             SELECT val
             FROM unnest($***) val
             WHERE VAL IS NOT NULL
             ORDER BY ***
          ),
          cnt AS
          (
            SELECT COUNT(*) AS c FROM q
          )
          SELECT AVG(val)::float8
          FROM
          (
            SELECT val FROM q
            LIMIT  2 - MOD((SELECT c FROM cnt), 2)
            OFFSET GREATEST(CEIL((SELECT c FROM cnt) / 2.0) - ***,0)
          ) q2;
        $$
        LANGUAGE sql IMMUTABLE;

        DROP AGGREGATE IF EXISTS median(numeric);
        DROP AGGREGATE IF EXISTS median(double precision);
        DROP AGGREGATE IF EXISTS median(anyelement);
        CREATE AGGREGATE median(anyelement) (
          SFUNC=array_append,
          STYPE=anyarray,
          FINALFUNC=median,
          INITCOND='{}'
        );
# ./spec/rails_helper.rb:***54:in `seed'
# ./spec/rails_helper.rb:***:in `block (2 levels) in <top (required)>'
# ------------------
# --- Caused by: ---

# PG::UndefinedFunction:
#   ERROR:  function array_append(anyarray, anyelement) does not exist
#   ./spec/rails_helper.rb:***54:in `seed'

There is ActiveMedian.create_function on spec/rails_helper.rb which might causing the issue.

Any lead or suggestion would be appreciated

Answer 1

You are using PostGreSQL 14, aren't you ?

Because with postgres 13 and before, this should work, see the test result in dbfiddle.

And I confirm that this doesn't work with postgres 14, see the test result in dbfiddle.

The reason is explained in the PostGres 14 manual :

User-defined objects that reference certain built-in array functions along with their argument types must be recreated (Tom Lane)

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.

To make it working, you can do instead :

CREATE OR REPLACE FUNCTION median(anycompatiblearray)
RETURNS float8 AS
$$
...
$$ LANGUAGE sql IMMUTABLE ;

...

CREATE AGGREGATE median(anycompatible) (
  SFUNC=array_append,
  STYPE=anycompatiblearray,
  FINALFUNC=median,
  INITCOND='{}'
);