Thread: pg_upgrade does not upgrade pg_stat_statements properly

Notice the upgraded version is 1.5 and the new version is 1.8

I would think somewhere in the upgrade of the schema there should have been a create extension pg_stat_statements ?

Dave Cramer <davecramer@gmail.com> writes:
> On Wed, 14 Jul 2021 at 15:09, David G. Johnston <david.g.johnston@gmail.com>
> wrote:
>> "Install ... files used by the old cluster" (which must be binary
>> compatible with the new cluster as noted elsewhere on that page) supports
>> the claim that it is the old cluster's version that is going to result.
>> But I agree that saying "because these will be upgraded from the old
>> cluster" is poorly worded and should be fixed to be more precise here.
>> 
>> Something like, "... because the installed extensions will be copied from
>> the old cluster during the upgrade."

> This is still rather opaque. Without intimate knowledge of what changes
> have occurred in each extension I have installed; how would I know what I
> have to fix after the upgrade.

That's exactly why we don't force upgrades of extensions.  It is on the
user's head to upgrade their extensions from time to time, but we don't
make them do it as part of pg_upgrade.  (There are also some
implementation-level reasons to avoid this, IIRC, but the overall
choice is intentional.)

I agree this documentation could be worded better.  Another idea
is that possibly pg_upgrade could produce a list of extensions
that are not the latest version, so people know what's left to
be addressed.

            regards, tom lane

As a first step I propose the following

diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml

index a83c63cd98..f747a4473a 100644

--- a/doc/src/sgml/ref/pgupgrade.sgml

+++ b/doc/src/sgml/ref/pgupgrade.sgml

@@ -305,9 +305,10 @@ make prefix=/usr/local/pgsql.new install

      Install any custom shared object files (or DLLs) used by the old cluster

      into the new cluster, e.g., <filename>pgcrypto.so</filename>,

      whether they are from <filename>contrib</filename>

-     or some other source. Do not install the schema definitions, e.g.,

-     <command>CREATE EXTENSION pgcrypto</command>, because these will be upgraded

-     from the old cluster.

+     or some other source. Do not execute CREATE EXTENSION on the new cluster.

+     The extensions will be upgraded from the old cluster. However it may be

+     necessary to recreate the extension on the new server after the upgrade

+     to ensure compatibility with the new library.

      Also, any custom full text search files (dictionary, synonym,

      thesaurus, stop words) must also be copied to the new cluster.

     </para>

 

On Thursday, July 15, 2021, Dave Cramer <davecramer@gmail.com> wrote:

      Install any custom shared object files (or DLLs) used by the old cluster

      into the new cluster, e.g., <filename>pgcrypto.so</filename>,

      whether they are from <filename>contrib</filename>

-     or some other source.

However it may be

+     necessary to recreate the extension on the new server after the upgrade

+     to ensure compatibility with the new library.

      

 My uncertainty revolves around core extensions since it seems odd to tell the user to overwrite them with versions from an older version of PostgreSQL.

Well clearly my suggestion was not clear if you interpreted that as over writing them with versions from an older version of PostgreSQL.

I'm thinking at this point we need something a bit more sophisticated like

ALTER EXTENSION ... UPGRADE. And the extension knows how to upgrade itself.

On 2021-Jul-15, Dave Cramer wrote:

> Well IMHO the status quo is terrible. Perhaps you have a suggestion on how
> to make it better ?

I thought the suggestion of having pg_upgrade emit a file with a list of
all extensions needing upgrade in each database was a fairly decent one.

-- 
Álvaro Herrera           39°49'30"S 73°17'W  —  https://www.EnterpriseDB.com/

On 2021-Jul-15, Alvaro Herrera wrote:

> On 2021-Jul-15, Dave Cramer wrote:
> 
> > Well IMHO the status quo is terrible. Perhaps you have a suggestion on how
> > to make it better ?
> 
> I thought the suggestion of having pg_upgrade emit a file with a list of
> all extensions needing upgrade in each database was a fairly decent one.

Eh, and 
  pg_upgrade [other switches] --upgrade-extensions
sounds good too ...

-- 
Álvaro Herrera           39°49'30"S 73°17'W  —  https://www.EnterpriseDB.com/

On 7/15/21 12:25 PM, David G. Johnston wrote:

> I would say that it probably should be "--upgrade-extension=aaa 
> --upgrade_extension=bbb" though if we are going to the effort to offer 
> something.

I am a bit confused here. From the previous exchange I get the feeling 
that you haven't created and maintained a single extension that survived 
a single version upgrade of itself or PostgreSQL (in the latter case 
requiring code changes to the extension due to internal API changes 
inside the PostgreSQL version).

I have. PL/Profiler to be explicit.

Can you please elaborate what experience your opinion is based on?


Regards, Jan


-- 
Jan Wieck

On 7/15/21 12:31 PM, Robert Eckhardt wrote:
>   I don’t know if this is a terrible flaw in pg_upgrade, it is a 
> terrible flaw in the overall Postgres experience.

+1 (that is the actual problem here)


-- 
Jan Wieck

On 7/15/21 12:46 PM, David G. Johnston wrote:

> I am an application developer who operates on the principle of "change 
> only one thing at a time".

Which pg_upgrade by definition isn't. It is bringing ALL the changes 
from one major version to the target version, which may be multiple at 
once. Including, but not limited to, catalog schema changes, SQL 
language changes, extension behavior changes and utility command 
behavior changes.

On that principle, you should advocate against using pg_upgrade in the 
first place.


Regards, Jan

-- 
Jan Wieck

On 7/15/21 1:10 PM, David G. Johnston wrote:
> ... But while 
> PostgreSQL doesn't really have a choice here - it cannot be expected to 
> subdivide itself - extensions (at least external ones - PostGIS is one I 
> have in mind presently) - can and often do attempt to support multiple 
> versions of PostgreSQL for whatever major versions of their product they 
> are offering.  For these it is possible to adhere to the "change one 
> thing at a time principle" and to treat the extensions as not being part 
> of "ALL the changes from one major version to the target version..."

You may make that exception for an external extension like PostGIS. But 
I don't think it is valid for one distributed in sync with the core 
system in the contrib package, like pg_stat_statements. Which happens to 
be the one named in the subject line of this entire discussion.


Regards, Jan

-- 
Jan Wieck

On Thu, Jul 15, 2021 at 02:14:28PM -0400, Jan Wieck wrote:
> On 7/15/21 1:10 PM, David G. Johnston wrote:
> > ... But while PostgreSQL doesn't really have a choice here - it cannot
> > be expected to subdivide itself - extensions (at least external ones -
> > PostGIS is one I have in mind presently) - can and often do attempt to
> > support multiple versions of PostgreSQL for whatever major versions of
> > their product they are offering.  For these it is possible to adhere to
> > the "change one thing at a time principle" and to treat the extensions
> > as not being part of "ALL the changes from one major version to the
> > target version..."
> 
> You may make that exception for an external extension like PostGIS. But I
> don't think it is valid for one distributed in sync with the core system in
> the contrib package, like pg_stat_statements. Which happens to be the one
> named in the subject line of this entire discussion.

Yes, I think one big issue is that the documentation of the new server
might not match the API of the extension installed on the old server.

There has been a lot of discussion from years ago about why we can't
auto-upgrade extensions, so it might be good to revisit that.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

On 7/15/21 4:24 PM, David G. Johnston wrote:

> OK, especially as this seems useful outside of pg_upgrade, and if done 
> separately is something pg_upgrade could just run as part of its new 
> cluster evaluation scripts.  Knowing whether an extension is outdated 
> doesn't require the old cluster.

Knowing that (the extension is outdated) exactly how? Can you give us an 
example query, maybe a few SQL snippets explaining what exactly you are 
talking about? Because at this point you completely lost me.

Sorry, Jan

-- 
Jan Wieck

On 7/15/21 4:24 PM, David G. Johnston wrote:

OK, especially as this seems useful outside of pg_upgrade, and if done separately is something pg_upgrade could just run as part of its new cluster evaluation scripts.  Knowing whether an extension is outdated doesn't require the old cluster.

Knowing that (the extension is outdated) exactly how? Can you give us an example query, maybe a few SQL snippets explaining what exactly you are talking about? Because at this point you completely lost me.

On Thu, Jul 15, 2021 at 08:15:57AM -0700, David G. Johnston wrote:
> On Thursday, July 15, 2021, David G. Johnston <david.g.johnston@gmail.com>
>      My uncertainty revolves around core extensions since it seems odd to tell
>     the user to overwrite them with versions from an older version of
>     PostgreSQL.
> 
> Ok. Just re-read the docs a third time…no uncertainty regarding contrib
> now…following the first part of the instructions means that before one could
> re-run create extension they would need to restore the original contrib library
> files to avoid the new extension code using the old library.  So that whole
> part about recreation is inconsistent with the existing unchanged text.

I came up with the attached patch.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

On Wed, Jul 28, 2021 at 09:35:28PM -0700, David G. Johnston wrote:
> On Wed, Jul 28, 2021 at 6:52 PM Bruce Momjian <bruce@momjian.us> wrote:
> 
>     I came up with the attached patch.
> 
> 
> Thank you.  It is an improvement but I think more could be done here (not
> exactly sure what - though removing the "copy binaries for contrib modules from
> the old server" seems like a decent second step.)

Uh, I don't see that text.

> I'm not sure it really needs a parenthetical, and I personally dislike using
> "Consider" to start the sentence.
> 
> "Bringing extensions up to the newest version available on the new server can
> be done later using ALTER EXTENSION UPGRADE (after ensuring the correct
> binaries are installed)."

OK, I went with this new text.  There is confusion over install vs copy,
and whether this is happening at the operating system level or the SQL
level.  I tried to clarify that, but I am not sure I was successful.  I
also used the word "extension" since this is more common than "custom".

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

On Thu, Jul 29, 2021 at 11:01:43AM -0400, Dave Cramer wrote:
> 
> 
> 
> 
>     OK, I went with this new text.  There is confusion over install vs copy,
>     and whether this is happening at the operating system level or the SQL
>     level.  I tried to clarify that, but I am not sure I was successful.  I
>     also used the word "extension" since this is more common than "custom".
> 
> 
> Much better, however I'm unclear on whether CREATE EXTENSION is actually
> executed on the upgraded server.
> 
> From what I could gather it is not, otherwise the new function definitions
> should have been in place. 
> I think what happens is that the function definitions are copied as part of the
> schema and pg_extension is also copied.

Yes, the _effect_ of CREATE EXTENSION in the old cluster is copied to
the new cluster as object definitions.  CREATE EXTENSION runs the SQL
files associated with the extension.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

On Thu, Jul 29, 2021 at 11:17:52AM -0400, Dave Cramer wrote:
> On Thu, 29 Jul 2021 at 11:10, Bruce Momjian <bruce@momjian.us> wrote:
> OK, I think we should be more clear as to what is happening.  Saying they will
> be recreated is misleading.
> The extension definitions are being copied from the old server to the new
> server.

I think my wording is says exactly that:

    Do not load the schema definitions, e.g., <command>CREATE EXTENSION
    pgcrypto</command>, because these will be recreated from the old
    cluster.  

> I also think we should have stronger wording in the "The extensions may be
> upgraded ..." statement. 
> I'd suggest "Any new versions of extensions should be upgraded using...."

I can't really comment on that since I see little mention of upgrading
extensions in our docs.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

On Thu, Jul 29, 2021 at 08:28:12AM -0700, David G. Johnston wrote:
> On Thu, Jul 29, 2021 at 7:56 AM Bruce Momjian <bruce@momjian.us> wrote:
> 
>     On Wed, Jul 28, 2021 at 09:35:28PM -0700, David G. Johnston wrote:
>     > On Wed, Jul 28, 2021 at 6:52 PM Bruce Momjian <bruce@momjian.us> wrote:
>     >
>     >     I came up with the attached patch.
>     >
>     >
>     > Thank you.  It is an improvement but I think more could be done here (not
>     > exactly sure what - though removing the "copy binaries for contrib
>     modules from
>     > the old server" seems like a decent second step.)
> 
>     Uh, I don't see that text.
> """
>  5. Install custom shared object files
> 
> Install any custom shared object files (or DLLs) used by the old cluster into
> the new cluster, e.g., pgcrypto.so, whether they are from contrib or some other
> source. Do not install the schema definitions, e.g., CREATE EXTENSION pgcrypto,
> because these will be upgraded from the old cluster. Also, any custom full text
> search files (dictionary, synonym, thesaurus, stop words) must also be copied
> to the new cluster.
> """
> I have an issue with the fragment "whether they are from contrib" - my
> understanding at this point is that because of the way we package and version
> contrib it should not be necessary to copy those shared object files from the
> old to the new server (maybe, just maybe, with a qualification that you are
> upgrading between two versions that were in support during the same time
> period).

OK, so this is the confusion I was talking about.  You are supposed to
install _new_ _versions_ of the extensions that are in the old cluster
to the new cluster.  You are not supposed to _copy_ the files from the
old to new cluster.  I think my new patch makes that clearer, but can it
be improved?

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

On Thu, Jul 29, 2021 at 11:36:19AM -0400, Dave Cramer wrote:
> 
> 
>     I have an issue with the fragment "whether they are from contrib" - my
>     understanding at this point is that because of the way we package and
>     version contrib it should not be necessary to copy those shared object
>     files from the old to the new server (maybe, just maybe, with a
>     qualification that you are upgrading between two versions that were in
>     support during the same time period).
> 
> 
> Just to clarify. In no case are binaries copied from the old server to the new
> server. Whether from contrib or otherwise.

Right.  Those are _binaries_ and therefore made to match a specific
Postgres binary.  They might work or might not, but copying them is
never a good idea --- they should be recompiled to match the new server
binary, even if the extension had no version/API changes.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

On 7/29/21 11:10 AM, Bruce Momjian wrote:
> On Thu, Jul 29, 2021 at 11:01:43AM -0400, Dave Cramer wrote:
>> Much better, however I'm unclear on whether CREATE EXTENSION is actually
>> executed on the upgraded server.
>> 
>> From what I could gather it is not, otherwise the new function definitions
>> should have been in place. 
>> I think what happens is that the function definitions are copied as part of the
>> schema and pg_extension is also copied.
> 
> Yes, the _effect_ of CREATE EXTENSION in the old cluster is copied to
> the new cluster as object definitions.  CREATE EXTENSION runs the SQL
> files associated with the extension.
> 

This assumes that the scripts executed during CREATE EXTENSION have no 
conditional code in them that depends on the server version. Running the 
same SQL script on different server versions can have different effects.

I don't have a ready example of such an extension, but if we ever would 
convert the backend parts of Slony into an extension, it would be one.


Regards, Jan

-- 
Jan Wieck

On Thu, Jul 29, 2021 at 08:39:32AM -0700, David G. Johnston wrote:
> On Thu, Jul 29, 2021 at 8:36 AM Dave Cramer <davecramer@gmail.com> wrote:
> 
> 
> 
> 
>         I have an issue with the fragment "whether they are from contrib" - my
>         understanding at this point is that because of the way we package and
>         version contrib it should not be necessary to copy those shared object
>         files from the old to the new server (maybe, just maybe, with a
>         qualification that you are upgrading between two versions that were in
>         support during the same time period).
> 
> 
>     Just to clarify. In no case are binaries copied from the old server to the
>     new server. Whether from contrib or otherwise.
>
> I had used "binaries" when I should have written "shared object files".  I just
> imagine a DLL as being a binary file so it seems accurate but we use the term
> differently I suppose?

Uh, technically, the _executable_ binary should only use shared object /
loadable libraries that were compiled against that binary's exported
API.  Sometimes mismatches work (if the API used by the shared object
has not changed in the binary) so people get used to it working, and
then one day it doesn't, but it is never a safe process.

If two people here are confused about this, obviously others will be as
well.  I think we were trying to do too much in that first sentence, so
I split it into two in the attached patch.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

On Thu, Jul 29, 2021 at 11:46:12AM -0400, Jan Wieck wrote:
> On 7/29/21 11:10 AM, Bruce Momjian wrote:
> > On Thu, Jul 29, 2021 at 11:01:43AM -0400, Dave Cramer wrote:
> > > Much better, however I'm unclear on whether CREATE EXTENSION is actually
> > > executed on the upgraded server.
> > > 
> > > From what I could gather it is not, otherwise the new function definitions
> > > should have been in place. I think what happens is that the function
> > > definitions are copied as part of the
> > > schema and pg_extension is also copied.
> > 
> > Yes, the _effect_ of CREATE EXTENSION in the old cluster is copied to
> > the new cluster as object definitions.  CREATE EXTENSION runs the SQL
> > files associated with the extension.
> > 
> 
> This assumes that the scripts executed during CREATE EXTENSION have no
> conditional code in them that depends on the server version. Running the
> same SQL script on different server versions can have different effects.
> 
> I don't have a ready example of such an extension, but if we ever would
> convert the backend parts of Slony into an extension, it would be one.

The bottom line is that we have _no_ mechanism to handle this except
uninstalling the extension before the upgrade and re-installing it
afterward, which isn't clearly spelled out for each extension, as far as
I know, and would not work for extensions that create data types.

Yes, I do feel this is a big hold in our upgrade instructions.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

On Thu, Jul 29, 2021 at 09:00:36AM -0700, David G. Johnston wrote:
> On Thu, Jul 29, 2021 at 8:42 AM Bruce Momjian <bruce@momjian.us> wrote:
> 
>     On Thu, Jul 29, 2021 at 11:36:19AM -0400, Dave Cramer wrote:
>     >
>     >
>     >     I have an issue with the fragment "whether they are from contrib" -
>     my
>     >     understanding at this point is that because of the way we package and
>     >     version contrib it should not be necessary to copy those shared
>     object
>     >     files from the old to the new server (maybe, just maybe, with a
>     >     qualification that you are upgrading between two versions that were
>     in
>     >     support during the same time period).
>     >
>     >
>     > Just to clarify. In no case are binaries copied from the old server to
>     the new
>     > server. Whether from contrib or otherwise.
> 
>     Right.  Those are _binaries_ and therefore made to match a specific
>     Postgres binary.  They might work or might not, but copying them is
>     never a good idea --- they should be recompiled to match the new server
>     binary, even if the extension had no version/API changes.
> 
> 
> Ok, looking at the flow again, where exactly would the user even be able to
> execute "CREATE EXTENSION" meaningfully?  The relevant databases do not exist
> (not totally sure what happens to the postgres database created during the
> initdb step...) so at the point where the user is "installing the extension"
> all they can reasonably do is a server-level install (they could maybe create
> extension in the postgres database, but does that even matter?).

They could technically start the new cluster and use "CREATE EXTENSION"
before the upgrade, and then the ugprade would fail since there would be
duplicate object errors.

> So, I'd propose simplifying this all to something like:
> 
> Install extensions on the new server
> 
> Any extensions that are used by the old cluster need to be installed into the
> new cluster.  Each database in the old cluster will have its current version of
> all extensions migrated to the new cluster as-is.  You can use the ALTER
> EXTENSION command, on a per-database basis, to update its extensions
> post-upgrade.

Can you review the text I just posted?  Thanks.   I think we are making
progress.  ;-)

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

On 7/29/21 12:00 PM, David G. Johnston wrote:
> Ok, looking at the flow again, where exactly would the user even be able 
> to execute "CREATE EXTENSION" meaningfully?  The relevant databases do 
> not exist (not totally sure what happens to the postgres database 
> created during the initdb step...) so at the point where the user is 
> "installing the extension" all they can reasonably do is a server-level 
> install (they could maybe create extension in the postgres database, but 
> does that even matter?).
> 
> So, I'd propose simplifying this all to something like:
> 
> Install extensions on the new server

Extensions are not installed on the server level. Their binary 
components (shared objects) are, but the actual catalog modifications 
that make them accessible are performed per database by CREATE 
EXTENSION, which executes the SQL files associated with the extension. 
And they can be performed differently per database, like for example 
placing one and the same extension into different schemas in different 
databases.

pg_upgrade is not (and should not be) concerned with placing the 
extension's installation components into the new version's lib and share 
directories. But it is pg_upgrade's job to perform the correct catalog 
modification per database during the upgrade.

> Any extensions that are used by the old cluster need to be installed 
> into the new cluster.  Each database in the old cluster will have its 
> current version of all extensions migrated to the new cluster as-is.  
> You can use the ALTER EXTENSION command, on a per-database basis, to 
> update its extensions post-upgrade.

That assumes that the extension SQL files are capable of detecting a 
server version change and perform the necessary (if any) steps to alter 
the extension's objects accordingly.

Off the top of my head I don't remember what happens when one executes 
ALTER EXTENSION ... UPGRADE ... when it is already on the latest version 
*of the extension*. Might be an error or a no-op.

And to make matters worse, it is not possible to work around this with a 
DROP EXTENSION ... CREATE EXTENSION. There are extensions that create 
objects, like user defined data types and functions, that will be 
referenced by end user objects like tables and views.


Regards, Jan

-- 
Jan Wieck

On Thu, Jul 29, 2021 at 12:22:59PM -0400, Dave Cramer wrote:
> On Thu, 29 Jul 2021 at 12:16, Bruce Momjian <bruce@momjian.us> wrote:
>     Can you review the text I just posted?  Thanks.   I think we are making
>     progress.  ;-)
> 
> 
> I am OK with Everything except
> 
> Do not load the schema definitions,
> e.g., <command>CREATE EXTENSION pgcrypto</command>, because these
> will be recreated from the old cluster.  (The extensions may be
> upgraded later using <literal>ALTER EXTENSION ... UPGRADE</literal>.)
> 
>  I take issue with the word "recreated". This implies something new is created,
> when in fact the old definitions are simply copied over.
> 
> As I said earlier; using the wording "may be upgraded" is not nearly cautionary
> enough.

OK, I changed it to "copy" though I used "recreated" earlier since I was
worried "copy" would be confused with file copy.  I changed the
recommendation to "should be".

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

On 7/29/21 12:44 PM, David G. Johnston wrote:

> ... - but at present pg_upgrade simply 
> doesn't care and hopes that the extension author's documentation deals 
> with these possibilities in a sane manner.

pg_upgrade is not able to address this in a guaranteed, consistent 
fashion. At this point there is no way to even make sure that a 3rd 
party extension provides the scripts needed to upgrade from one 
extension version to the next. We don't have the mechanism to upgrade 
the same extension version from one server version to the next, in case 
there are any modifications in place necessary.

How exactly do you envision that we, the PostgreSQL project, make sure 
that extension developers provide those mechanisms in the future?


Regards, Jan

-- 
Jan Wieck

On 2021-Jul-29, Dave Cramer wrote:

> > If the old cluster used extensions those same extensions must be
> > installed in the new cluster via installation procedures specific
> > to, and documented by, each extension.  For contrib extensions it is
> > usually enough to install the -contrib package via the same method
> > you used to install the PostgreSQL server.
> 
> Well this is not strictly true. There are many extensions that would
> work just fine with the current pg_upgrade. It may not even be
> necessary to recompile them.

It is always necessary to recompile because of the PG_MODULE_MAGIC
declaration, which is mandatory and contains a check that the major
version matches.  Copying the original shared object never works.

-- 
Álvaro Herrera           39°49'30"S 73°17'W  —  https://www.EnterpriseDB.com/
"But static content is just dynamic content that isn't moving!"
                http://smylers.hates-software.com/2007/08/15/fe244d0c.html

On 2021-Jul-29, Bruce Momjian wrote:

> +     If the old cluster used extensions, whether from
> +     <filename>contrib</filename> or some other source, it used
> +     shared object files (or DLLs) to implement these extensions, e.g.,
> +     <filename>pgcrypto.so</filename>.  Now, shared object files matching
> +     the new server binary must be installed in the new cluster, usually
> +     via operating system commands.  Do not load the schema definitions,
> +     e.g., <command>CREATE EXTENSION pgcrypto</command>, because these
> +     will be copied from the old cluster.  (Extensions should be upgraded
> +     later using <literal>ALTER EXTENSION ... UPGRADE</literal>.)

I propose this:

<para>
  If the old cluster used shared-object files (or DLLs) for extensions
  or other loadable modules, install recompiled versions of those files
  onto the new cluster.
  Do not install the extension themselves (i.e., do not run
  <command>CREATE EXTENSION</command>), because extension definitions
  will be carried forward from the old cluster.
</para>

<para>
  Extensions can be upgraded after pg_upgrade completes using
  <command>ALTER EXTENSION ... UPGRADE</command>, on a per-database
  basis.
</para>

I suggest " ... for extensions or other loadable modules" because
loadable modules aren't necessarily for extensions.  Also, it's
perfectly possible to have extension that don't have a loadable module.

I suggest "extension definitions ... carried forward" instead of
"extensions ... copied" (your proposed text) to avoid the idea that
files are copied; use it instead of "schema definitions ... upgraded"
(the current docs) to avoid the idea that they are actually upgraded;
also, "schema definition" seems a misleading term to use here.

I suggest "can be upgraded" rather than "should be upgraded" because
we're not making a recommendation, merely stating the fact that it is
possible to do so.

I suggest using the imperative mood, to be consistent with the
surrounding text.  (Applies to the first para; the second para is
informative.)


I haven't seen it mentioned in the thread, but I think the final phrase
of this <step> should be a separate step,

<step>
 <title>Copy custom full-text search files</title>
 <para>
  Copy any custom full text search file (dictionary, synonym, thesaurus,
  stop word list) to the new server.
 </para>
</step>

While this is closely related to extensions, it's completely different.

-- 
Álvaro Herrera              Valdivia, Chile  —  https://www.EnterpriseDB.com/
"Sallah, I said NO camels! That's FIVE camels; can't you count?"
(Indiana Jones)

On Fri, Jul 30, 2021 at 12:14 AM Bruce Momjian <bruce@momjian.us> wrote:
>
> On Thu, Jul 29, 2021 at 11:46:12AM -0400, Jan Wieck wrote:
> >
> > This assumes that the scripts executed during CREATE EXTENSION have no
> > conditional code in them that depends on the server version. Running the
> > same SQL script on different server versions can have different effects.
> >
> > I don't have a ready example of such an extension, but if we ever would
> > convert the backend parts of Slony into an extension, it would be one.
>
> The bottom line is that we have _no_ mechanism to handle this except
> uninstalling the extension before the upgrade and re-installing it
> afterward, which isn't clearly spelled out for each extension, as far as
> I know, and would not work for extensions that create data types.
>
> Yes, I do feel this is a big hold in our upgrade instructions.

FWIW I have an example of such an extension: powa-archivist extension
script runs an anonymous block code and creates if needed a custom
wrapper to emulate the current_setting(text, boolean) variant that
doesn't exist on pre-pg96 servers.

On 7/29/21 2:04 PM, Julien Rouhaud wrote:
>> On Thu, Jul 29, 2021 at 11:46:12AM -0400, Jan Wieck wrote:

>> > I don't have a ready example of such an extension, but if we ever would
>> > convert the backend parts of Slony into an extension, it would be one.

> FWIW I have an example of such an extension: powa-archivist extension
> script runs an anonymous block code and creates if needed a custom
> wrapper to emulate the current_setting(text, boolean) variant that
> doesn't exist on pre-pg96 servers.
> 

Thank you!

I presume that pg_upgrade on a database with that extension installed 
would silently succeed and have the pg_catalog as well as public (or 
wherever) version of that function present.


Regards, Jan

-- 
Jan Wieck

On 2021-Jul-29, Dave Cramer wrote:

> > I suggest "can be upgraded" rather than "should be upgraded" because
> > we're not making a recommendation, merely stating the fact that it is
> > possible to do so.
>
> Why not recommend it? I was going to suggest that we actually run alter
> extension upgrade ... on all of them as a solution.
> 
> What are the downsides to upgrading them all by default ? AFAIK if they
> need upgrading this should run all of the SQL scripts, if they don't then
> this should be a NO-OP.

I'm not aware of any downsides, and I think it would be a good idea to
do so, but I also think that it would be good to sort out the docs
precisely (a backpatchable doc change, IMV) and once that is done we can
discuss how to improve pg_upgrade so that users no longer need to do
that (a non-backpatchable code change).  Incremental improvements and
all that ...

-- 
Álvaro Herrera              Valdivia, Chile  —  https://www.EnterpriseDB.com/
"La libertad es como el dinero; el que no la sabe emplear la pierde" (Alvarez)

On Thu, Jul 29, 2021 at 10:00:12AM -0700, David G. Johnston wrote:
> I'm warming up to "should" but maybe add a "why" such as "the old versions are
> considered unsupported on the newer server".
> 
> I dislike "usually via operating system commands", just offload this to the
> extension, i.e., "must be installed in the new cluster via installation
> procedures specific to, and documented by, each extension (for contrib it is
> usually enough to ensure the -contrib package was chosen to be installed along
> with the -server package for your operating system.)"
> 
> I would simplify the first two sentences to just:
> 
> If the old cluster used extensions those same extensions must be installed in
> the new cluster via installation procedures specific to, and documented by,
> each extension.  For contrib extensions it is usually enough to install the
> -contrib package via the same method you used to install the PostgreSQL server.
> 
> I would consider my suggestion for "copy as-is/alter extension" wording in my
> previous email in lieu of the existing third and fourth sentences, with the
> "should" and "why" wording possibly worked in.  But the existing works ok.

I am sorry but none of your suggestions are exciting me --- they seem to
get into too much detail for the context.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

On Thu, Jul 29, 2021 at 02:28:55PM -0400, Bruce Momjian wrote:
> On Thu, Jul 29, 2021 at 10:00:12AM -0700, David G. Johnston wrote:
> > I'm warming up to "should" but maybe add a "why" such as "the old versions are
> > considered unsupported on the newer server".
> > 
> > I dislike "usually via operating system commands", just offload this to the
> > extension, i.e., "must be installed in the new cluster via installation
> > procedures specific to, and documented by, each extension (for contrib it is
> > usually enough to ensure the -contrib package was chosen to be installed along
> > with the -server package for your operating system.)"
> > 
> > I would simplify the first two sentences to just:
> > 
> > If the old cluster used extensions those same extensions must be installed in
> > the new cluster via installation procedures specific to, and documented by,
> > each extension.  For contrib extensions it is usually enough to install the
> > -contrib package via the same method you used to install the PostgreSQL server.

Oh, and you can't use the same installation procedures as when you
installed the extension because that probably included CREATE EXTENSION.
This really highlights why this is tricky to explain --- we need the
binaries, but not the SQL that goes with it.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

On Thu, Jul 29, 2021 at 01:43:09PM -0400, Álvaro Herrera wrote:
> On 2021-Jul-29, Bruce Momjian wrote:
> 
> > +     If the old cluster used extensions, whether from
> > +     <filename>contrib</filename> or some other source, it used
> > +     shared object files (or DLLs) to implement these extensions, e.g.,
> > +     <filename>pgcrypto.so</filename>.  Now, shared object files matching
> > +     the new server binary must be installed in the new cluster, usually
> > +     via operating system commands.  Do not load the schema definitions,
> > +     e.g., <command>CREATE EXTENSION pgcrypto</command>, because these
> > +     will be copied from the old cluster.  (Extensions should be upgraded
> > +     later using <literal>ALTER EXTENSION ... UPGRADE</literal>.)
> 
> I propose this:
> 
> <para>
>   If the old cluster used shared-object files (or DLLs) for extensions
>   or other loadable modules, install recompiled versions of those files
>   onto the new cluster.
>   Do not install the extension themselves (i.e., do not run
>   <command>CREATE EXTENSION</command>), because extension definitions
>   will be carried forward from the old cluster.
> </para>
> 
> <para>
>   Extensions can be upgraded after pg_upgrade completes using
>   <command>ALTER EXTENSION ... UPGRADE</command>, on a per-database
>   basis.
> </para>
> 
> I suggest " ... for extensions or other loadable modules" because
> loadable modules aren't necessarily for extensions.  Also, it's
> perfectly possible to have extension that don't have a loadable module.

Yes, good point.

> I suggest "extension definitions ... carried forward" instead of
> "extensions ... copied" (your proposed text) to avoid the idea that
> files are copied; use it instead of "schema definitions ... upgraded"
> (the current docs) to avoid the idea that they are actually upgraded;
> also, "schema definition" seems a misleading term to use here.

I used the term "duplicated".

> I suggest "can be upgraded" rather than "should be upgraded" because
> we're not making a recommendation, merely stating the fact that it is
> possible to do so.

Agreed.  Most extensions don't have updates between major versions.

> I suggest using the imperative mood, to be consistent with the
> surrounding text.  (Applies to the first para; the second para is
> informative.)

OK.

> I haven't seen it mentioned in the thread, but I think the final phrase
> of this <step> should be a separate step,
> 
> <step>
>  <title>Copy custom full-text search files</title>
>  <para>
>   Copy any custom full text search file (dictionary, synonym, thesaurus,
>   stop word list) to the new server.
>  </para>
> </step>
> 
> While this is closely related to extensions, it's completely different.

Agreed.  See attached patch.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

On Thu, Jul 29, 2021 at 02:19:17PM -0400, Álvaro Herrera wrote:
> On 2021-Jul-29, Dave Cramer wrote:
> 
> > > I suggest "can be upgraded" rather than "should be upgraded" because
> > > we're not making a recommendation, merely stating the fact that it is
> > > possible to do so.
> >
> > Why not recommend it? I was going to suggest that we actually run alter
> > extension upgrade ... on all of them as a solution.
> > 
> > What are the downsides to upgrading them all by default ? AFAIK if they
> > need upgrading this should run all of the SQL scripts, if they don't then
> > this should be a NO-OP.
> 
> I'm not aware of any downsides, and I think it would be a good idea to
> do so, but I also think that it would be good to sort out the docs
> precisely (a backpatchable doc change, IMV) and once that is done we can
> discuss how to improve pg_upgrade so that users no longer need to do
> that (a non-backpatchable code change).  Incremental improvements and
> all that ...

Agreed.  I don't think we have any consistent set of steps for detecting
and upgrading extensions --- that needs a lot more research.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

Dave Cramer <davecramer@gmail.com> writes:
> So back to the original motivation for bringing this up. Recall that a
> cluster was upgraded. Everything appeared to work fine, except that the
> definitions of the functions were slightly different enough to cause a
> fatal issue on restoring a dump from pg_dump.
> Since pg_upgrade is now part of the core project, we need to make sure this
> is not possible or be much more insistent that the user needs to upgrade
> any extensions that require it.

TBH, this seems like mostly the fault of the extension author.
The established design is that the SQL-level objects will be
carried forward verbatim by pg_upgrade.  Therefore, any newer-version
shared library MUST be able to cope sanely with SQL objects from
some previous revision.  The contrib modules provide multiple
examples of ways to do this.

If particular extension authors don't care to go to that much
trouble, it's on their heads to document that their extensions
are unsafe to pg_upgrade.

            regards, tom lane

 On Thu, Jul 29, 2021 at 05:01:24PM -0400, Tom Lane wrote:
> Dave Cramer <davecramer@gmail.com> writes:
> > So back to the original motivation for bringing this up. Recall that a
> > cluster was upgraded. Everything appeared to work fine, except that the
> > definitions of the functions were slightly different enough to cause a
> > fatal issue on restoring a dump from pg_dump.
> > Since pg_upgrade is now part of the core project, we need to make sure this
> > is not possible or be much more insistent that the user needs to upgrade
> > any extensions that require it.
> 
> TBH, this seems like mostly the fault of the extension author.
> The established design is that the SQL-level objects will be
> carried forward verbatim by pg_upgrade.  Therefore, any newer-version
> shared library MUST be able to cope sanely with SQL objects from
> some previous revision.  The contrib modules provide multiple
> examples of ways to do this.
> 
> If particular extension authors don't care to go to that much
> trouble, it's on their heads to document that their extensions
> are unsafe to pg_upgrade.

I think we need to first give clear instructions on how to find out if
an extension update is available, and then how to update it.  I am
thinking we should supply a query which reports all extensions that can
be upgraded, at least for contrib.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

Bruce Momjian <bruce@momjian.us> writes:
> I think we need to first give clear instructions on how to find out if
> an extension update is available, and then how to update it.  I am
> thinking we should supply a query which reports all extensions that can
> be upgraded, at least for contrib.

I suggested awhile ago that pg_upgrade should look into
pg_available_extensions in the new cluster, and prepare
a script with ALTER EXTENSION UPDATE commands for
anything that's installed but is not the (new cluster's)
default version.

            regards, tom lane

On Thu, Jul 29, 2021 at 06:19:56PM -0400, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > I think we need to first give clear instructions on how to find out if
> > an extension update is available, and then how to update it.  I am
> > thinking we should supply a query which reports all extensions that can
> > be upgraded, at least for contrib.
> 
> I suggested awhile ago that pg_upgrade should look into
> pg_available_extensions in the new cluster, and prepare
> a script with ALTER EXTENSION UPDATE commands for
> anything that's installed but is not the (new cluster's)
> default version.

I can do that, but I would think a pg_dump/restore would also have this
issue, so should this be more generic?  If we had a doc section about
that, we could add it a step to run in the pg_upgrade instructions.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

Bruce Momjian <bruce@momjian.us> writes:
> On Thu, Jul 29, 2021 at 06:19:56PM -0400, Tom Lane wrote:
>> I suggested awhile ago that pg_upgrade should look into
>> pg_available_extensions in the new cluster, and prepare
>> a script with ALTER EXTENSION UPDATE commands for
>> anything that's installed but is not the (new cluster's)
>> default version.

> I can do that, but I would think a pg_dump/restore would also have this
> issue, so should this be more generic?

No, because dump/restore does not have this issue.  Regular pg_dump just
issues "CREATE EXTENSION" commands, so you automatically get the target
server's default version.

            regards, tom lane

On Thu, Jul 29, 2021 at 06:29:11PM -0400, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > On Thu, Jul 29, 2021 at 06:19:56PM -0400, Tom Lane wrote:
> >> I suggested awhile ago that pg_upgrade should look into
> >> pg_available_extensions in the new cluster, and prepare
> >> a script with ALTER EXTENSION UPDATE commands for
> >> anything that's installed but is not the (new cluster's)
> >> default version.
> 
> > I can do that, but I would think a pg_dump/restore would also have this
> > issue, so should this be more generic?
> 
> No, because dump/restore does not have this issue.  Regular pg_dump just
> issues "CREATE EXTENSION" commands, so you automatically get the target
> server's default version.

Oh, so pg_upgrade does it differently so the oids are preserved?

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

On 2021-Jul-29, Bruce Momjian wrote:

> On Thu, Jul 29, 2021 at 06:29:11PM -0400, Tom Lane wrote:

> > No, because dump/restore does not have this issue.  Regular pg_dump just
> > issues "CREATE EXTENSION" commands, so you automatically get the target
> > server's default version.
> 
> Oh, so pg_upgrade does it differently so the oids are preserved?

Have a look at pg_dump --binary-upgrade output :-)

-- 
Álvaro Herrera              Valdivia, Chile  —  https://www.EnterpriseDB.com/
"Ellos andaban todos desnudos como su madre los parió, y también las mujeres,
aunque no vi más que una, harto moza, y todos los que yo vi eran todos
mancebos, que ninguno vi de edad de más de XXX años" (Cristóbal Colón)

On Thu, Jul 29, 2021 at 02:14:56PM -0400, Jan Wieck wrote:
> 
> I presume that pg_upgrade on a database with that extension installed would
> silently succeed and have the pg_catalog as well as public (or wherever)
> version of that function present.

I'll have to run a pg_upgrade with it to be 100% sure, but given that this is a
plpgsql function and since the created function is part of the extension
dependencies (and looking at pg_dump source code for binary-upgrade mode), I'm
almost certain that the upgraded cluster would have the pg96- version of the
function even if upgrading to pg9.6+.

Note that in that case the extension would appear to work normally, but the
only way to simulate missing_ok = true is to add a BEGIN/EXCEPTION block.

Since this wrapper function is extensively used, it seems quite possible to
lead to overflowing the snapshot subxip array, as the extension basically runs
every x minutes many functions in a single trannsaction to retrieve many
performance metrics.  This can ruin the performance.

This was an acceptable trade off for people still using pg96- in 2021, but
would be silly to have on more recent versions.

Unfortunately I don't see any easy way to avoid that, as there isn't any
guarantee that a new version will be available after the upgrade.  AFAICT the
only way to ensure that the correct version of the function is present from an
extension point of view would be to add a dedicated function to overwrite any
object that depends on the servers version and document the need to call that
after a pg_upgrade.

On Fri, Jul 30, 2021 at 06:03:50AM -0400, Dave Cramer wrote:
> 
> What would happen if subsequent to the upgrade "ALTER EXTENSION UPGRADE"
> was executed ?

If the extension was already up to date on the source cluster then obviously
nothing.

Otherwise, the extension would be updated.  But unless I'm willing (and
remember) to copy/paste until the end of time an anonymous block code that
checks the current server version to see if the wrapper function needs to be
overwritten then nothing will happen either as far as this function is
concerned.

On Fri, Jul 30, 2021 at 06:48:34AM -0400, Dave Cramer wrote:
> 
> Well I think that's on the extension author to fix. There's only so much
> pg_upgrade can do here.
> It seems reasonable that upgrading the extension should upgrade the
> extension to the latest version.

That would only work iff the extension was *not* up to date on the original
instance.  Otherwise I fail to see how any extension script will be called at
all, and therefore the extension was no way to fix anything.

On Fri, Jul 30, 2021 at 07:18:56AM -0400, Dave Cramer wrote:
> 
> So my understanding is that upgrade is going to run all of the SQL files
> from whatever version the original instance was up to the current version.
> 
> I'm at a loss as to how this would not work ? How do you upgrade your
> extension otherwise ?

Yes, but as I said twice only if the currently installed version is different
from the default version.  Otherwise ALTER EXTENSION UPGRADE is a no-op.

Just to be clear: I'm not arguing against automatically doing an ALTER
EXTENSION UPGRADE for all extensions in all databases during pg_upgrade (I'm
all for it), just that this specific corner case can't be solved by that
approach.

On Fri, Jul 30, 2021 at 07:33:55AM -0400, Dave Cramer wrote:
> 
> Ah, ok, got it, thanks. Well I'm not sure how to deal with this.
> The only thing I can think of is that we add a post_upgrade function to the
> API.
> 
> All extensions would have to implement this.

It seems like a really big hammer for a niche usage.  As far as I know I'm the
only one who wrote an extension that can create different objects depending on
the server version, so I'm entirely fine with dealing with that problem in my
extension rather than forcing everyone to implement an otherwise useless API.

Now if that API can be useful for other cases or if there are other extensions
with similar problems that would be different story.

On Thu, Jul 29, 2021 at 06:19:56PM -0400, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > I think we need to first give clear instructions on how to find out if
> > an extension update is available, and then how to update it.  I am
> > thinking we should supply a query which reports all extensions that can
> > be upgraded, at least for contrib.
> 
> I suggested awhile ago that pg_upgrade should look into
> pg_available_extensions in the new cluster, and prepare
> a script with ALTER EXTENSION UPDATE commands for
> anything that's installed but is not the (new cluster's)
> default version.

OK, done in this patch.  I am assuming that everything that shows an
update in pg_available_extensions can use ALTER EXTENSION UPDATE.  I
assume this would be backpatched to 9.6.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

On 7/30/21 7:33 AM, Dave Cramer wrote:
> 
> 
> 
> 
>     Yes, but as I said twice only if the currently installed version is
>     different
>     from the default version.  Otherwise ALTER EXTENSION UPGRADE is a no-op.
> 
> 
> Ah, ok, got it, thanks. Well I'm not sure how to deal with this.
> The only thing I can think of is that we add a post_upgrade function to 
> the API.
> 
> All extensions would have to implement this.

An alternative to this would be a recommended version number scheme for 
extensions. If extensions were numbered

    MAJOR_SERVER.MAJOR_EXTENSION.MINOR_EXTENSION

then pg_upgrade could check the new cluster for the existence of an SQL 
script that upgrades the extension from the old cluster's version to the 
new current. And since an extension cannot have the same version number 
on two major server versions, there is no ambiguity here.

The downside is that all the extensions that don't need anything done 
for those upgrades (which I believe is the majority of them) would have 
to provide empty SQL files. Not necessarily a bad thing, as it actually 
documents "yes, the extension developer checked this and there is 
nothing to do here."


Regards, Jan

-- 
Jan Wieck

On 7/30/21 7:40 AM, Julien Rouhaud wrote:
> On Fri, Jul 30, 2021 at 07:33:55AM -0400, Dave Cramer wrote:
>> 
>> Ah, ok, got it, thanks. Well I'm not sure how to deal with this.
>> The only thing I can think of is that we add a post_upgrade function to the
>> API.
>> 
>> All extensions would have to implement this.
> 
> It seems like a really big hammer for a niche usage.  As far as I know I'm the
> only one who wrote an extension that can create different objects depending on
> the server version, so I'm entirely fine with dealing with that problem in my
> extension rather than forcing everyone to implement an otherwise useless API.
> 
> Now if that API can be useful for other cases or if there are other extensions
> with similar problems that would be different story.
> 

I haven't worked on it for a while, but I think pl_profiler does the 
same thing, so you are not alone.


Jan

-- 
Jan Wieck

Jan Wieck <jan@wi3ck.info> writes:
> An alternative to this would be a recommended version number scheme for 
> extensions. If extensions were numbered
>     MAJOR_SERVER.MAJOR_EXTENSION.MINOR_EXTENSION
> then pg_upgrade could check the new cluster for the existence of an SQL 
> script that upgrades the extension from the old cluster's version to the 
> new current. And since an extension cannot have the same version number 
> on two major server versions, there is no ambiguity here.

That idea cannot get off the ground.  We've spent ten years telling
people they can use whatever version-numbering scheme they like for
their extensions; we can't suddenly go from that to "you must use
exactly this scheme".

I don't see the need for it anyway.  What is different from just
putting the update actions into an extension version upgrade
script created according to the current rules, and then setting
that new extension version as the default version in the extension
build you ship for the new server version?

            regards, tom lane

On 7/30/21 1:05 PM, Tom Lane wrote:
> I don't see the need for it anyway.  What is different from just
> putting the update actions into an extension version upgrade
> script created according to the current rules, and then setting
> that new extension version as the default version in the extension
> build you ship for the new server version?

You are right. The real fix should actually be that an extension, that 
creates different objects depending on the major server version it is 
installed on, should not use the same version number for itself on those 
two server versions. It is actually wrong to have DO blocks that execute 
server version dependent sections in the CREATE EXTENSION scripts. 
However similar the code may be, it is intended for different server 
versions, so it is not the same version of the extension.


Regards, Jan

-- 
Jan Wieck

On Thu, Jul 29, 2021 at 03:06:45PM -0400, Bruce Momjian wrote:
> > I haven't seen it mentioned in the thread, but I think the final phrase
> > of this <step> should be a separate step,
> > 
> > <step>
> >  <title>Copy custom full-text search files</title>
> >  <para>
> >   Copy any custom full text search file (dictionary, synonym, thesaurus,
> >   stop word list) to the new server.
> >  </para>
> > </step>
> > 
> > While this is closely related to extensions, it's completely different.
> 
> Agreed.  See attached patch.

Doc patch applied to all supported versions.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

On Fri, Jul 30, 2021 at 12:40:06PM -0400, Bruce Momjian wrote:
> On Thu, Jul 29, 2021 at 06:19:56PM -0400, Tom Lane wrote:
> > Bruce Momjian <bruce@momjian.us> writes:
> > > I think we need to first give clear instructions on how to find out if
> > > an extension update is available, and then how to update it.  I am
> > > thinking we should supply a query which reports all extensions that can
> > > be upgraded, at least for contrib.
> > 
> > I suggested awhile ago that pg_upgrade should look into
> > pg_available_extensions in the new cluster, and prepare
> > a script with ALTER EXTENSION UPDATE commands for
> > anything that's installed but is not the (new cluster's)
> > default version.
> 
> OK, done in this patch.  I am assuming that everything that shows an
> update in pg_available_extensions can use ALTER EXTENSION UPDATE.  I
> assume this would be backpatched to 9.6.

Patch applied through 9.6.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.