Subject: RE: [PATCH] Support % wildcard in extension upgrade filenames

From: "Regina Obe" <[email-masked]>
Date: Tue, 10 Jan 2023 23:09:23 -0500

> Sandro Santilli <[email-masked]> writes:
> > On Mon, Jan 09, 2023 at 05:51:49PM -0500, Tom Lane wrote:
> >> ... you still need one script file for each supported upgrade step
> 
> > That's exactly the problem we're trying to solve here.
> > The include support is nice on itself, but won't solve our problem.
> 
> The script-file-per-upgrade-path aspect solves a problem that you have,
> whether you admit it or not; I think you simply aren't realizing that
because
> you have not had to deal with the consequences of your proposed feature.
> Namely that you won't have any control over what the backend will try to
do in
> terms of upgrade paths.

It would be nice if there were some way to apply at least a range match to
upgrades or explicitly state in the control file what paths should be
applied for an upgrade.
Ultimately as Sandro mentioned, it's the 1000 files issue we are trying to
address.

The only way we can fix that in the current setup, is to move to a minor
version mode which means we can 
never do micro updates.

> As an example, suppose that a database has foo 4.0 installed, and the DBA
> decides to try to downgrade to 3.0.  With the system as it stands, if
you've
> provided foo--4.0--3.0.sql then the conversion will go through, and
presumably
> it will work because you tested that that script does what it is intended
to.  If
> you haven't provided any such downgrade script, then ALTER EXTENSION
> UPDATE will say "Sorry Dave, I'm afraid I can't do that" and no harm is
done.
In our case we've already addressed that in our script, because our upgrades
don't rely on what 
extension model tells us is the version, ultimately what our
postgis..version() tells us is the true determinate (one for the lib file
and one for the script).
But you are right, that's a selfish way of thinking about it, cause we have
internal plumbing to handle that issue already and other projects probably
don't.

What I was hoping for was to having a section in the control file to say
something like

Upgrade patterns: 3.0.%--4.0.0.sql, 2.0.%--4.0.0.sql

(and perhaps some logic to guarantee no way to match two patterns)
So you wouldn't be able to have a set of patterns like

Upgrade patterns: %--4.0.0.sql, 3.0.%--4.0.0.sql, 2.0.%--4.0.0.sql

That would allow your proposed include something or other to work and for us
to be able to use that, and still reduce our 
file footprint.

I'd even settle for absolute paths stated in the control file if we can
dispense with the need a file path to push you forward.

In that mode, your downgrade issue would never happen even with the way
people normally create scripts now.

> So I really think this is a case of "be careful what you ask for, you
might get it".
> Even if PostGIS is willing to put in the amount of infrastructure legwork
needed
> to make such a design bulletproof, I'm quite sure nobody else will manage
to
> use such a thing successfully. I'd rather spend our development effort on
a
> feature that has more than one use-case.
> 
> 			regards, tom lane

I think you are underestimating the number of extensions that have this
issue and could benefit (agree not in the current incarnation of the patch).
It's not just PostGIS, it's pgRouting (has 56 files), h3 extension (has 37
files in last release, most of them a do nothing, except at the minor update
steps) that have the same issue (and both pgRouting and h3 do have little
bitty script updates that follows the best practice way of doing this).
For pgRouting alone there are 56 files for the last release (of which it can
easily be reduced to about 5 files if the paths could be explicitly stated
in the control file).

Yes all those extensions should dispense with their dreams of having micro
updates (I honestly wish they would).
 
Perhaps I'm a little obsessive, but it annoys me to see 1000s of files in my
extension folder, when I know even if I followed best practice I only need
like 5 files for each extension.

And as a packager to have to ship these files is even more annoying.

The reality is for micros, no one ships new functions (or at least shouldn't
be), so all functions just need to be replaced perhaps with a micro update
file you propose.

Heck if we could even have the option to itemize our own upgrade file paths
explicitly in the control file, 

Like:

paths: 3.0.1--4.0.0 = 3--4.0.0.sql, 3.0.2--4.0.0 = 3--4.0.0.sql,
2--4.0.0.sql = 2.0.2--4.0.0.sql

I'd be happy, and PostgreSQL can do the math pretending there are files it
thinks it needs.

So if we could somehow rework this patch perhaps adding something in the
control to explicitly state the upgrade paths.

I think that would satisfy your concern? And I'd be eternally grateful. 

Thanks,
Regina

---

From: 'Sandro Santilli' <[email-masked]>
Date: Wed, 11 Jan 2023 11:16:36 +0100

> version mode which means we can 

Or just not with standard PostgreSQL syntax, because we can of course
do upgrades using the `SELECT postgis_extensions_upgrade()` call at
the moment, which, if you ask me, sounds MORE dangerous than the
wildcard upgrade approach because the _implementation_ of that
function will always be the OLD implementation, never the NEW one,
so if bogus cannot be fixed by a new release w/out a way to upgrade
there...

--strk;

---

From: "Gregory Stark (as CFM)" <[email-masked]>
Date: Mon, 6 Mar 2023 14:54:35 -0500

This patch too is conflicting on meson.build. Are these two patches
interdependent?

This one looks a bit more controversial. I'm not sure if Tom has been
convinced and I haven't seen anyone else speak up.

Perhaps this needs a bit more discussion of other options to solve
this issue. Maybe it can just be solved with multiple one-line scripts
that call to a master script?

-- 
Gregory Stark
As Commitfest Manager

---

From: "Regina Obe" <[email-masked]>
Date: Mon, 6 Mar 2023 16:37:39 -0500

> Perhaps this needs a bit more discussion of other options to solve this issue.
> Maybe it can just be solved with multiple one-line scripts that call to a master
> script?
> 
> --
> Gregory Stark
> As Commitfest Manager

They edit the same file yes so yes conflicts are expected.  
The wildcard one, Tom was not convinced, so I assume we'll need to 
go a couple more rounds on it and hopefully we can get something that will not be so controversial.

I don't think the schema qualifying required extension feature is controversial though so I think that should be able to go and we'll rebase our other patch after that.

Thanks,
Regina

---

From: Sandro Santilli <[email-masked]>
Date: Tue, 7 Mar 2023 10:00:13 +0100

On Mon, Mar 06, 2023 at 02:54:35PM -0500, Gregory Stark (as CFM) wrote:
> This patch too is conflicting on meson.build.

I'm attaching a rebased version to this email.

> Maybe it can just be solved with multiple one-line scripts

Not really, as the problem we are trying to solve is the need
to install many files, and the need to foresee any possible
future bug-fix release we might want to support upgrades from.

PostGIS is already installing zero-line scripts to upgrade
from <old_version> to a virtual "ANY" version which we then
use to have a single ANY--<new_version> upgrade path, but we
are still REQUIRED to install a file for any <old_version> we
want to allow upgrade from, which is what this patch is aimed
at fixing.

--strk;
>From ffefd039f24e3def8d2216b3ac7875d0b6feb8fb Mon Sep 17 00:00:00 2001
From: Sandro Santilli <[email-masked]>
Date: Wed, 14 Sep 2022 11:10:10 +0200
Subject: [PATCH v1] Allow wildcard (%) in extension upgrade paths

A wildcard character "%" will be accepted in the
"source" side of the upgrade script and be considered
usable to upgrade any version to the "target" side.

Includes regression test and documentation.
---
 doc/src/sgml/extend.sgml                      |  8 ++++
 src/backend/commands/extension.c              | 42 ++++++++++++++++---
 src/test/modules/test_extensions/Makefile     |  6 ++-
 .../expected/test_extensions.out              | 15 +++++++
 src/test/modules/test_extensions/meson.build  |  3 ++
 .../test_extensions/sql/test_extensions.sql   |  7 ++++
 .../test_ext_wildcard1--%--2.0.sql            |  6 +++
 .../test_ext_wildcard1--1.0.sql               |  6 +++
 .../test_ext_wildcard1.control                |  3 ++
 9 files changed, 88 insertions(+), 8 deletions(-)
 create mode 100644 src/test/modules/test_extensions/test_ext_wildcard1--%--2.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext_wildcard1--1.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext_wildcard1.control

diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml
index b70cbe83ae..f1f0ae1244 100644
--- a/doc/src/sgml/extend.sgml
+++ b/doc/src/sgml/extend.sgml
@@ -1081,6 +1081,14 @@ SELECT pg_catalog.pg_extension_config_dump('my_config', 'WHERE NOT standard_entr
      <literal>1.1</literal>).
     </para>
 
+    <para>
+     The literal value <literal>%</literal> can be used as the
+     <replaceable>old_version</replaceable> component in an extension
+     update script for it to match any version. Such wildcard update
+     scripts will only be used when no explicit path is found from
+     old to target version.
+    </para>
+
     <para>
      Given that a suitable update script is available, the command
      <command>ALTER EXTENSION UPDATE</command> will update an installed extension
diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index 02ff4a9a7f..6df0fd403a 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -130,6 +130,7 @@ static void ApplyExtensionUpdates(Oid extensionOid,
 								  bool cascade,
 								  bool is_create);
 static char *read_whole_file(const char *filename, int *length);
+static bool file_exists(const char *name);

 /*
@@ -893,7 +894,14 @@ execute_extension_script(Oid extensionOid, ExtensionControlFile *control,
 	if (from_version == NULL)
 		elog(DEBUG1, "executing extension script for \"%s\" version '%s'", control->name, version);
 	else
+	{
+		if ( ! file_exists(filename) )
+		{
+			/* if filename does not exist, try wildcard */
+			filename = get_extension_script_filename(control, "%", version);
+		}
 		elog(DEBUG1, "executing extension script for \"%s\" update from version '%s' to '%s'", control->name, from_version, version);
+	}
 
 	/*
 	 * If installing a trusted extension on behalf of a non-superuser, become
@@ -1217,14 +1225,19 @@ identify_update_path(ExtensionControlFile *control,
 
 	/* Find shortest path */
 	result = find_update_path(evi_list, evi_start, evi_target, false, false);
+	if (result != NIL)
+		return result;
 
-	if (result == NIL)
-		ereport(ERROR,
-				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
-				 errmsg("extension \"%s\" has no update path from version \"%s\" to version \"%s\"",
-						control->name, oldVersion, newVersion)));
+	/* Find wildcard path, if no explicit path was found */
+	evi_start = get_ext_ver_info("%", &evi_list);
+	result = find_update_path(evi_list, evi_start, evi_target, false, false);
+	if (result != NIL)
+		return result;
 
-	return result;
+	ereport(ERROR,
+			(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+			 errmsg("extension \"%s\" has no update path from version \"%s\" to version \"%s\"",
+					control->name, oldVersion, newVersion)));
 }
 
 /*
@@ -3395,3 +3408,20 @@ read_whole_file(const char *filename, int *length)
 	buf[*length] = '\0';
 	return buf;
 }
+
+static bool
+file_exists(const char *name)
+{
+	struct stat st;
+
+	Assert(name != NULL);
+
+	if (stat(name, &st) == 0)
+		return !S_ISDIR(st.st_mode);
+	else if (!(errno == ENOENT || errno == ENOTDIR || errno == EACCES))
+		ereport(ERROR,
+				(errcode_for_file_access(),
+				 errmsg("could not access file \"%s\": %m", name)));
+
+	return false;
+}
diff --git a/src/test/modules/test_extensions/Makefile b/src/test/modules/test_extensions/Makefile
index c3139ab0fc..4fe2d82b6e 100644
--- a/src/test/modules/test_extensions/Makefile
+++ b/src/test/modules/test_extensions/Makefile
@@ -6,14 +6,16 @@ PGFILEDESC = "test_extensions - regression testing for EXTENSION support"
 EXTENSION = test_ext1 test_ext2 test_ext3 test_ext4 test_ext5 test_ext6 \
             test_ext7 test_ext8 test_ext_cine test_ext_cor \
             test_ext_cyclic1 test_ext_cyclic2 \
-            test_ext_evttrig
+            test_ext_evttrig test_ext_wildcard1
 DATA = test_ext1--1.0.sql test_ext2--1.0.sql test_ext3--1.0.sql \
        test_ext4--1.0.sql test_ext5--1.0.sql test_ext6--1.0.sql \
        test_ext7--1.0.sql test_ext7--1.0--2.0.sql test_ext8--1.0.sql \
        test_ext_cine--1.0.sql test_ext_cine--1.0--1.1.sql \
        test_ext_cor--1.0.sql \
        test_ext_cyclic1--1.0.sql test_ext_cyclic2--1.0.sql \
-       test_ext_evttrig--1.0.sql test_ext_evttrig--1.0--2.0.sql
+       test_ext_evttrig--1.0.sql test_ext_evttrig--1.0--2.0.sql \
+       test_ext_wildcard1--1.0.sql test_ext_wildcard1--%--2.0.sql \
+
 
 REGRESS = test_extensions test_extdepend
 
diff --git a/src/test/modules/test_extensions/expected/test_extensions.out b/src/test/modules/test_extensions/expected/test_extensions.out
index 821fed38d1..1c4dc5be42 100644
--- a/src/test/modules/test_extensions/expected/test_extensions.out
+++ b/src/test/modules/test_extensions/expected/test_extensions.out
@@ -312,3 +312,18 @@ Objects in extension "test_ext_cine"
  table ext_cine_tab3
 (9 rows)
 
+CREATE EXTENSION test_ext_wildcard1;
+SELECT ext_wildcard1_version();
+ ext_wildcard1_version 
+-----------------------
+ 1.0
+(1 row)
+
+ALTER EXTENSION test_ext_wildcard1 UPDATE TO '2.0';
+SELECT ext_wildcard1_version();
+ ext_wildcard1_version 
+-----------------------
+ 2.0
+(1 row)
+
+DROP EXTENSION test_ext_wildcard1;
diff --git a/src/test/modules/test_extensions/meson.build b/src/test/modules/test_extensions/meson.build
index c3af3e1721..026be6a879 100644
--- a/src/test/modules/test_extensions/meson.build
+++ b/src/test/modules/test_extensions/meson.build
@@ -30,6 +30,9 @@ test_install_data += files(
   'test_ext_evttrig--1.0--2.0.sql',
   'test_ext_evttrig--1.0.sql',
   'test_ext_evttrig.control',
+  'test_ext_wildcard1--1.0.sql',
+  'test_ext_wildcard1--%--2.0.sql',
+  'test_ext_wildcard1.control',
 )
 
 tests += {
diff --git a/src/test/modules/test_extensions/sql/test_extensions.sql b/src/test/modules/test_extensions/sql/test_extensions.sql
index 41b6cddf0b..071845e8df 100644
--- a/src/test/modules/test_extensions/sql/test_extensions.sql
+++ b/src/test/modules/test_extensions/sql/test_extensions.sql
@@ -209,3 +209,10 @@ CREATE EXTENSION test_ext_cine;
 ALTER EXTENSION test_ext_cine UPDATE TO '1.1';
 
 \dx+ test_ext_cine
+
+
+CREATE EXTENSION test_ext_wildcard1;
+SELECT ext_wildcard1_version();
+ALTER EXTENSION test_ext_wildcard1 UPDATE TO '2.0';
+SELECT ext_wildcard1_version();
+DROP EXTENSION test_ext_wildcard1;
diff --git a/src/test/modules/test_extensions/test_ext_wildcard1--%--2.0.sql b/src/test/modules/test_extensions/test_ext_wildcard1--%--2.0.sql
new file mode 100644
index 0000000000..75154e5c55
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_wildcard1--%--2.0.sql
@@ -0,0 +1,6 @@
+/* src/test/modules/test_extensions/test_ext_wildcard1--%--2.0.sql */
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION test_ext_wildcard1 UPDATE TO '2.0'" to load this file. \quit
+
+CREATE OR REPLACE FUNCTION ext_wildcard1_version() returns TEXT
+AS 'SELECT 2.0' LANGUAGE 'sql';
diff --git a/src/test/modules/test_extensions/test_ext_wildcard1--1.0.sql b/src/test/modules/test_extensions/test_ext_wildcard1--1.0.sql
new file mode 100644
index 0000000000..a69e791fda
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_wildcard1--1.0.sql
@@ -0,0 +1,6 @@
+/* src/test/modules/test_extensions/test_ext_wildcard1--1.0.sql */
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "CREATE EXTENSION test_ext_wildcard1" to load this file. \quit
+
+CREATE FUNCTION ext_wildcard1_version() returns TEXT
+AS 'SELECT 1.0' LANGUAGE 'sql';
diff --git a/src/test/modules/test_extensions/test_ext_wildcard1.control b/src/test/modules/test_extensions/test_ext_wildcard1.control
new file mode 100644
index 0000000000..0c2fc6fca6
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_wildcard1.control
@@ -0,0 +1,3 @@
+comment = 'Test extension wildcard 1'
+default_version = '1.0'
+relocatable = true
-- 
2.34.1