On Wed, May 04, 2022 at 07:34:15AM -0700, David G. Johnston wrote:
> On Wed, May 4, 2022 at 7:29 AM Petr Vejsada <p...@paymorrow.com> wrote:
> > We solved it (in our case) dropping the aggregate before upgrade and
> > re-create in using new syntax in V14:
> >
> > but pg_upgrade shouldn't fail on this.
> >
> > I hope it can help to improve pg_upgrade process.
>
> The release notes say explicitly that one needs to drop and recreate the
> affected functions. Thus, we know about the issue and to date our best
> solution is to have the user do exactly what you did (i.e., it is not
> something pg_upgrade is going to do for you). If you have an alternative
> solution to suggest that would help.
>
> https://www.postgresql.org/docs/current/release-14.html : the first
> compatibility note
David is right that this is documented as a compatibility issue.
But Petr has a point - pg_upgrade should aspire to catch errors in --check,
rather than starting and then leaving a mess behind for the user to clean up
(remove existing dir, rerun initdb, start old cluster, having first moved the
dir back into place if you moved it out of the way as I do). This can take
extra minutes, and exacerbates any other problem one encounters.
$ ./tmp_install/usr/local/pgsql/bin/pg_upgrade -d pg95.dat -D pg15.dat -b
/usr/lib/postgresql/9.5/bin
...
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster
postgres
*failure*
Consult the last few lines of
"pg15.dat/pg_upgrade_output.d/20220610T104419.303/log/pg_upgrade_dump_12455.log"
for
the probable cause of the failure.
pg_restore: error: could not execute query: ERROR: function
array_append(anyarray, anyelement) does not exist
Command was: CREATE AGGREGATE "public"."array_accum"("anyelement") (
SFUNC = "array_append",
STYPE = "anyarray",
INITCOND = '{}'
);
This patch catches the issue; the query needs to be reviewed.
SELECT pn.nspname, p.proname FROM pg_proc p
JOIN pg_aggregate a ON a.aggfnoid=p.oid
JOIN pg_proc q ON q.oid=a.aggtransfn
JOIN pg_namespace pn ON pn.oid=p.pronamespace
JOIN pg_namespace qn ON qn.oid=q.pronamespace
WHERE pn.nspname != 'pg_catalog' AND qn.nspname = 'pg_catalog'
AND 'anyelement'::regtype = ANY(q.proargtypes)
AND q.proname IN ('array_append', 'array_prepend', 'array_cat',
'array_position', 'array_positions', 'array_remove', 'array_replace',
'width_bucket');
>From 5349d32d91ce0160e08405387a30ec53ea434944 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Fri, 10 Jun 2022 11:17:36 -0500
Subject: [PATCH] WIP: pg_upgrade --check: detect aggregates for pre-pg14
This fails when upgrading from pre-14 (as expected), but it should fail during
pg_upgrade --check.
CREATE AGGREGATE array_accum(anyelement) (sfunc=array_append, stype=anyarray, initcond='{}');
See also:
9e38c2bb5093ceb0c04d6315ccd8975bd17add66
97f73a978fc1aca59c6ad765548ce0096d95a923
---
src/bin/pg_upgrade/check.c | 97 ++++++++++++++++++++++++++++++++++++++
1 file changed, 97 insertions(+)
diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
index ace7387edaf..4fd47e0b59a 100644
--- a/src/bin/pg_upgrade/check.c
+++ b/src/bin/pg_upgrade/check.c
@@ -31,6 +31,7 @@ static void check_for_jsonb_9_4_usage(ClusterInfo *cluster);
static void check_for_pg_role_prefix(ClusterInfo *cluster);
static void check_for_new_tablespace_dir(ClusterInfo *new_cluster);
static void check_for_user_defined_encoding_conversions(ClusterInfo *cluster);
+static void check_for_old_aggregates(ClusterInfo *cluster);
static char *get_canonical_locale_name(int category, const char *locale);
@@ -122,6 +123,12 @@ check_and_dump_old_cluster(bool live_check)
if (GET_MAJOR_VERSION(old_cluster.major_version) <= 1300)
check_for_user_defined_postfix_ops(&old_cluster);
+ /*
+ * Pre-PG 14 changed from anyarray to anycompatibelarray.
+ */
+ if (GET_MAJOR_VERSION(old_cluster.major_version) <= 1300)
+ check_for_old_aggregates(&old_cluster);
+
/*
* Pre-PG 12 allowed tables to be declared WITH OIDS, which is not
* supported anymore. Verify there are none, iff applicable.
@@ -775,6 +782,96 @@ check_proper_datallowconn(ClusterInfo *cluster)
}
+/*
+ * check_for_old_aggregates()
+ *
+ * Make sure there are no aggregates using anyelement which need to be changed
+ * to anycompatible.
+ */
+static void
+check_for_old_aggregates(ClusterInfo *cluster)
+{
+ PGresult *res;
+ PGconn *conn = connectToServer(cluster, "template1");
+ FILE *script = NULL;
+ char output_path[MAXPGPATH];
+
+ prep_status("Checking for old aggregates using anyelement");
+
+ snprintf(output_path, sizeof(output_path), "%s/%s",
+ log_opts.basedir,
+ "databases_with_old_aggregates.txt");
+
+ for (int dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
+ {
+ bool db_used = false;
+ DbInfo *active_db = &cluster->dbarr.dbs[dbnum];
+ PGconn *conn = connectToServer(cluster, active_db->db_name);
+ int ntups;
+ int i_nspname,
+ i_proname;
+
+ res = executeQueryOrDie(conn,
+ "SELECT pn.nspname, p.proname FROM "
+ "pg_proc p "
+ "JOIN pg_aggregate a ON a.aggfnoid=p.oid "
+ "JOIN pg_proc q ON q.oid=a.aggtransfn "
+ "JOIN pg_namespace pn ON pn.oid=p.pronamespace "
+ "JOIN pg_namespace qn ON qn.oid=q.pronamespace "
+ "WHERE pn.nspname != 'pg_catalog' "
+ "AND qn.nspname = 'pg_catalog' "
+ "AND 'anyelement'::regtype=ANY(q.proargtypes) "
+ "AND q.proname IN ('array_append',"
+ " 'array_prepend', 'array_cat', "
+ " 'array_position', 'array_positions', "
+ " 'array_remove', 'array_replace', "
+ " 'width_bucket');");
+ // "AND aggtranstype='anyarray'::regtype
+ /* Before v11, used proisagg=true, and afterwards uses prokind='a' */
+
+ ntups = PQntuples(res);
+
+ i_nspname = PQfnumber(res, "nspname");
+ i_proname = PQfnumber(res, "proname");
+
+ for (int rowno = 0; rowno < ntups; rowno++)
+ {
+ if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)
+ pg_fatal("could not open file \"%s\": %s\n",
+ output_path, strerror(errno));
+
+ if (!db_used)
+ {
+ db_used = true;
+ fprintf(script, "In database: %s\n", active_db->db_name);
+ }
+
+ fprintf(script, " %s.%s\n",
+ PQgetvalue(res, rowno, i_nspname),
+ PQgetvalue(res, rowno, i_proname));
+ }
+
+ PQclear(res);
+ }
+
+ PQfinish(conn);
+
+ if (script)
+ {
+ fclose(script);
+ pg_log(PG_REPORT, "fatal\n");
+ pg_fatal("The cluster contains aggregate functions using anyelement.\n"
+ "User defined aggregates which refer to built-in functions with\n"
+ "arguments of type 'anyelement' must be dropped before upgrading\n"
+ "and restored afterwards to refer to the corresponding functions\n"
+ "with arguments of type 'anycompatible'.\n"
+ "A list of the problem functions is in the file:\n"
+ " %s\n\n", output_path);
+ }
+ else
+ check_ok();
+}
+
/*
* check_for_prepared_transactions()
*
--
2.17.1
