View to get all the extension control file details
Hi Hackers,
Currently PostgreSQL provides following views to get the extension specific
details
pg_available_extensions - Name, default_version, installed_version, comment
pg_available_extension_versions - Name, version, installed, superuser,
relocatable, schema, requires, comment
But these misses the "directory", "module_pathname" and "encoding"
extension specific informations and these are not available even with
extension specific functions also. There are some extension that differs in
extension name to library name. The pgpool_recovery extension library name
is pgpool-recovery.so, '_' to '-'. While we are developing some tool on top
of PostgreSQL, we found out this problem and it can be solved easily if the
server expose the details that i have and got it from the extension control
file.
Any opinion in adding a new view like "pg_available_extension_details" to
display all extension control file columns? or Adding them to the existing
view is good?
Regards,
Haribabu Kommi
Fujitsu Australia
Hello.
At Mon, 17 Sep 2018 16:49:41 +1000, Haribabu Kommi <kommi.haribabu@gmail.com> wrote in <CAJrrPGf0LZEEhTLCpjRm0Hg0K_37BOY4RUPe7Lrdk0z=ke-6zQ@mail.gmail.com>
Hi Hackers,
Currently PostgreSQL provides following views to get the extension specific
detailspg_available_extensions - Name, default_version, installed_version, comment
pg_available_extension_versions - Name, version, installed, superuser,
relocatable, schema, requires, commentBut these misses the "directory", "module_pathname" and "encoding"
extension specific informations and these are not available even with
extension specific functions also. There are some extension that differs in
extension name to library name. The pgpool_recovery extension library name
is pgpool-recovery.so, '_' to '-'. While we are developing some tool on top
of PostgreSQL, we found out this problem and it can be solved easily if the
server expose the details that i have and got it from the extension control
file.
Nowadays we are going to provide views for such files. Howerer
I'm not a fan of checking extension packaging using such views, I
agree that it's good to have at least a function/view that shows
all available attributes of extensions. Is there no other items
not in controlfiles?
Any opinion in adding a new view like "pg_available_extension_details" to
display all extension control file columns? or Adding them to the existing
view is good?
I felt it's a bit too noisy at first but pg_settings is doing
something like. So +1 to extend the existing
pg_available_extensions view from me from the viewpoint of
consistency with other views of the similar objective.
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
On Thu, Sep 20, 2018 at 3:18 PM Kyotaro HORIGUCHI <
horiguchi.kyotaro@lab.ntt.co.jp> wrote:
Hello.
At Mon, 17 Sep 2018 16:49:41 +1000, Haribabu Kommi <
kommi.haribabu@gmail.com> wrote in
<CAJrrPGf0LZEEhTLCpjRm0Hg0K_37BOY4RUPe7Lrdk0z=ke-6zQ@mail.gmail.com>Hi Hackers,
Currently PostgreSQL provides following views to get the extension
specific
details
pg_available_extensions - Name, default_version, installed_version,
comment
pg_available_extension_versions - Name, version, installed, superuser,
relocatable, schema, requires, commentBut these misses the "directory", "module_pathname" and "encoding"
extension specific informations and these are not available even with
extension specific functions also. There are some extension that differsin
extension name to library name. The pgpool_recovery extension library
name
is pgpool-recovery.so, '_' to '-'. While we are developing some tool on
top
of PostgreSQL, we found out this problem and it can be solved easily if
the
server expose the details that i have and got it from the extension
control
file.
Nowadays we are going to provide views for such files. Howerer
I'm not a fan of checking extension packaging using such views,
Thanks for your opinion.
As we are in the process of developing a tool to find out the details
of the extensions automatically, in that case, it will be helpful if any
view is available.
I
agree that it's good to have at least a function/view that shows
all available attributes of extensions. Is there no other items
not in controlfiles?
I listed all the members of the ExtensionControlFile structure. I don't
find anything else is required.
Any opinion in adding a new view like "pg_available_extension_details" to
display all extension control file columns? or Adding them to theexisting
view is good?
I felt it's a bit too noisy at first but pg_settings is doing
something like. So +1 to extend the existing
pg_available_extensions view from me from the viewpoint of
consistency with other views of the similar objective.
OK, thanks for your view. Will do accordingly.
Regards,
Haribabu Kommi
Fujitsu Australia
On Fri, Sep 21, 2018 at 5:09 PM Haribabu Kommi <kommi.haribabu@gmail.com>
wrote:
On Thu, Sep 20, 2018 at 3:18 PM Kyotaro HORIGUCHI <
horiguchi.kyotaro@lab.ntt.co.jp> wrote:Hello.
At Mon, 17 Sep 2018 16:49:41 +1000, Haribabu Kommi <
kommi.haribabu@gmail.com> wrote in
<CAJrrPGf0LZEEhTLCpjRm0Hg0K_37BOY4RUPe7Lrdk0z=ke-6zQ@mail.gmail.com>Hi Hackers,
Currently PostgreSQL provides following views to get the extension
specific
details
pg_available_extensions - Name, default_version, installed_version,
comment
pg_available_extension_versions - Name, version, installed, superuser,
relocatable, schema, requires, commentBut these misses the "directory", "module_pathname" and "encoding"
extension specific informations and these are not available even with
extension specific functions also. There are some extension thatdiffers in
extension name to library name. The pgpool_recovery extension library
name
is pgpool-recovery.so, '_' to '-'. While we are developing some tool on
top
of PostgreSQL, we found out this problem and it can be solved easily if
the
server expose the details that i have and got it from the extension
control
file.
Nowadays we are going to provide views for such files. Howerer
I'm not a fan of checking extension packaging using such views,Thanks for your opinion.
As we are in the process of developing a tool to find out the details
of the extensions automatically, in that case, it will be helpful if any
view is available.I
agree that it's good to have at least a function/view that shows
all available attributes of extensions. Is there no other items
not in controlfiles?I listed all the members of the ExtensionControlFile structure. I don't
find anything else is required.Any opinion in adding a new view like "pg_available_extension_details"
to
display all extension control file columns? or Adding them to the
existing
view is good?
I felt it's a bit too noisy at first but pg_settings is doing
something like. So +1 to extend the existing
pg_available_extensions view from me from the viewpoint of
consistency with other views of the similar objective.OK, thanks for your view. Will do accordingly.
Here is the patch as per the above discussion.
Regards,
Haribabu Kommi
Fujitsu Australia
Attachments:
0001-pg_available_extensions-update.patchapplication/octet-stream; name=0001-pg_available_extensions-update.patchDownload
From 5f52ab2e7494882837d0b103301f3a2bb8add044 Mon Sep 17 00:00:00 2001
From: Hari Babu <kommi.haribabu@gmail.com>
Date: Mon, 8 Oct 2018 21:20:57 +1100
Subject: [PATCH] pg_available_extensions update
The view is updated with all the existing extension specific options
to list the details of the all the available extensions persent in
the installation folder.
---
doc/src/sgml/catalogs.sgml | 44 ++++++++++++++++++++++++++++
src/backend/catalog/system_views.sql | 3 +-
src/backend/commands/extension.c | 34 +++++++++++++++++++--
src/include/catalog/pg_proc.dat | 5 ++--
src/test/regress/expected/rules.out | 11 +++++--
5 files changed, 90 insertions(+), 7 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 0179deea2e..b91430c156 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -8365,6 +8365,50 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
<entry><type>text</type></entry>
<entry>Comment string from the extension's control file</entry>
</row>
+
+ <row>
+ <entry><structfield>directory</structfield></entry>
+ <entry><type>text</type></entry>
+ <entry>Directory containing the extension's SQL script file(s)</entry>
+ </row>
+
+ <row>
+ <entry><structfield>encoding</structfield></entry>
+ <entry><type>text</type></entry>
+ <entry>The Server converted encoding format that is mapped to many aliases</entry>
+ </row>
+
+ <row>
+ <entry><structfield>module_pathname</structfield></entry>
+ <entry><type>text</type></entry>
+ <entry>String to substitute for MODULE_PATHNAME in the script file(s)</entry>
+ </row>
+
+ <row>
+ <entry><structfield>requires</structfield></entry>
+ <entry><type>name[]</type></entry>
+ <entry>Names of prerequisite extensions,
+ or <literal>NULL</literal> if none</entry>
+ </row>
+
+ <row>
+ <entry><structfield>superuser</structfield></entry>
+ <entry><type>bool</type></entry>
+ <entry>True if only superusers are allowed to install this extension</entry>
+ </row>
+
+ <row>
+ <entry><structfield>relocatable</structfield></entry>
+ <entry><type>bool</type></entry>
+ <entry>True if extension can be relocated to another schema</entry>
+ </row>
+
+ <row>
+ <entry><structfield>schema</structfield></entry>
+ <entry><type>name</type></entry>
+ <entry>Name of the schema that the extension must be installed into,
+ or <literal>NULL</literal> if partially or fully relocatable</entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 0c1bcebb0d..44f9b3d43b 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -270,7 +270,8 @@ CREATE VIEW pg_cursors AS
CREATE VIEW pg_available_extensions AS
SELECT E.name, E.default_version, X.extversion AS installed_version,
- E.comment
+ E.comment, E.directory, E.encoding, E.module_pathname, E.requires, E.superuser, E.relocatable,
+ E.schema
FROM pg_available_extensions() AS E
LEFT JOIN pg_extension AS X ON E.name = X.extname;
diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index 2d761a5773..fb5f99fffd 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -1929,8 +1929,8 @@ pg_available_extensions(PG_FUNCTION_ARGS)
{
ExtensionControlFile *control;
char *extname;
- Datum values[3];
- bool nulls[3];
+ Datum values[10];
+ bool nulls[10];
if (!is_extension_control_filename(de->d_name))
continue;
@@ -1961,6 +1961,36 @@ pg_available_extensions(PG_FUNCTION_ARGS)
nulls[2] = true;
else
values[2] = CStringGetTextDatum(control->comment);
+ /* directory */
+ if (control->directory == NULL)
+ nulls[3] = true;
+ else
+ values[3] = CStringGetTextDatum(control->directory);
+ /* encoding */
+ if (control->encoding < 0)
+ nulls[4] = true;
+ else
+ values[4] = CStringGetTextDatum(pg_encoding_to_char(control->encoding));
+ /* module_pathname */
+ if (control->module_pathname == NULL)
+ nulls[5] = true;
+ else
+ values[5] = CStringGetTextDatum(control->module_pathname);
+ /* requires */
+ if (control->requires == NULL)
+ nulls[6] = true;
+ else
+ values[6] = convert_requires_to_datum(control->requires);
+ /* superuser */
+ values[7] = BoolGetDatum(control->superuser);
+ /* relocatable */
+ values[8] = BoolGetDatum(control->relocatable);
+ /* schema */
+ if (control->schema == NULL)
+ nulls[9] = true;
+ else
+ values[9] = DirectFunctionCall1(namein,
+ CStringGetDatum(control->schema));
tuplestore_putvalues(tupstore, tupdesc, values, nulls);
}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 038a11d6cd..2ed78c8c3f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9388,8 +9388,9 @@
{ oid => '3082', descr => 'list available extensions',
proname => 'pg_available_extensions', procost => '10', prorows => '100',
proretset => 't', provolatile => 's', prorettype => 'record',
- proargtypes => '', proallargtypes => '{name,text,text}',
- proargmodes => '{o,o,o}', proargnames => '{name,default_version,comment}',
+ proargtypes => '', proallargtypes => '{name,text,text,text,text,text,_name,bool,bool,name}',
+ proargmodes => '{o,o,o,o,o,o,o,o,o,o}', proargnames => '{name,default_version,comment,directory,
+ encoding,module_pathname,requires,superuser,relocatable,schema}',
prosrc => 'pg_available_extensions' },
{ oid => '3083', descr => 'list available extension versions',
proname => 'pg_available_extension_versions', procost => '10',
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 078129f251..cae8efc8a3 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1310,8 +1310,15 @@ pg_available_extension_versions| SELECT e.name,
pg_available_extensions| SELECT e.name,
e.default_version,
x.extversion AS installed_version,
- e.comment
- FROM (pg_available_extensions() e(name, default_version, comment)
+ e.comment,
+ e.directory,
+ e.encoding,
+ e.module_pathname,
+ e.requires,
+ e.superuser,
+ e.relocatable,
+ e.schema
+ FROM (pg_available_extensions() e(name, default_version, comment, directory, encoding, module_pathname, requires, superuser, relocatable, schema)
LEFT JOIN pg_extension x ON ((e.name = x.extname)));
pg_config| SELECT pg_config.name,
pg_config.setting
--
2.18.0.windows.1
On Wed, Oct 10, 2018 at 8:27 AM Haribabu Kommi <kommi.haribabu@gmail.com> wrote:
Here is the patch as per the above discussion.
One potential problem with this is that we could add more control-file
attributes in the future, and it will be annoying if the view ends up
with a million columns, or if we ever have to rename them. People who
have created objects that depend on those views may find that
pg_dump/restore or pg_upgrade fail, just as they do when we whack
around pg_stat_activity. pg_settings gets around that using an
EAV-like format. I'm not sure that's the best solution here, but it's
something to think about.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Sat, Oct 13, 2018 at 3:57 AM Robert Haas <robertmhaas@gmail.com> wrote:
On Wed, Oct 10, 2018 at 8:27 AM Haribabu Kommi <kommi.haribabu@gmail.com>
wrote:Here is the patch as per the above discussion.
One potential problem with this is that we could add more control-file
attributes in the future, and it will be annoying if the view ends up
with a million columns, or if we ever have to rename them.
Yes, there is a problem if we add more attributes or rename them.
People who
have created objects that depend on those views may find that
pg_dump/restore or pg_upgrade fail, just as they do when we whack
around pg_stat_activity. pg_settings gets around that using an
EAV-like format. I'm not sure that's the best solution here, but it's
something to think about.
similar like pg_settings view, so displaying all the data without validating
them from pg_extension may solve the problem.
Other idea to avoid this problem is, how about displaying the extra columns
using JSONB
data type, so that all the extra additional columns that are not important
will go to that column?
Regards,
Haribabu Kommi
Fujitsu Australia
Hello.
At Wed, 17 Oct 2018 18:38:05 +1100, Haribabu Kommi <kommi.haribabu@gmail.com> wrote in <CAJrrPGdyJ=a=Aqu6Uzz2CsFt+erzg4GH15SaYU6uUPYpqkaj1Q@mail.gmail.com>
On Sat, Oct 13, 2018 at 3:57 AM Robert Haas <robertmhaas@gmail.com> wrote:
On Wed, Oct 10, 2018 at 8:27 AM Haribabu Kommi <kommi.haribabu@gmail.com>
wrote:Here is the patch as per the above discussion.
One potential problem with this is that we could add more control-file
attributes in the future, and it will be annoying if the view ends up
with a million columns, or if we ever have to rename them.Yes, there is a problem if we add more attributes or rename them.
FWIW, I came to feel that pg_stat_activity getting too bolder a
bit in both width and hight. I'm accustomed to type extra "where
backend_type like 'client%'":p
People who
have created objects that depend on those views may find that
pg_dump/restore or pg_upgrade fail, just as they do when we whack
around pg_stat_activity. pg_settings gets around that using an
EAV-like format. I'm not sure that's the best solution here, but it's
something to think about.similar like pg_settings view, so displaying all the data without validating
them from pg_extension may solve the problem.
I'm not sure I understand correctly, it seems that the problem
raised is not data dependency but schema evolution, or schema
dependency. (Anyway we cannot restore a view data in a straight
way.)
Other idea to avoid this problem is, how about displaying the extra columns
using JSONB
data type, so that all the extra additional columns that are not important
will go to that column?
It may be an EA->V function. Specifically pg_extension_info(name
text, attr text) returns text. Or may be such like
pg_get_environment_info(category text, name text, attr text), where category
would be 'extension' in this case.
=# select name, version,
pg_get_environemnt('extension', name, 'encoding') as encoding
from pg_extension;
If we enforce user views to use the function by, say, not
bundling system view using it, such views won't complain during
restoration. (May complain at runtime later, though.)
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center