public inbox for [email protected]
help / color / mirror / Atom feedFrom: Justin Pryzby <[email protected]>
Subject: [PATCH] WIP: pg_upgrade --check: detect aggregates for pre-pg14
Date: Fri, 10 Jun 2022 11:17:36 -0500
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
--98e8jtXdkpgskNou--
view thread (21+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [PATCH] WIP: pg_upgrade --check: detect aggregates for pre-pg14
In-Reply-To: <no-message-id-1859706@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox