Thread: pg_upgrade(?) not cleaning up old extensions

Hello,

I have a (test) database that has been upgraded several times from 9.4 to 13 using pg_upgrade.

I have noticed that when I look at pg_available_extensions many extensions are listed with multiple versions even
thoughonly the latest one is actually used.
 

I did "alter extension ... update" for those that are installed in the database after running pg_upgrade. But the
multipleversions even show up for extensions that are not installed.
 

After I did a dump & restore of the database in question, this list was cleaned and only the most recent version of the
extensionswere shown.
 

This is not a big deal, but I am curious neverthless:

Did I have another choice to cleanup that apart from doing the dump & restore?

Running "drop extension" obviously doesn't work for those that aren't installed.


Thomas

Thomas Kellerer <shammat@gmx.net> writes:
> I have noticed that when I look at pg_available_extensions many extensions are listed with multiple versions even
thoughonly the latest one is actually used. 

Yes, that view is supposed to list the versions that are available
to be installed.  Typically, installation scripts for several back
versions exist on disk.  This is an artifact of the way that we
approach providing extension upgrade capability, but it can be handy
if you want to test SQL code against back versions.

As an example, contrib/adminpack installs

adminpack--1.0.sql
adminpack--1.0--1.1.sql
adminpack--1.1--2.0.sql
adminpack--2.0--2.1.sql

Ordinarily, when you "create extension adminpack", it runs all four
of those scripts in sequence and you end up with the 2.1 SQL objects.
But if you ask to install some earlier version, it just stops after
the appropriate script.  So pg_available_extensions shows 1.0, 1.1,
2.0, and 2.1 as available versions.  (Of course, the main reason
for having the delta scripts is to support "alter extension upgrade".)

            regards, tom lane

Tom Lane schrieb am 06.02.2021 um 18:09:
>> I have noticed that when I look at pg_available_extensions many
>> extensions are listed with multiple versions even though only the
>> latest one is actually used.
>
> Yes, that view is supposed to list the versions that are available
> to be installed.  Typically, installation scripts for several back
> versions exist on disk.  This is an artifact of the way that we
> approach providing extension upgrade capability, but it can be handy
> if you want to test SQL code against back versions.
>
> As an example, contrib/adminpack installs
>
> adminpack--1.0.sql
> adminpack--1.0--1.1.sql
> adminpack--1.1--2.0.sql
> adminpack--2.0--2.1.sql
>
> Ordinarily, when you "create extension adminpack", it runs all four
> of those scripts in sequence and you end up with the 2.1 SQL objects.
> But if you ask to install some earlier version, it just stops after
> the appropriate script.  So pg_available_extensions shows 1.0, 1.1,
> 2.0, and 2.1 as available versions.  (Of course, the main reason
> for having the delta scripts is to support "alter extension upgrade".)

So if it is expected to see multiple versions through that view,
then why do the multiple versions go away when I dump & reload
the database?

Thomas Kellerer <shammat@gmx.net> writes:
> Tom Lane schrieb am 06.02.2021 um 18:09:
>> Yes, that view is supposed to list the versions that are available
>> to be installed.

> So if it is expected to see multiple versions through that view,
> then why do the multiple versions go away when I dump & reload
> the database?

Oh, sorry, -ENOCAFFEINE.  I was thinking of
pg_available_extension_versions, which does what I described.

As for pg_available_extensions, that should produce just one row
per extension control file.  The only way I can see for it to
produce more is if you have duplicate entries in pg_extension,
which also seems unlikely if not impossible.  Maybe you should
provide more detail about exactly what you are seeing, what is
in pg_extension, and what is in your installation's
.../share/extension/ directory.

            regards, tom lane

Jeff Janes schrieb am 06.02.2021 um 19:42:
> On Sat, Feb 6, 2021 at 7:24 AM Thomas Kellerer <shammat@gmx.net <mailto:shammat@gmx.net>> wrote:
>
>     Hello,
>
>     I have a (test) database that has been upgraded several times from 9.4 to 13 using pg_upgrade.
>
>     I have noticed that when I look at pg_available_extensions many extensions are listed with multiple versions even
thoughonly the latest one is actually used. 
>
>
> I can't reproduce this.  Are you sure you aren't looking at pg_available_extension_versions ?  Could you show some
exampleoutput? 


Hmm, I could have sworn I was looking at pg_available_extensions - but maybe I wasn't.

But as I can't reproduce this after a dump & reload and it's really not a big deal,
let's just assume I was indeed using the wrong view.

Thomas