Date: Mon, 27 Jun 2022 18:30:03 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Andrey Borodin <x4mmm@yandex-team.ru>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Robert Haas <robertmhaas@gmail.com>,
	"David G. Johnston" <david.g.johnston@gmail.com>,
	Petr Vejsada <pve@paymorrow.com>,
	pgsql-hackers@lists.postgresql.org
Subject: Re: pg_upgrade (12->14) fails on aggregate
Message-ID: <20220627233003.GE19662@telsasoft.com>
References: 
 <CAKFQuwbUUwkO0ffRY4+z4Hs9-jRjAUwGHZrDKyHhRudWVhtgkw@mail.gmail.com>
 <20220614230949.GX29853@telsasoft.com>
 <540147.1655321524@sss.pgh.pa.us>
 <20220617020103.GG29853@telsasoft.com>
 <CA+Tgmob=sKD=Ag3TXQ+b7wWiJaoYaV-AgEddQYCxMNMCqmz=bA@mail.gmail.com>
 <1216673.1655475253@sss.pgh.pa.us>
 <20220622235845.GV22452@telsasoft.com>
 <33211367-6CAF-4B09-AA0E-020C62FAD8EF@yandex-team.ru>
 <20220624202824.GF22452@telsasoft.com>
 <AF678A5C-6ED7-4144-B264-68FEF31ED3A8@yandex-team.ru>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="ZfOjI3PrQbgiZnxM"
Content-Disposition: inline
In-Reply-To: <AF678A5C-6ED7-4144-B264-68FEF31ED3A8@yandex-team.ru>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20220627233003.GE19662%40telsasoft.com>
Precedence: bulk


--ZfOjI3PrQbgiZnxM
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Sat, Jun 25, 2022 at 03:34:49PM +0500, Andrey Borodin wrote:
> > On 25 Jun 2022, at 01:28, Justin Pryzby <pryzby@telsasoft.com> wrote:
> 
> >  this is my latest.
> > <0001-WIP-pg_upgrade-check-detect-old-polymorphics-from-pr.patch>
> 
> Let's rename "databases_with_old_polymorphics.txt" to somthing like "old_polymorphics.txt" or maybe even "incompatible_polymorphics_usage.txt"?
> I think you will come up with a better name, my point is here everythin is in "databases", and "old" doesn't describe essence of the problem.

> Also, let's check that oid of used functions belong to system catalog (<16384)? We don't care about user-defined functions with the same name.

Right now, we test
  =ANY(ARRAY['array_remove(anyarray,anyelement)',...]::regprocedure)

..which will find the system's array_remove, and not some other one, due to
ALWAYS_SECURE_SEARCH_PATH_SQL (which is also why ::regprocedure prints a
namespace for the non-system functions we're interested in displaying).

I had "transnsp.nspname='pg_catalog'", which was redundant, so I removed it.

I tested that this allows upgrades with aggregates on top of non-system
functions of the same name/args:

postgres=# CREATE FUNCTION array_append(anyarray, anyelement) RETURNS ANYARRAY LANGUAGE SQL AS $$ $$;
postgres=# CREATE AGGREGATE foo(anyelement) (sfunc=public.array_append, stype=anyarray, initcond='{}');

> And, probably, we can do this unconditionally:
> if (old_cluster.major_version >= 9500)
>         appendPQExpBufferStr(&old_polymorphics,
> Nothing bad will happen if we blacklist usage of nonexistent functions.

Nope, it's as I said: this would break pg_upgrade from older versions.

> I realized that my latest patch would break upgrades from old servers, which do
> not have array_position/s nor width_bucket, so ::reprocedure would fail.  Maybe
> Andrey's way is better (checking proname rather than its OID).

This fixes several error with the version test.

-- 
Justin

--ZfOjI3PrQbgiZnxM
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="0001-WIP-pg_upgrade-check-detect-old-polymorphics-from-pr.patch"

From 965deb773dd0170018f4b82d27420c61690ad690 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@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, and not after dumping the cluster and in the middle of
restoring it.

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 | 131 +++++++++++++++++++++++++++++++++++++
 1 file changed, 131 insertions(+)

diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
index ace7387edaf..20e7923c20c 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_incompatible_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);
 
+	/*
+	 * PG 14 changed polymorphic functions from anyarray to anycompatiblearray.
+	 */
+	if (GET_MAJOR_VERSION(old_cluster.major_version) <= 1300)
+		check_for_incompatible_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,130 @@ check_proper_datallowconn(ClusterInfo *cluster)
 }
 
 
+/*
+ *	check_for_incompatible_polymorphics()
+ *
+ *	Make sure nothing is using old polymorphic functions with
+ *	anyarray/anyelement rather than the new anycompatible variants.
+ */
+static void
+check_for_incompatible_polymorphics(ClusterInfo *cluster)
+{
+	PGresult   *res;
+	FILE	   *script = NULL;
+	char		output_path[MAXPGPATH];
+	PQExpBufferData old_polymorphics;
+
+	initPQExpBuffer(&old_polymorphics);
+
+	if (GET_MAJOR_VERSION(old_cluster.major_version) >= 903)
+		appendPQExpBufferStr(&old_polymorphics,
+							"'array_remove(anyarray,anyelement)', "
+							"'array_replace(anyarray,anyelement,anyelement)', ");
+
+	if (GET_MAJOR_VERSION(old_cluster.major_version) >= 905)
+		appendPQExpBufferStr(&old_polymorphics,
+							"'array_position(anyarray,anyelement)', "
+							"'array_position(anyarray,anyelement,integer)', "
+							"'array_positions(anyarray,anyelement)', "
+							"'width_bucket(anyelement,anyarray)', ");
+
+	appendPQExpBufferStr(&old_polymorphics,
+						"'array_append(anyarray,anyelement)', "
+						"'array_cat(anyarray,anyarray)', "
+						"'array_prepend(anyelement,anyarray)' ");
+
+	prep_status("Checking for incompatible polymorphic functions");
+
+	snprintf(output_path, sizeof(output_path), "%s/%s",
+			 log_opts.basedir,
+			 "incompatible_polymorphics.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_objkind,
+					i_objname;
+
+		/*
+		 * The query below hardcodes FirstNormalObjectId as 16384 rather than
+		 * interpolating that C #define into the query because, if that
+		 * #define is ever changed, the cutoff we want to use is the value
+		 * used by pre-version 14 servers, not that of some future version.
+		 */
+		res = executeQueryOrDie(conn,
+								/* Aggregate transition functions */
+								"SELECT 'aggregate' AS objkind, p.oid::regprocedure::text AS objname "
+								"FROM pg_proc AS p "
+								"JOIN pg_aggregate AS a ON a.aggfnoid=p.oid "
+								"JOIN pg_proc AS transfn ON transfn.oid=a.aggtransfn "
+								"WHERE p.oid >= 16384 "
+								/* Before v11, used proisagg=true, and afterwards uses prokind='a' */
+								"AND a.aggtransfn = ANY(ARRAY[%s]::regprocedure[]) "
+
+								/* Aggregate final functions */
+								"UNION ALL "
+								"SELECT 'aggregate' AS objkind, p.oid::regprocedure::text AS objname "
+								"FROM pg_proc AS p "
+								"JOIN pg_aggregate AS a ON a.aggfnoid=p.oid "
+								"JOIN pg_proc AS finalfn ON finalfn.oid=a.aggfinalfn "
+								"WHERE p.oid >= 16384 "
+								"AND a.aggfinalfn = ANY(ARRAY[%s]::regprocedure[]) "
+
+								/* Operators */
+								"UNION ALL "
+								"SELECT 'operator' AS objkind, op.oid::regoperator::text AS objname "
+								"FROM pg_operator AS op "
+								"WHERE op.oid >= 16384 "
+								"AND oprcode = ANY(ARRAY[%s]::regprocedure[]);",
+								old_polymorphics.data, old_polymorphics.data, old_polymorphics.data);
+
+		ntups = PQntuples(res);
+
+		i_objkind = PQfnumber(res, "objkind");
+		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_objkind),
+					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 internal polymorphic\n"
+				"functions with arguments of type 'anyarray' or 'anyelement'. These user-defined\n"
+				"objects must be dropped before upgrading and restored afterwards to refer to the\n"
+				"corresponding functions with arguments of type 'anycompatiblearray' and\n"
+				"'anycompatible'.  A list of the problem objects is in the file:\n"
+				"    %s\n\n", output_path);
+	}
+	else
+		check_ok();
+
+	termPQExpBuffer(&old_polymorphics);
+}
+
 /*
  *	check_for_prepared_transactions()
  *
-- 
2.17.1


--ZfOjI3PrQbgiZnxM--