Обсуждение: View to get all the extension control file details

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, 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.

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 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

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