From: "Regina Obe" <lr@pcorp.us>
To: "'Eric Ridge'" <eebbrr@gmail.com>,
	"'Tom Lane'" <tgl@sss.pgh.pa.us>
Cc: "'Robert Haas'" <robertmhaas@gmail.com>,
	<strk@kbt.io>,
	"'Regina Obe'" <r@pcorp.us>,
	<pgsql-hackers@lists.postgresql.org>
References: <YgakFklJyM5pNdt+@c19> <20221117095734.igldlk6kngr6ogim@c19>
 <166914379479.1121.7549798686571352890.pgcf@coridan.postgresql.org>
 <55512.1673304709@sss.pgh.pa.us> <20230110205259.htcuhg3t7shd367d@c19>
 <387183.1673394631@sss.pgh.pa.us>
 <CA+TgmobDy0sasmzOFkCcKXC3XeQZzRNwbaQRJ752tF1i11BRKQ@mail.gmail.com>
 <20230308122736.e2mx4e3paxbabcnw@c19>
 <CA+TgmoYqK6nfP15SjuyO6t5jOmymG=qO7JOOVJdTOj96L0XJ1Q@mail.gmail.com>
 <20230313122210.r7mnwawjhfklcp7w@c19>
 <003201d955dc$7770af60$66520e20$@pcorp.us>
 <F2820A88-F29B-4F0A-9430-7D9AE33E953C@gmail.com>
 <CA+TgmobhF+VOCLETv3ewNTdP6Y04V1Lh5jy1N45VRJoNER8PNw@mail.gmail.com>
 <86875FF7-0592-4F76-8CBA-8B5AE632F1B2@gmail.com>
 <2297723.1682972670@sss.pgh.pa.us>
 <01919CC8-C27C-46DF-A8D5-2FF11A264733@gmail.com>
In-Reply-To: <01919CC8-C27C-46DF-A8D5-2FF11A264733@gmail.com>
Subject: RE: [PATCH] Support % wildcard in extension upgrade filenames
Date: Mon, 1 May 2023 17:50:49 -0400
Message-ID: <006501d97c76$fecb9400$fc62bc00$@pcorp.us>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: 
 AQGYmaaUCwtlrmE55CHyCSUXu9VHkgKopfE4AVtxnv8DArAuyQGehsdEA0I4l/UBweK/cwJAEwUVAUHBNksCLq4DngE8qUWDAYmn3ToC3MypDAHO1SnXAkiu8yAC0El7Tq7Ijoeg
Content-Language: en-us
Archived-At: 
 <https://www.postgresql.org/message-id/006501d97c76%24fecb9400%24fc62bc00%24%40pcorp.us>
Precedence: bulk

> It isn't.  ZDB, and I think (at least) PostGIS, have their own "version()"
function.
> Keeping everything the same version keeps me "sane" and eliminates a class
> of round-trip questions with users.
> 
Yes we have several version numbers and yes we too like to keep the
extension version the same, cause it's the only thing that is universally
consistent across all PostgreSQL extensions.

Yes we have our own internal version functions. One for PostGIS (has the
true lib version file) and a script version and we have logic to make sure
they are aligned.  We even have versions for our dependencies (PROJ, GEOS,
GDAL) cause behavior of PostGIS changes based on versions of those.
This goes for each extension we package that has a lib file (postgis,
postgis_raster, postgis_topology, postgis_sfcgal)

In addition to that we also have a version for PostgreSQL (that the scripts
were installed on). To catch cases when a pg_upgrade is needed to go from
3.3.0 to 3.3.0.
Yes we need same version upgrades (particularly because of pg_upgrade).
Sandro and I were talking about this.  This is something we do in our
postgis_extensions_upgrade()  (basically forcing an upgrade to a version
that does nothing, so we can force an upgrade to 3.3.0 again) to make up for
this limitation in extension model.

The reason for that is features get exposed based on version of PostgreSQL
you are running.
So in 3.3.0 we leveraged the new gist fast indexing build, which is only
enabled for users running PostgreSQL 15 and above.

What usually happens is someone has PostGIS 3.3.0 say on PG 14, they
pg_upgrade to PG 15 but they are running with PG 14 scripts 
So they are not taking advantage of the new PG 15 features until they do a 

SELECT postgis_extensions_upgrade();

So this is why we need the DO commands for scenarios like this.

> One of my desires is that the on-disk .so's filename be associated with
the
> pg_extension entry and not Each. Individual. Function.  There's a few
> extensions that like to version the on-disk .so's filename which means a
> CREATE OR REPLACE for every function on every extension version bump.
> That forces an upgrade script even if the schema didn't technically change
and
> also creates the need for bespoke tooling around extension.sql and
> upgrade.sql scripts.
> 
> But I don't want to derail this thread.
> 
> eric=

This is more or less the reason why we had to do CREATE OR REPLACE for all
our functions.
In the past we minor versioned our lib files so we had postgis-2.4,
postgis-2.5

At 3.0 after much in-fighting (battle between convenience of developers vs.
regular old users just wanting to use PostGIS and my frustration trying to
hold peoples hands thru pg_upgrade), we settled on major version for the lib
file, with option for developers to still keep the minor.

So default install will be postgis-3 for life of 3 series and become
postgis-4 when 4 comes along (hopefully not for another 10 years).
Completely stripping the version we decided not to do cause with the change
we have a whole baggage of legacy functions we needed to stub as we removed
them so pg_upgrade will work more or less seamlessly.  So come postgis-4
these stubs will be removed.

Our CI bots however many of them do use the minor versionings 3.1, 3.2, 3.3
etc, cause it's easier to test upgrades and do regressions.
And many PostGIS developers do the same.  So a replace of all functions is
still largely needed.  This is one of the reasons the whole chained upgrade
path never worked for us and why we settled on one script to handle
everything.