pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_upgrade(?) not cleaning up old extensions
6+ messages / 3 participants
[nested] [flat]
* pg_upgrade(?) not cleaning up old extensions
@ 2021-02-06 12:23 Thomas Kellerer <shammat@gmx.net>
0 siblings, 2 replies; 6+ messages in thread
From: Thomas Kellerer @ 2021-02-06 12:23 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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 though only 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 multiple versions 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 extensions were 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_upgrade(?) not cleaning up old extensions
@ 2021-02-06 17:09 Tom Lane <tgl@sss.pgh.pa.us>
parent: Thomas Kellerer <shammat@gmx.net>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2021-02-06 17:09 UTC (permalink / raw)
To: Thomas Kellerer <shammat@gmx.net>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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 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".)
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_upgrade(?) not cleaning up old extensions
@ 2021-02-06 18:11 Thomas Kellerer <shammat@gmx.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Thomas Kellerer @ 2021-02-06 18:11 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>
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?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_upgrade(?) not cleaning up old extensions
@ 2021-02-06 18:42 Jeff Janes <jeff.janes@gmail.com>
parent: Thomas Kellerer <shammat@gmx.net>
1 sibling, 1 reply; 6+ messages in thread
From: Jeff Janes @ 2021-02-06 18:42 UTC (permalink / raw)
To: Thomas Kellerer <shammat@gmx.net>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Sat, Feb 6, 2021 at 7:24 AM Thomas Kellerer <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 though only 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 example output?
Cheers,
Jeff
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_upgrade(?) not cleaning up old extensions
@ 2021-02-06 18:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: Thomas Kellerer <shammat@gmx.net>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2021-02-06 18:47 UTC (permalink / raw)
To: Thomas Kellerer <shammat@gmx.net>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_upgrade(?) not cleaning up old extensions
@ 2021-02-07 16:06 Thomas Kellerer <shammat@gmx.net>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Thomas Kellerer @ 2021-02-07 16:06 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; +Cc: Jeff Janes <jeff.janes@gmail.com>
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 though only 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 example output?
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2021-02-07 16:06 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-02-06 12:23 pg_upgrade(?) not cleaning up old extensions Thomas Kellerer <shammat@gmx.net>
2021-02-06 17:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-02-06 18:11 ` Thomas Kellerer <shammat@gmx.net>
2021-02-06 18:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-02-06 18:42 ` Jeff Janes <jeff.janes@gmail.com>
2021-02-07 16:06 ` Thomas Kellerer <shammat@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox