Unable to migrate from postgres-13 to 14

Started by Jasvant Singhover 4 years ago2 messagesgeneral
Jump to latest
#1Jasvant Singh
jasingh@watermarkinsights.com

Hi,

I am trying to migrate my database from postgres-13 to 14 but getting
following error:

pg_restore: error: could not execute query: ERROR: function
array_append(anyarray, anyelement) does not exist
Command was: CREATE AGGREGATE "public"."mode"("anyelement") (
SFUNC = "array_append",
STYPE = "anyarray",
INITCOND = '{}',
FINALFUNC = "public"."_final_mode"
);

To work around this problem I decided to drop this aggregate in postgres-13
and create it again with the supported version of array_append in
postgres-14.
But when I tried to drop it in postgres-13 I got following error:
#drop aggregate mode("anyelement") CASCADE;
ERROR: cannot drop function mode(anyelement) because it is required by the
database system

I don't understand why the database system is using my user defined
aggregate. If it is a system defined aggregate then it should not be copied
from postgres-13 to postgres-14. It should be there in postgres-14.

Please suggest some work around.

Thanks,
Jasvant

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Jasvant Singh (#1)
Re: Unable to migrate from postgres-13 to 14

Jasvant Singh <jasingh@watermarkinsights.com> writes:

pg_restore: error: could not execute query: ERROR: function
array_append(anyarray, anyelement) does not exist
Command was: CREATE AGGREGATE "public"."mode"("anyelement") (
SFUNC = "array_append",
STYPE = "anyarray",
INITCOND = '{}',
FINALFUNC = "public"."_final_mode"
);

To work around this problem I decided to drop this aggregate in postgres-13
and create it again with the supported version of array_append in
postgres-14.
But when I tried to drop it in postgres-13 I got following error:
#drop aggregate mode("anyelement") CASCADE;
ERROR: cannot drop function mode(anyelement) because it is required by the
database system

I think this is trying to drop the built-in pg_catalog.mode(anyelement)
aggregate. Say DROP AGGREGATE public.mode("anyelement")

(I'd be pretty cautious about using CASCADE, too, at least till
you've seen what would get dropped.)

regards, tom lane