Allow an extention to be updated without a script

2023-01-30 Thread Yugo NAGATA
Hi hackers,

I propose to add a new option "updates_without_script" to extension's
control file which a list of updates that do not need update script. 
This enables to update an extension by ALTER EXTENSION even if the
extension module doesn't provide the update script.

Currently, even when we don't need to execute any command to update an
extension from one version to the next, we need to provide an update
script that doesn't contain any command. Preparing such meaningless
files are sometimes annoying.

The attached patch introduces a new option "updates_without_script"
into extension control file. This specifies a list of such updates
following the pattern 'old_version--target_version'. 

For example, 

 updates_without_script = '1.1--1.2, 1.3--1.4'
 
means that updates from version 1.1 to version 1.2 and from version 1.3
to version 1.4 don't need an update script.  In this case, users don't
need to prepare update scripts extension--1.1--1.2.sql and
extension--1.3--1.4.sql if it is not necessary to execute any commands.

The updated path of an extension is determined based on both the filenames
of update scripts and the list of updates specified in updates_without_script.
Presence of update script has higher priority than the option. Therefore,
if an update script is provided, the script will be executed even if this
update is specified in updates_without_script.

What do you think of this feature?
Any feedback would be appreciated.

Regards,
Yugo Nagata

-- 
Yugo NAGATA 
>From 938e15b28f66015044b559e4c523fc74590691fc Mon Sep 17 00:00:00 2001
From: Yugo Nagata 
Date: Mon, 30 Jan 2023 17:36:55 +0900
Subject: [PATCH] Allow an extention to be updated without a script

When we don't need to execute any command to update an extension from one
version to the next, we can specify a list of such updates following
the pattern 'old_version--target_version' into a new option
updates_without_script.  For example, specifying '1.1--1.2, 1.3--1.4'
means updates from version 1.1 to version 1.2, and from version 1.3
to version 1.4 don't need an update script.  User doesn't need to
provide an update script that doesn't contain any command for such
updates.

The updated path is determined based on both the names of update scripts
and the list in updates_without_script.  If an update script is provided,
the script will be executed even if this update is specified in
updates_without_script.
---
 doc/src/sgml/extend.sgml  |  30 +++-
 src/backend/commands/extension.c  | 161 +++---
 src/test/modules/test_extensions/Makefile |   3 +-
 .../expected/test_extensions.out  |   6 +
 .../test_extensions/sql/test_extensions.sql   |   8 +
 .../test_extensions/test_ext9--1.0.sql|   0
 .../test_extensions/test_ext9--2.0--3.0.sql   |   0
 .../modules/test_extensions/test_ext9.control |   5 +
 8 files changed, 182 insertions(+), 31 deletions(-)
 create mode 100644 src/test/modules/test_extensions/test_ext9--1.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext9--2.0--3.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext9.control

diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml
index 46e873a166..1c4c978264 100644
--- a/doc/src/sgml/extend.sgml
+++ b/doc/src/sgml/extend.sgml
@@ -807,6 +807,17 @@ RETURNS anycompatible AS ...

   
  
+
+ 
+  updates_without_script (string)
+  
+   
+A list of updates that do not need update scripts following the pattern
+old_version--target_version,
+for example updates_without_script = '1.1--1.2, 1.3--1.4'.
+   
+  
+ 
 
 
 
@@ -818,8 +829,9 @@ RETURNS anycompatible AS ...
  Secondary control files follow the same format as the primary control
  file.  Any parameters set in a secondary control file override the
  primary control file when installing or updating to that version of
- the extension.  However, the parameters directory and
- default_version cannot be set in a secondary control file.
+ the extension.  However, the parameters directory,
+ default_version, and updates_without_script
+ cannot be set in a secondary control file.
 
 
 
@@ -1092,6 +1104,20 @@ SELECT pg_catalog.pg_extension_config_dump('my_config', 'WHERE NOT standard_entr
  objects, they are automatically dissociated from the extension.
 
 
+
+ If you don't need to execute any command to update an extension from one
+ version to the next, provide an update script that doesn't contain
+ any command or specify a list of such updates following the pattern
+ old_version--target_version
+ into updates_without_script.  For example,
+ updates_without_script = '1.1--1.2, 1.3--1.4'
+ means updates from version 1.1 to ver

Re: Allow an extention to be updated without a script

2023-01-30 Thread Tom Lane
Yugo NAGATA  writes:
> Currently, even when we don't need to execute any command to update an
> extension from one version to the next, we need to provide an update
> script that doesn't contain any command. Preparing such meaningless
> files are sometimes annoying.

If you have no update script, why call it a new version?  The point
of extension versions is to distinguish different states of the
extension's SQL objects.  We do not consider mods in underlying C code
to justify a new version.

> The attached patch introduces a new option "updates_without_script"
> into extension control file. This specifies a list of such updates
> following the pattern 'old_version--target_version'.

This seems completely unnecessary and confusing.

regards, tom lane




Re: Allow an extention to be updated without a script

2023-01-30 Thread Yugo NAGATA
Hi,

Thank you for your comment.

On Mon, 30 Jan 2023 16:05:52 -0500
Tom Lane  wrote:

> Yugo NAGATA  writes:
> > Currently, even when we don't need to execute any command to update an
> > extension from one version to the next, we need to provide an update
> > script that doesn't contain any command. Preparing such meaningless
> > files are sometimes annoying.
> 
> If you have no update script, why call it a new version?  The point
> of extension versions is to distinguish different states of the
> extension's SQL objects.  We do not consider mods in underlying C code
> to justify a new version.

Although, as you say, the extension manager doesn't track changes in C code
functions, a new version could be released with changes in only in C
functions that implement a new feature or fix some bugs because it looks
a new version from user's view.  Actually, there are several extensions
that provide empty update scripts in order to allow user to install such
new versions, for example;

- pglogical
 
(https://github.com/2ndQuadrant/pglogical/blob/REL2_x_STABLE/pglogical--2.4.1--2.4.2.sql)
- hll
 
(https://github.com/citusdata/postgresql-hll/blob/master/update/hll--2.16--2.17.sql)
- orafce
 (https://github.com/orafce/orafce/blob/master/orafce--3.12--3.13.sql)
- hypopg
 (https://github.com/HypoPG/hypopg/blob/REL1_STABLE/hypopg--1.3.1--1.3.2.sql)
- timescaledb
 
(https://github.com/timescale/timescaledb/blob/main/sql/updates/2.9.2--2.9.1.sql)


-- 
Yugo NAGATA 




Re: Allow an extention to be updated without a script

2023-01-31 Thread Julien Rouhaud
Hi,

On Tue, Jan 31, 2023 at 11:17:22AM +0900, Yugo NAGATA wrote:
>
> On Mon, 30 Jan 2023 16:05:52 -0500
> Tom Lane  wrote:
>
> > If you have no update script, why call it a new version?  The point
> > of extension versions is to distinguish different states of the
> > extension's SQL objects.  We do not consider mods in underlying C code
> > to justify a new version.
>
> Although, as you say, the extension manager doesn't track changes in C code
> functions, a new version could be released with changes in only in C
> functions that implement a new feature or fix some bugs because it looks
> a new version from user's view.  Actually, there are several extensions
> that provide empty update scripts in order to allow user to install such
> new versions, for example;
>
> [...]
> - hypopg
>  (https://github.com/HypoPG/hypopg/blob/REL1_STABLE/hypopg--1.3.1--1.3.2.sql)
> [...]

Indeed, almost all users don't really understand the difference between the
module / C code and the extension, and that gets worse when
shared_preload_libraries gets in the way.

I personally choose to ship "empty" extension versions so that people can
upgrade it if they want to have e.g. the OS level version match the SQL level
version.  I know some extensions that chose a different approach: keep the
first 2 digits for anything that involves extension changes and have a 3rd
digit for C level bugfix only.  But they get very frequent bug reports about
version mismatch any time a C bugfix is released, so I will again personally
keep shipping those useless versions.  That being said, I agree with Tom here
and we shouldn't add hacks for that.




Re: Allow an extention to be updated without a script

2023-02-01 Thread Yugo NAGATA
On Wed, 1 Feb 2023 14:37:27 +0800
Julien Rouhaud  wrote:

> Hi,
> 
> On Tue, Jan 31, 2023 at 11:17:22AM +0900, Yugo NAGATA wrote:
> >
> > On Mon, 30 Jan 2023 16:05:52 -0500
> > Tom Lane  wrote:
> >
> > > If you have no update script, why call it a new version?  The point
> > > of extension versions is to distinguish different states of the
> > > extension's SQL objects.  We do not consider mods in underlying C code
> > > to justify a new version.
> >
> > Although, as you say, the extension manager doesn't track changes in C code
> > functions, a new version could be released with changes in only in C
> > functions that implement a new feature or fix some bugs because it looks
> > a new version from user's view.  Actually, there are several extensions
> > that provide empty update scripts in order to allow user to install such
> > new versions, for example;
> >
> > [...]
> > - hypopg
> >  
> > (https://github.com/HypoPG/hypopg/blob/REL1_STABLE/hypopg--1.3.1--1.3.2.sql)
> > [...]
> 
> Indeed, almost all users don't really understand the difference between the
> module / C code and the extension, and that gets worse when
> shared_preload_libraries gets in the way.
> 
> I personally choose to ship "empty" extension versions so that people can
> upgrade it if they want to have e.g. the OS level version match the SQL level
> version.  I know some extensions that chose a different approach: keep the
> first 2 digits for anything that involves extension changes and have a 3rd
> digit for C level bugfix only.  But they get very frequent bug reports about
> version mismatch any time a C bugfix is released, so I will again personally
> keep shipping those useless versions.  That being said, I agree with Tom here
> and we shouldn't add hacks for that.

Thank you for your comment and explanation. That helped me understand extension
release approaches.

I will withdraw the proposal since just providing empty update scripts can
resolve the problem and it wouldn't be worth the confusing. 

Regards,
Yugo Nagata

-- 
Yugo NAGATA