On Fri, Jun 17, 2022 at 10:14:13AM -0400, Tom Lane wrote: > Robert Haas <robertmh...@gmail.com> writes: > > On Thu, Jun 16, 2022 at 10:01 PM Justin Pryzby <pry...@telsasoft.com> wrote: > >> To me, oid>=16384 seems more hard-wired than namespace!='pg_catalog'. > > > Extensions can be installed into pg_catalog, but they can't get > > low-numbered OIDs. > > Exactly. (To be clear, I had in mind writing something involving > FirstNormalObjectId, not that you should put literal "16384" in the > code.)
Actually, 16384 is already used in two other places in check.c, so ... done like that for consistency. Also fixes parenthesis, typos, and renames vars.
>From 44a66cddbc1f32c16ee7229b85fe76aada4689aa Mon Sep 17 00:00:00 2001 From: Justin Pryzby <pryz...@telsasoft.com> Date: Fri, 10 Jun 2022 11:17:36 -0500 Subject: [PATCH v3] 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 | 124 +++++++++++++++++++++++++++++++++++++ 1 file changed, 124 insertions(+) diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c index ace7387edaf..da1781237ba 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); + /* + * PG 14 changed polymorphic functions from anyarray to anycompatiblearray. + */ + 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,123 @@ 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_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 " + "JOIN pg_namespace AS transnsp ON transnsp.oid=transfn.pronamespace " + "WHERE p.oid >= 16384 " + /* 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 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 " + "JOIN pg_namespace AS finalnsp ON finalnsp.oid=finalfn.pronamespace " + "WHERE p.oid >= 16384 " + "AND finalnsp.nspname = 'pg_catalog' " + "AND a.aggfinalfn = ANY(ARRAY[" OLD_POLYMORPHICS "]::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[ " OLD_POLYMORPHICS "]::regprocedure[]);"); + + 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(); +} + /* * check_for_prepared_transactions() * -- 2.17.1