On Wed, Jun 15, 2022 at 03:32:04PM -0400, Tom Lane wrote:
> Justin Pryzby <pry...@telsasoft.com> writes:
> > 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
>
> Agreed; pg_upgrade has historically tried to find problems similar to
> this. However, it's not just aggregates that are at risk. People
> might also have built user-defined plain functions, or operators,
> atop these functions. How far do we want to go in looking?
I wasn't yet able to construct a user-defined function which fails to reload.
> As for the query, I think it could be simplified quite a bit by
> relying on regprocedure literals, that is something like
Yes, thanks.
> Also, I think you need to check aggfinalfn too.
Done but maybe needs more cleanup.
> Also, I'd be inclined to reject system-provided objects by checking
> for OID >= 16384 rather than hard-wiring assumptions about things
> being in pg_catalog or not.
To me, oid>=16384 seems more hard-wired than namespace!='pg_catalog'.
This patch also resolves an issue with PQfinish()/dangling connections.
--
Justin
>From d8c3a2d4e4971b671347cc1c73c70e67049599c9 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 old polymorphics from pre-14
These fail 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='{}');
CREATE OPERATOR !@# (PROCEDURE = array_append, LEFTARG=anyarray, rightarg=anyelement);
See also:
9e38c2bb5093ceb0c04d6315ccd8975bd17add66
97f73a978fc1aca59c6ad765548ce0096d95a923
---
src/bin/pg_upgrade/check.c | 123 +++++++++++++++++++++++++++++++++++++
1 file changed, 123 insertions(+)
diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
index ace7387edaf..bfdadc4d685 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_polymorphics(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_polymorphics(&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,122 @@ check_proper_datallowconn(ClusterInfo *cluster)
}
+/*
+ * check_for_old_polymorphics()
+ *
+ * Make sure nothing is using old polymorphic functions with
+ * anyarray/anyelement rather than the new anycompatible variants.
+ */
+static void
+check_for_old_polymorphics(ClusterInfo *cluster)
+{
+ PGresult *res;
+ FILE *script = NULL;
+ char output_path[MAXPGPATH];
+
+ prep_status("Checking for use of old polymorphic functions");
+
+ snprintf(output_path, sizeof(output_path), "%s/%s",
+ log_opts.basedir,
+ "databases_with_old_polymorphics.txt");
+
+#define OLD_POLYMORPHICS \
+ "'array_append(anyarray,anyelement)', " \
+ "'array_cat(anyarray,anyarray)', " \
+ "'array_position(anyarray,anyelement)', " \
+ "'array_position(anyarray,anyelement,integer)', " \
+ "'array_positions(anyarray,anyelement)', " \
+ "'array_prepend(anyelement,anyarray)', " \
+ "'array_remove(anyarray,anyelement)', " \
+ "'array_replace(anyarray,anyelement,anyelement)', " \
+ "'width_bucket(anyelement,anyarray)' "
+
+ 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_what,
+ i_objname;
+
+ res = executeQueryOrDie(conn,
+ /* Aggregate transition functions */
+ "SELECT 'aggregate' AS what, p.oid::regprocedure::text AS objname "
+ "FROM pg_proc p "
+ "JOIN pg_aggregate a ON a.aggfnoid=p.oid "
+ "JOIN pg_proc transfn ON transfn.oid=a.aggtransfn "
+ "JOIN pg_namespace pn ON pn.oid=p.pronamespace "
+ "JOIN pg_namespace transnsp ON transnsp.oid=transfn.pronamespace "
+ "WHERE pn.nspname != 'pg_catalog' "
+ /* Before v11, used proisagg=true, and afterwards uses prokind='a' */
+ "AND transnsp.nspname = 'pg_catalog' "
+ "AND a.aggtransfn = ANY(ARRAY[" OLD_POLYMORPHICS "]::regprocedure[]) "
+ // "AND aggtranstype='anyarray'::regtype
+
+ /* Aggregate final functions */
+ "UNION ALL "
+ "SELECT 'aggregate' AS what, p.oid::regprocedure::text AS objname "
+ "FROM pg_proc p "
+ "JOIN pg_aggregate a ON a.aggfnoid=p.oid "
+ "JOIN pg_namespace pn ON pn.oid=p.pronamespace "
+ "JOIN pg_proc finalfn ON finalfn.oid=a.aggfinalfn "
+ "JOIN pg_namespace finalnsp ON finalnsp.oid=finalfn.pronamespace "
+ "WHERE pn.nspname != 'pg_catalog' "
+ "AND finalnsp.nspname = 'pg_catalog' "
+ "AND a.aggfinalfn = ANY(ARRAY[" OLD_POLYMORPHICS "]::regprocedure[])) "
+
+ /* Operators */
+ "UNION ALL "
+ "SELECT 'operator' AS what, op.oid::regoperator::text AS objname"
+ "FROM pg_operator op "
+ "JOIN pg_namespace opn ON opn.oid=op.oprnamespace "
+ "WHERE opn.nspname != 'pg_catalog' "
+ "AND oprcode=ANY(ARRAY[ " OLD_POLYMORPHICS "]::regprocedure[]);");
+
+ ntups = PQntuples(res);
+
+ i_what = PQfnumber(res, "what");
+ i_objname = PQfnumber(res, "objname");
+
+ 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_what),
+ PQgetvalue(res, rowno, i_objname));
+ }
+
+ PQclear(res);
+ PQfinish(conn);
+ }
+
+ if (script)
+ {
+ fclose(script);
+ pg_log(PG_REPORT, "fatal\n");
+ pg_fatal("The cluster contains user-defined objects which refer to\n"
+ "internal polymorphic functions with arguments of type\n"
+ "'anyarray' or 'anyelement'. These user-defined objects\n"
+ "must be dropped before upgrading and restored afterwards\n"
+ "to refer to the corresponding functions with arguments\n"
+ "of type 'anycompatiblearray' and 'anycompatible'.\n"
+ "A list of the problem objects is in the file:\n"
+ " %s\n\n", output_path);
+ }
+ else
+ check_ok();
+}
+
/*
* check_for_prepared_transactions()
*
--
2.17.1
