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