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"