Adding extension default version to \dx

Started by Magnus Haganderabout 1 year ago13 messages

magnus@hagander.net

about 1 year ago

1 attachment(s)

In an effort to make at least a couple of more people realize they have to
run ALTER EXTENSION UPDATE after they've upgraded an extension, as well as
make it a bit easier to realize when you have to do it, I propose we add
the default version of an extension to \dx in psql. We currently show the
installed version in the schema, but no indications that a newer one might
be installed on the system.

PFA a patch to do this.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>

Attachments:

psql_extension_default_version.patchtext/x-patch; charset=US-ASCII; name=psql_extension_default_version.patchDownload

diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index d5543fd62b0..319ad15d4de 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -6107,13 +6107,16 @@ listExtensions(const char *pattern)
 	initPQExpBuffer(&buf);
 	printfPQExpBuffer(&buf,
 					  "SELECT e.extname AS \"%s\", "
-					  "e.extversion AS \"%s\", n.nspname AS \"%s\", c.description AS \"%s\"\n"
+					  "e.extversion AS \"%s\", ae.default_version AS \"%s\","
+					  "n.nspname AS \"%s\", c.description AS \"%s\"\n"
 					  "FROM pg_catalog.pg_extension e "
 					  "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace "
 					  "LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid "
+					  "LEFT JOIN pg_catalog.pg_available_extensions() ae(name, default_version, comment)  ON ae.name=e.extname "
 					  "AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass\n",
 					  gettext_noop("Name"),
 					  gettext_noop("Version"),
+					  gettext_noop("Default version"),
 					  gettext_noop("Schema"),
 					  gettext_noop("Description"));

Julien Rouhaud

rjuju123@gmail.com

about 1 year ago

In reply to: Magnus Hagander (#1)

Re: Adding extension default version to \dx

Hi,

On Fri, Jan 10, 2025 at 01:04:14PM +0100, Magnus Hagander wrote:

In an effort to make at least a couple of more people realize they have to
run ALTER EXTENSION UPDATE after they've upgraded an extension, as well as
make it a bit easier to realize when you have to do it, I propose we add
the default version of an extension to \dx in psql.

+1, I need the info very often and \dx is way faster than writing a query for
that when I need it, especially since \dx output is not too big.

We currently show the
installed version in the schema, but no indications that a newer one might
be installed on the system.

PFA a patch to do this.

Patch LGTM, tested locally and I like the output.

Michael Banck

mbanck@gmx.net

about 1 year ago

In reply to: Magnus Hagander (#1)

Re: Adding extension default version to \dx

Hi,

On Fri, Jan 10, 2025 at 01:04:14PM +0100, Magnus Hagander wrote:

In an effort to make at least a couple of more people realize they have to
run ALTER EXTENSION UPDATE after they've upgraded an extension, as well as
make it a bit easier to realize when you have to do it, I propose we add
the default version of an extension to \dx in psql

+1, I think this is useful.

diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index d5543fd62b0..319ad15d4de 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -6107,13 +6107,16 @@ listExtensions(const char *pattern)
initPQExpBuffer(&buf);
printfPQExpBuffer(&buf,
"SELECT e.extname AS \"%s\", "
-					  "e.extversion AS \"%s\", n.nspname AS \"%s\", c.description AS \"%s\"\n"
+					  "e.extversion AS \"%s\", ae.default_version AS \"%s\","
+					  "n.nspname AS \"%s\", c.description AS \"%s\"\n"
"FROM pg_catalog.pg_extension e "
"LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace "
"LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid "

Not really part of your patch, but "pg_catalog.pg_description c" looks
weird/might be a typo? It is "pg_catalog.pg_description d" everywhere
else AFAICT. So maybe this could be fixed/changed in passing?

+					  "LEFT JOIN pg_catalog.pg_available_extensions() ae(name, default_version, comment)  ON ae.name=e.extname "
"AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass\n",
gettext_noop("Name"),
gettext_noop("Version"),
+					  gettext_noop("Default version"),

pg_available_extensions has "installed_version" and "default_version", I
wonder whether it would make sense to harmonize that and change
"Version" to "Installed version" as well when we change the output
anyway?

Michael

Yugo Nagata

nagata@sraoss.co.jp

12 months ago

In reply to: Julien Rouhaud (#2)

Re: Adding extension default version to \dx

On Fri, 10 Jan 2025 20:37:17 +0800
Julien Rouhaud <rjuju123@gmail.com> wrote:

Hi,

On Fri, Jan 10, 2025 at 01:04:14PM +0100, Magnus Hagander wrote:

In an effort to make at least a couple of more people realize they have to
run ALTER EXTENSION UPDATE after they've upgraded an extension, as well as
make it a bit easier to realize when you have to do it, I propose we add
the default version of an extension to \dx in psql.

+1, I need the info very often and \dx is way faster than writing a query for
that when I need it, especially since \dx output is not too big.

We currently show the
installed version in the schema, but no indications that a newer one might
be installed on the system.

PFA a patch to do this.

Patch LGTM, tested locally and I like the output.

I have a minor comment.

+ "LEFT JOIN pg_catalog.pg_available_extensions() ae(name, default_version, comment) ON ae.name=e.extname "

For consistency with around codes, it seems better to add a space before and after "=",
like "ae.name = e.extname".

Regards,
Yugo Nagata

--
Yugo Nagata <nagata@sraoss.co.jp>

Nathan Bossart

nathandbossart@gmail.com

12 months ago

In reply to: Michael Banck (#3)

Re: Adding extension default version to \dx

On Fri, Jan 10, 2025 at 03:56:31PM +0100, Michael Banck wrote:

On Fri, Jan 10, 2025 at 01:04:14PM +0100, Magnus Hagander wrote:

In an effort to make at least a couple of more people realize they have to
run ALTER EXTENSION UPDATE after they've upgraded an extension, as well as
make it a bit easier to realize when you have to do it, I propose we add
the default version of an extension to \dx in psql

+1, I think this is useful.

diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index d5543fd62b0..319ad15d4de 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -6107,13 +6107,16 @@ listExtensions(const char *pattern)
initPQExpBuffer(&buf);
printfPQExpBuffer(&buf,
"SELECT e.extname AS \"%s\", "
-					  "e.extversion AS \"%s\", n.nspname AS \"%s\", c.description AS \"%s\"\n"
+					  "e.extversion AS \"%s\", ae.default_version AS \"%s\","
+					  "n.nspname AS \"%s\", c.description AS \"%s\"\n"
"FROM pg_catalog.pg_extension e "
"LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace "
"LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid "

Yeah, "c" seems to ordinarily be used for pg_class, so +1 for changing it
to "d".

+					  "LEFT JOIN pg_catalog.pg_available_extensions() ae(name, default_version, comment)  ON ae.name=e.extname "
"AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass\n",
gettext_noop("Name"),
gettext_noop("Version"),
+					  gettext_noop("Default version"),
pg_available_extensions has "installed_version" and "default_version", I
wonder whether it would make sense to harmonize that and change
"Version" to "Installed version" as well when we change the output
anyway?

Separately, I see that there's one update needed for
src/test/regress/expected/psql.out. The documentation for \dx looks
generic enough that it probably doesn't need any updates, though.

--
nathan

Nathan Bossart

nathandbossart@gmail.com

12 months ago

In reply to: Yugo Nagata (#4)

Re: Adding extension default version to \dx

On Wed, Jan 15, 2025 at 02:11:05AM +0900, Yugo Nagata wrote:

I have a minor comment.

+ "LEFT JOIN pg_catalog.pg_available_extensions() ae(name, default_version, comment) ON ae.name=e.extname "

For consistency with around codes, it seems better to add a space before and after "=",
like "ae.name = e.extname".

--
nathan

Jelte Fennema-Nio

postgres@jeltef.nl

11 months ago

In reply to: Nathan Bossart (#5)

2 attachment(s)

Re: Adding extension default version to \dx

On Mon Jan 27, 2025 at 8:39 PM CET, Nathan Bossart wrote:

+1

Separately, I see that there's one update needed for
src/test/regress/expected/psql.out. The documentation for \dx looks
generic enough that it probably doesn't need any updates, though.

Attached is an updated patch that fixes the tests and changes the
whitespace as discussed (including removing a spurious second space
before the ON, which I noticed while changing it). As well as a seprate
patch that changes the "c" alias to "d".

Attachments:

v2-0001-Add-default-extension-version-to-dx.patchtext/x-patch; charset=utf-8; name=v2-0001-Add-default-extension-version-to-dx.patchDownload

From 9762388cfe3b16b22a468ebe29b613e2b81088f3 Mon Sep 17 00:00:00 2001
From: Jelte Fennema-Nio <github-tech@jeltef.nl>
Date: Sun, 23 Feb 2025 15:18:27 +0100
Subject: [PATCH v2 1/2] Add default extension version to \dx

In an effort to make at least a couple of more people realize they have
to run ALTER EXTENSION UPDATE after they've upgraded an extension,  as
well as make it a bit easier to realize when you have to do it, this
adds the default version of an extension to the output of \dx. We
previously showed the installed version in the schema, but no
indications that a newer one might be installed on the system.
---
 src/bin/psql/describe.c            | 5 ++++-
 src/test/regress/expected/psql.out | 6 +++---
 2 files changed, 7 insertions(+), 4 deletions(-)

diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index e6cf468ac9e..01e62430fff 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -6188,13 +6188,16 @@ listExtensions(const char *pattern)
 	initPQExpBuffer(&buf);
 	printfPQExpBuffer(&buf,
 					  "SELECT e.extname AS \"%s\", "
-					  "e.extversion AS \"%s\", n.nspname AS \"%s\", c.description AS \"%s\"\n"
+					  "e.extversion AS \"%s\", ae.default_version AS \"%s\","
+					  "n.nspname AS \"%s\", c.description AS \"%s\"\n"
 					  "FROM pg_catalog.pg_extension e "
 					  "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace "
 					  "LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid "
+					  "LEFT JOIN pg_catalog.pg_available_extensions() ae(name, default_version, comment) ON ae.name = e.extname "
 					  "AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass\n",
 					  gettext_noop("Name"),
 					  gettext_noop("Version"),
+					  gettext_noop("Default version"),
 					  gettext_noop("Schema"),
 					  gettext_noop("Description"));
 
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 6543e90de75..38e24df33ab 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -6459,9 +6459,9 @@ List of schemas
 (0 rows)
 
 \dx "no.such.installed.extension"
-     List of installed extensions
- Name | Version | Schema | Description 
-------+---------+--------+-------------
+              List of installed extensions
+ Name | Version | Default version | Schema | Description 
+------+---------+-----------------+--------+-------------
 (0 rows)
 
 \dX "no.such.extended.statistics"

base-commit: 454c182f8542890d0e2eac85f70d9a254a34fce3
-- 
2.43.0

v2-0002-Use-d-alias-for-pg_description-consistently.patchtext/x-patch; charset=utf-8; name=v2-0002-Use-d-alias-for-pg_description-consistently.patchDownload

From 042fe1d90aa12d798387b61dcd35115626714fec Mon Sep 17 00:00:00 2001
From: Jelte Fennema-Nio <github-tech@jeltef.nl>
Date: Sun, 23 Feb 2025 15:41:48 +0100
Subject: [PATCH v2 2/2] Use d alias for pg_description consistently

In the psql query for \dx we used "c" as an alias for pg_description.
That's a pretty arbitrary letter and given that in all other queries in
use "d" as alias for that table this changes that to follow that
convention.

Reported-By: Michael Banck
---
 src/bin/psql/describe.c | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 01e62430fff..0e13c81a203 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -6192,7 +6192,7 @@ listExtensions(const char *pattern)
 					  "n.nspname AS \"%s\", c.description AS \"%s\"\n"
 					  "FROM pg_catalog.pg_extension e "
 					  "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace "
-					  "LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid "
+					  "LEFT JOIN pg_catalog.pg_description d ON d.objoid = e.oid "
 					  "LEFT JOIN pg_catalog.pg_available_extensions() ae(name, default_version, comment) ON ae.name = e.extname "
 					  "AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass\n",
 					  gettext_noop("Name"),
-- 
2.43.0

Jelte Fennema-Nio

postgres@jeltef.nl

11 months ago

In reply to: Jelte Fennema-Nio (#7)

2 attachment(s)

Re: Adding extension default version to \dx

On Sun Feb 23, 2025 at 3:51 PM CET, Jelte Fennema-Nio wrote:

Attached is an updated patch that fixes the tests and changes the
whitespace as discussed (including removing a spurious second space
before the ON, which I noticed while changing it). As well as a seprate
patch that changes the "c" alias to "d".

Ugh, I forgot to commit a few additional lines in the second patch.
Fixed now.

Attachments:

v3-0001-Add-default-extension-version-to-dx.patchtext/x-patch; charset=utf-8; name=v3-0001-Add-default-extension-version-to-dx.patchDownload

From 9762388cfe3b16b22a468ebe29b613e2b81088f3 Mon Sep 17 00:00:00 2001
From: Jelte Fennema-Nio <github-tech@jeltef.nl>
Date: Sun, 23 Feb 2025 15:18:27 +0100
Subject: [PATCH v3 1/2] Add default extension version to \dx

In an effort to make at least a couple of more people realize they have
to run ALTER EXTENSION UPDATE after they've upgraded an extension,  as
well as make it a bit easier to realize when you have to do it, this
adds the default version of an extension to the output of \dx. We
previously showed the installed version in the schema, but no
indications that a newer one might be installed on the system.
---
 src/bin/psql/describe.c            | 5 ++++-
 src/test/regress/expected/psql.out | 6 +++---
 2 files changed, 7 insertions(+), 4 deletions(-)

diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index e6cf468ac9e..01e62430fff 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -6188,13 +6188,16 @@ listExtensions(const char *pattern)
 	initPQExpBuffer(&buf);
 	printfPQExpBuffer(&buf,
 					  "SELECT e.extname AS \"%s\", "
-					  "e.extversion AS \"%s\", n.nspname AS \"%s\", c.description AS \"%s\"\n"
+					  "e.extversion AS \"%s\", ae.default_version AS \"%s\","
+					  "n.nspname AS \"%s\", c.description AS \"%s\"\n"
 					  "FROM pg_catalog.pg_extension e "
 					  "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace "
 					  "LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid "
+					  "LEFT JOIN pg_catalog.pg_available_extensions() ae(name, default_version, comment) ON ae.name = e.extname "
 					  "AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass\n",
 					  gettext_noop("Name"),
 					  gettext_noop("Version"),
+					  gettext_noop("Default version"),
 					  gettext_noop("Schema"),
 					  gettext_noop("Description"));
 
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 6543e90de75..38e24df33ab 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -6459,9 +6459,9 @@ List of schemas
 (0 rows)
 
 \dx "no.such.installed.extension"
-     List of installed extensions
- Name | Version | Schema | Description 
-------+---------+--------+-------------
+              List of installed extensions
+ Name | Version | Default version | Schema | Description 
+------+---------+-----------------+--------+-------------
 (0 rows)
 
 \dX "no.such.extended.statistics"

base-commit: 454c182f8542890d0e2eac85f70d9a254a34fce3
-- 
2.43.0

v3-0002-Use-d-alias-for-pg_description-consistently.patchtext/x-patch; charset=utf-8; name=v3-0002-Use-d-alias-for-pg_description-consistently.patchDownload

From 8f047924e0d5e7fbf4ad7064b5be1b9fe3b53078 Mon Sep 17 00:00:00 2001
From: Jelte Fennema-Nio <github-tech@jeltef.nl>
Date: Sun, 23 Feb 2025 15:41:48 +0100
Subject: [PATCH v3 2/2] Use d alias for pg_description consistently

In the psql query for \dx we used "c" as an alias for pg_description.
That's a pretty arbitrary letter and given that in all other queries in
use "d" as alias for that table this changes that to follow that
convention.

Reported-By: Michael Banck
---
 src/bin/psql/describe.c | 6 +++---
 1 file changed, 3 insertions(+), 3 deletions(-)

diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 01e62430fff..bf565afcc4e 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -6189,12 +6189,12 @@ listExtensions(const char *pattern)
 	printfPQExpBuffer(&buf,
 					  "SELECT e.extname AS \"%s\", "
 					  "e.extversion AS \"%s\", ae.default_version AS \"%s\","
-					  "n.nspname AS \"%s\", c.description AS \"%s\"\n"
+					  "n.nspname AS \"%s\", d.description AS \"%s\"\n"
 					  "FROM pg_catalog.pg_extension e "
 					  "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace "
-					  "LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid "
+					  "LEFT JOIN pg_catalog.pg_description d ON d.objoid = e.oid "
 					  "LEFT JOIN pg_catalog.pg_available_extensions() ae(name, default_version, comment) ON ae.name = e.extname "
-					  "AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass\n",
+					  "AND d.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass\n",
 					  gettext_noop("Name"),
 					  gettext_noop("Version"),
 					  gettext_noop("Default version"),
-- 
2.43.0

Nathan Bossart

nathandbossart@gmail.com

11 months ago

In reply to: Jelte Fennema-Nio (#8)

Re: Adding extension default version to \dx

On Sun, Feb 23, 2025 at 04:05:34PM +0100, Jelte Fennema-Nio wrote:

On Sun Feb 23, 2025 at 3:51 PM CET, Jelte Fennema-Nio wrote:

Attached is an updated patch that fixes the tests and changes the
whitespace as discussed (including removing a spurious second space
before the ON, which I noticed while changing it). As well as a seprate
patch that changes the "c" alias to "d".

Ugh, I forgot to commit a few additional lines in the second patch.
Fixed now.

Thanks, Jelte. I can take care of committing this if Magnus can't get to
it soon.

--
nathan

#10

Magnus Hagander

magnus@hagander.net

11 months ago

In reply to: Nathan Bossart (#9)

Re: Adding extension default version to \dx

On Tue, Feb 25, 2025 at 4:40 PM Nathan Bossart <nathandbossart@gmail.com>
wrote:

On Sun, Feb 23, 2025 at 04:05:34PM +0100, Jelte Fennema-Nio wrote:

On Sun Feb 23, 2025 at 3:51 PM CET, Jelte Fennema-Nio wrote:

Attached is an updated patch that fixes the tests and changes the
whitespace as discussed (including removing a spurious second space
before the ON, which I noticed while changing it). As well as a seprate
patch that changes the "c" alias to "d".

Ugh, I forgot to commit a few additional lines in the second patch.
Fixed now.

Thanks, Jelte. I can take care of committing this if Magnus can't get to
it soon.

Hi!

Thanks goes to both you and the previous responders - I did manage to mute
this thread away and missed the early replies, but got Jeltes the other day
which brought it back up on my list to get to within the Not Too Long
Future (TM).

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>

#11

Nathan Bossart

nathandbossart@gmail.com

11 months ago

In reply to: Magnus Hagander (#10)

Re: Adding extension default version to \dx

On Tue, Feb 25, 2025 at 04:42:37PM +0100, Magnus Hagander wrote:

Thanks goes to both you and the previous responders - I did manage to mute
this thread away and missed the early replies, but got Jeltes the other day
which brought it back up on my list to get to within the Not Too Long
Future (TM).

Got it, sounds good.

--
nathan

#12

Jelte Fennema-Nio

postgres@jeltef.nl

10 months ago

In reply to: Nathan Bossart (#11)

Re: Adding extension default version to \dx

On Tue, 25 Feb 2025 at 17:11, Nathan Bossart <nathandbossart@gmail.com> wrote:

On Tue, Feb 25, 2025 at 04:42:37PM +0100, Magnus Hagander wrote:

Thanks goes to both you and the previous responders - I did manage to mute
this thread away and missed the early replies, but got Jeltes the other day
which brought it back up on my list to get to within the Not Too Long
Future (TM).

Got it, sounds good.

The commitfest is close to ending, but this hasn't been committed yet.
I think it would be great if either of you could commit it before the
commitfest ends. It would be a shame to have this quality of life
improvement wait another year.

#13

Magnus Hagander

magnus@hagander.net

10 months ago

In reply to: Jelte Fennema-Nio (#12)

Re: Adding extension default version to \dx

On Sat, Mar 22, 2025 at 11:40 AM Jelte Fennema-Nio <postgres@jeltef.nl>
wrote:

On Tue, 25 Feb 2025 at 17:11, Nathan Bossart <nathandbossart@gmail.com>
wrote:

On Tue, Feb 25, 2025 at 04:42:37PM +0100, Magnus Hagander wrote:

Thanks goes to both you and the previous responders - I did manage to

mute

this thread away and missed the early replies, but got Jeltes the

other day

which brought it back up on my list to get to within the Not Too Long
Future (TM).

Got it, sounds good.

The commitfest is close to ending, but this hasn't been committed yet.
I think it would be great if either of you could commit it before the
commitfest ends. It would be a shame to have this quality of life
improvement wait another year.

Sorry about the delay in this one. Nordic PGDay week basically ate up 150%
of my community time for a bit longer before the event than I planned for.

Anyway -- both patch applied now! Thanks!

//Magnus