From: "Regina Obe" <lr@pcorp.us>
To: <strk@kbt.io>
Cc: "'Yurii Rashkovskii'" <yrashk@gmail.com>,
	"'Tom Lane'" <tgl@sss.pgh.pa.us>,
	"'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> <CA+RLCQwWpMuKtqg7eBOjvzpJc-hE708WpHG2=z5B969xncQS3w@mail.gmail.com> <20230409204629.sf4fptx672iehcau@c19> <000501d96c23$0f05bdf0$2d1139d0$@pcorp.us> <20230411184823.s3cctaf63qvfeqlj@c19> <006201d96cb5$3d23b150$b76b13f0$@pcorp.us> <20230411212737.phtzffycglbdhmpx@c19> 
In-Reply-To: 
Subject: RE: [PATCH] Support % wildcard in extension upgrade filenames
Date: Thu, 13 Apr 2023 18:32:55 -0400
Message-ID: <002201d96e57$e50461a0$af0d24e0$@pcorp.us>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AQGYmaaUCwtlrmE55CHyCSUXu9VHkgKopfE4AVtxnv8DArAuyQGfbhARAm8A8U4CpUA3zwGDW9/EAvd7mHsCV9qbi68HDEvQgAAGLPA=
Content-Language: en-us
Archived-At: <https://www.postgresql.org/message-id/002201d96e57%24e50461a0%24af0d24e0%24%40pcorp.us>
Precedence: bulk

> Here are my thoughts of how this can work to satisfy our specific needs
and
> that of others who have many micro versions.
> 
> 1) We define an additional file.  I'll call this a paths file
> 
> So for example postgis would have a
> 
> postgis.paths file
> 
> The format of the path file would be of the form
> 
> <version pattern1>,<version pattern2> => 3.3.0--3.4.0
> 
> It will also allow a wildcard option
> % => ANY--3.4.0.sql
> 
> So a postgis.paths with multiple lines might look like
> 
> 3.2.0,3.2.1 => 3.2.2--3.3.0
> 3.3.% => 3.3--3.4.0
> % => ANY--3.4.0
> 
> 2) The order of precedence would be:
> 
> a) physical files are always used first
> b) If no physical path is present on disk, then it looks at a
<component>.paths
> file to formulate virtual paths
> c) Longer mappings are preferred over shorter mappings
> 
> So that means the % => ANY--3.4.0 would be the path of last resort
> 
> Let's say our current installed version of postgis is  postgis VERSION
3.2.0
> 
> The above path formulated would be
> 
> 3.2.0 -> 3.3.0 -> 3.4.0
> The simulated scripts used to get there would be
> 
> postgis--3.2.2--3.3.0.sql
> postgis--3.3.0--3.4.0.sql
> 
> 
> This however does not solve the issue of downgrades, which admittedly
> postgis is not concerned about since we have accounted for that in our
> internal scripts.
> 
> So we might have issue with having a bear:  %.  If we don't allow a bear %
> 
> Then our postgis patterns might look something like:
> 
> 3.%, 2.% => ANY --3.4.0
> 
> Which would mean 3.0.1, 3.0.2, 3.2.etc would all use the same script.
> 
> Which would still be a major improvement from what we have today and
> minimizes likeliness of downgrade footguns.
> 
> Thoughts anyone?
> 

Minor correction scripts to get from 3.2.0 to 3.4.0 would be:

postgis--3.2.2--3.3.0.sql
postgis--3.3--3.4.0.sql