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: <20230411212737.phtzffycglbdhmpx@c19>
Subject: RE: [PATCH] Support % wildcard in extension upgrade filenames
Date: Thu, 13 Apr 2023 18:28:10 -0400
Message-ID: <001e01d96e57$3ac6fdb0$b054f910$@pcorp.us>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: 
 AQGYmaaUCwtlrmE55CHyCSUXu9VHkgKopfE4AVtxnv8DArAuyQGfbhARAm8A8U4CpUA3zwGDW9/EAvd7mHsCV9qbi68HDEvQ
Content-Language: en-us
Archived-At: 
 <https://www.postgresql.org/message-id/001e01d96e57%243ac6fdb0%24b054f910%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?