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

Kenn

Can you please add more details to the question like create_function, rails, postgres,Also, the things you have tried already etc.? – Damalas

Which version of PostgreSQL are you using? – Yokum

You are using a pg function, so you should check if that function is present in your test db. -- I'm talking about array_append – Textualist

Answer 1

After a few research, I have resolved this issue by changing the Postgres image version on GitHub as follows.

file .github/workflows/build.yml

services:
      postgres:
        image: postgres:13
        ports:
          - 5432:5432

Solution: This is an issue with Postgres 14 version, For quick solve downgrade it to Postgres 13.

Kenn

Answer 2

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='{}'
);

Brentbrenton

Answer 3

I have solved by creating a wrapper function in pg_catalog with:

CREATE FUNCTION pg_catalog.array_append(a anyarray, b anyelement)
RETURNS anyarray AS
$$
   SELECT array_append(a::anycompatiblearray, b::anycompatible);
$$ LANGUAGE sql IMMUTABLE ;

The issue for me happened when importing PostgreSQL 13 dump in PostgreSQL 16.3

Homogenize