From: Justin Pryzby <pryzby@telsasoft.com>
To: Andrey Borodin <x4mmm@yandex-team.ru>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: Petr Vejsada <pve@paymorrow.com>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: pg_upgrade (12->14) fails on aggregate
Date: Fri, 24 Jun 2022 15:28:24 -0500
Message-ID: <20220624202824.GF22452@telsasoft.com> (raw)
In-Reply-To: <33211367-6CAF-4B09-AA0E-020C62FAD8EF@yandex-team.ru>
References: <3383880.QJadu78ljV@vejsadalnx>
<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>
On Fri, Jun 24, 2022 at 11:43:18PM +0500, Andrey Borodin wrote:
> > On 23 Jun 2022, at 04:58, Justin Pryzby <pryzby@telsasoft.com> wrote:
> >
> > On Fri, Jun 17, 2022 at 10:14:13AM -0400, Tom Lane wrote:
> >> Robert Haas <robertmhaas@gmail.com> writes:
> >>> On Thu, Jun 16, 2022 at 10:01 PM Justin Pryzby <pryzby@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 ...
>
> Yes, but it's a third copy of the comment ("* The query below hardcodes FirstNormalObjectId as 16384 rather than") across the file.
>
> Also, we can return slightly more information about found objects. For example, operator will look like "operator: ||". At least we can get nspname and oid. And, maybe return type for aggregator and leftarg\rightarg types for operator?
But what I wrote already shows what you want.
In database: postgres
aggregate: public.array_accum(anyelement)
operator: public.!@#(anyarray,anyelement)
In my testing, this works great - it shows what you need to put in your DROP
command. If you try it and still wanted the OID, I'll add it for consistency
with check_for_user_defined_{encoding_conversions,postfix_ops}
> BTW comment /* Before v11, used proisagg=true, and afterwards uses prokind='a' */ seems interesting, but irrelevant. We join with pg_aggregate anyway.
Yes, that's why the query doesn't need to include that.
Something is broken in my old clusters and I can't test all the upgrades right
now, but this is my latest.
Attachments:0001-WIP-pg_upgrade-check-detect-old-polymorphics-from-pr.patch (text/x-diff, 6.4K) download
From 87132a8eb7310c4f00d33ea09d97fab481ea1173 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 | 134 +++++++++++++++++++++++++++++++++++++
1 file changed, 134 insertions(+)
diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.cindex ace7387edaf..8b8509b6aa5 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,133 @@ 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];+ PQExpBufferData old_polymorphics;++ initPQExpBuffer(&old_polymorphics);++ appendPQExpBufferStr(&old_polymorphics,+ "'array_append(anyarray,anyelement)', "+ "'array_cat(anyarray,anyarray)', "+ "'array_prepend(anyelement,anyarray)', "+ "'array_remove(anyarray,anyelement)', "+ "'array_replace(anyarray,anyelement,anyelement)' ");++ if (old_cluster.major_version >= 9500)+ appendPQExpBufferStr(&old_polymorphics,+ ", "+ "'array_position(anyarray,anyelement)', "+ "'array_position(anyarray,anyelement,integer)', "+ "'array_positions(anyarray,anyelement)', "+ "'width_bucket(anyelement,anyarray)' ");++ 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");++ 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[%s]::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[%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
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: pgsql-hackers@postgresql.org
Cc: pryzby@telsasoft.com, x4mmm@yandex-team.ru, tgl@sss.pgh.pa.us, robertmhaas@gmail.com, david.g.johnston@gmail.com, pve@paymorrow.com, pgsql-hackers@lists.postgresql.org
Subject: Re: pg_upgrade (12->14) fails on aggregate
In-Reply-To: <20220624202824.GF22452@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox