psql \d commands and information_schema

Attached is a patch that modifies psql \dX commands to treat objects
in information_schema as "system objects". This prevents them from
showing up in \dX *.* and polluting the user objects list. This is
especially annoying if user objects are in multiple schemas, and
one wants to get a quick overview by running \dX *.*

regards,
Martin

Martin Pihlak wrote:
> Attached is a patch that modifies psql \dX commands to treat objects
> in information_schema as "system objects". This prevents them from
> showing up in \dX *.* and polluting the user objects list. This is
> especially annoying if user objects are in multiple schemas, and
> one wants to get a quick overview by running \dX *.*

I have applied a simplified version of your patch, attached, that adds
just a "AND <>" line to the query; I was a little concerned that IN
might affect performance, and the macros seemed kind of complicated.

Also, since my patch this morning any pattern will also trigger
information_schema lookups, not just 'S'.

Thanks.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:
> I have applied a simplified version of your patch, attached, that adds
> just a "AND <>" line to the query; I was a little concerned that IN
> might affect performance, and the macros seemed kind of complicated.
>

Thanks.

> Also, since my patch this morning any pattern will also trigger
> information_schema lookups, not just 'S'.
>

Hmm, this is a problem -- "\dX *.*" now shows all objects, and there is
no way to list only user objects. This is especially a problem if user
objects are scattered in different schemas.

I'd suggest that the U option to be reintroduced for the purpouse to describe
only user objects. One possible solution is to modifiy exec_command so that
show_system is set to true if S or pattern present, and false if U is
specified.

Proposed patch attached.

regards,
Martin

Martin Pihlak <martin(dot)pihlak(at)gmail(dot)com> writes:
> Hmm, this is a problem -- "\dX *.*" now shows all objects, and there is
> no way to list only user objects. This is especially a problem if user
> objects are scattered in different schemas.

I don't find this to be a pressing problem. If the user has lots of
schemas, they probably have lots of objects too, and are unlikely to
need such a thing. Moreover, with lots of schemas the behavior of the
search_path enters into it too; a simple U switch isn't going to provide
a full answer.

regards, tom lane

Tom Lane wrote:
> I don't find this to be a pressing problem. If the user has lots of
> schemas, they probably have lots of objects too, and are unlikely to
> need such a thing.

Current behaviour makes it impossible to get a quick overview of all the
user defined objects. And it doesn't really matter what the number of
schemas is -- it gets messy for even small number of schemas and objects.
Lets assume 2 user tables in schemas "public" and "foo".

\dt *.* will give:
List of relations
Schema | Name | Type | Owner
--------------------+-------------------------+-------+---------
foo | t2 | table | martinp
information_schema | sql_features | table | martinp
...
pg_catalog | pg_aggregate | table | martinp
...
public | t1 | table | martinp
(51 rows)

This is a lot of irrelevant stuff the user has to filter out. It is
much worse with functions -- \df *.* results in 1900+ functions that
I usually don't want to see. The alternative is to perform a \dn first
and then loop through that (this is the annoyance the U switch would
remove).

> search_path enters into it too; a simple U switch isn't going to provide
> a full answer.
>

For our needs I wouldn't really consider using search_path for anything
but temporary hacks. However, a psql variable that specifies a list of
name patterns to be excluded from describe, could be useful. Something
along the lines of:

\set DESCRIBE_EXCLUDE_PATTERNS 'pg_catalog.*, information_schema.*, ...'

This could be then customized to each site's needs -- add pgq, slony,
etc. and put to .psqlrc. It is questionable whether the filter should be
applied to default \dX (override with S to describe all). Maybe it'd be
better to introduce an extra switch that applies the filters.

I just noticed that something similar was already suggested by Euler in
http://archives.postgresql.org/message-id/49CDB4E0.8030603@timbira.com

regards,
Martin

Martin Pihlak <martin(dot)pihlak(at)gmail(dot)com> writes:
> Tom Lane wrote:
>> I don't find this to be a pressing problem. If the user has lots of
>> schemas, they probably have lots of objects too, and are unlikely to
>> need such a thing.

> Current behaviour makes it impossible to get a quick overview of all the
> user defined objects. And it doesn't really matter what the number of
> schemas is -- it gets messy for even small number of schemas and objects.

Well, if they're all in your search_path then plain old \df will do
fine. If they're not in your search path then I think it gets pretty
questionable whether they're "user defined" in a real sense. It seems
more likely that you've got a pile of modules loaded, and which of those
modules is "user defined" for your immediate purposes is something that
psql can't hope to intuit.

In short I'm still not convinced that there's much use-case for a
simple "U" switch.

> For our needs I wouldn't really consider using search_path for anything
> but temporary hacks. However, a psql variable that specifies a list of
> name patterns to be excluded from describe, could be useful. Something
> along the lines of:

> \set DESCRIBE_EXCLUDE_PATTERNS 'pg_catalog.*, information_schema.*, ...'

Possibly something like this could be useful. But I'd like to see it
designed in conjunction with the "real module facility" that we keep
hoping for, because I think a situation with a number of modules loaded
is going to be exactly where you want some flexible filtering.

regards, tom lane

Tom Lane wrote:
> Well, if they're all in your search_path then plain old \df will do
> fine. If they're not in your search path then I think it gets pretty
> questionable whether they're "user defined" in a real sense. It seems
> more likely that you've got a pile of modules loaded, and which of those
> modules is "user defined" for your immediate purposes is something that
> psql can't hope to intuit.
>

I my environment schemas are used for namespace separation, so it doesn't
make much sense to use search_path to pull everything back into a single
namespace. Might as well use public for everything then. And these are not
really modules, just ordinary user objects in separate namespaces.

The main benefit of the U switch is that it enables to get a quick overview
of whats deployed to the database. At the moment this is not possible as the
*.* listings are polluted with system objects.

PS. The original \dfU suggestion was made by you in
http://archives.postgresql.org/message-id/28027.1206976227@sss.pgh.pa.us.
And seems, it didn't receive too much negative feedback then.

regards,
Martin

Martin Pihlak wrote:
> Tom Lane wrote:
> > Well, if they're all in your search_path then plain old \df will do
> > fine. If they're not in your search path then I think it gets pretty
> > questionable whether they're "user defined" in a real sense. It seems
> > more likely that you've got a pile of modules loaded, and which of those
> > modules is "user defined" for your immediate purposes is something that
> > psql can't hope to intuit.
> >
>
> I my environment schemas are used for namespace separation, so it doesn't
> make much sense to use search_path to pull everything back into a single
> namespace. Might as well use public for everything then. And these are not
> really modules, just ordinary user objects in separate namespaces.
>
> The main benefit of the U switch is that it enables to get a quick overview
> of whats deployed to the database. At the moment this is not possible as the
> *.* listings are polluted with system objects.
>
> PS. The original \dfU suggestion was made by you in
> http://archives.postgresql.org/message-id/28027.1206976227@sss.pgh.pa.us.
> And seems, it didn't receive too much negative feedback then.

We already had a huge discussion over 'S' and I think we did as good as
we can. I think we risk overcomplicating the API by adding U, but we
can revisit this in 8.5 once we get more feedback from users.

But a larger issue is that if we try to make everyone happy with the
psql API, the API will be unusablely complex. We have just not seen
enough demand for U yet.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Wed, Apr 8, 2009 at 3:49 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> We already had a huge discussion over 'S' and I think we did as good as
> we can.  I think we risk overcomplicating the API by adding U, but we
> can revisit this in 8.5 once we get more feedback from users.

I think we'll need to take stock before 8.4 actually. Tom's pointed
out a whole pile of problems with the current approach and I'm
becoming convinced he's right. I know I was one of the proponents of
the change but I didn't realize how bad the problems were.

As I understand his proposal is that \df with no pattern could list
all user functions but \df <pattern> should always follow the
search_path and show the same functions that would actually be called.

One possibility for reducing clutter would be moving a whole slew of
the system functions which are never intended for users to call
explicitly to a different schema which isn't implicitly added to
search_path. That would at least get all the RI functions, bt procs,
maybe even the operator functions out of the way.

--
greg

Greg Stark <stark(at)enterprisedb(dot)com> writes:
> On Wed, Apr 8, 2009 at 3:49 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> We already had a huge discussion over 'S' and I think we did as good as
>> we can. I think we risk overcomplicating the API by adding U, but we
>> can revisit this in 8.5 once we get more feedback from users.

> I think we'll need to take stock before 8.4 actually. Tom's pointed
> out a whole pile of problems with the current approach and I'm
> becoming convinced he's right. I know I was one of the proponents of
> the change but I didn't realize how bad the problems were.

> As I understand his proposal is that \df with no pattern could list
> all user functions but \df <pattern> should always follow the
> search_path and show the same functions that would actually be called.

Uh, that change got applied last week ...
http://archives.postgresql.org/pgsql-committers/2009-04/msg00014.php

> One possibility for reducing clutter would be moving a whole slew of
> the system functions which are never intended for users to call
> explicitly to a different schema which isn't implicitly added to
> search_path. That would at least get all the RI functions, bt procs,
> maybe even the operator functions out of the way.

Perhaps, but is it really important? I haven't noticed that those
things were cluttering my \df searches anyway.

BTW, I hesitate to mention this and perhaps upset a fragile consensus,
but should we remove the special-case code in \df that tries to hide I/O
functions by excluding functions that take or return cstring? I think
that its value has largely disappeared given the new overall behavior.

regards, tom lane

Tom Lane escribió:

> BTW, I hesitate to mention this and perhaps upset a fragile consensus,
> but should we remove the special-case code in \df that tries to hide I/O
> functions by excluding functions that take or return cstring? I think
> that its value has largely disappeared given the new overall behavior.

+1

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera wrote:
> Tom Lane escribi?:
>
> > BTW, I hesitate to mention this and perhaps upset a fragile consensus,
> > but should we remove the special-case code in \df that tries to hide I/O
> > functions by excluding functions that take or return cstring? I think
> > that its value has largely disappeared given the new overall behavior.
>
> +1

Agreed.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Alvaro Herrera wrote:
>> Tom Lane escribi?:
>>> BTW, I hesitate to mention this and perhaps upset a fragile consensus,
>>> but should we remove the special-case code in \df that tries to hide I/O
>>> functions by excluding functions that take or return cstring? I think
>>> that its value has largely disappeared given the new overall behavior.
>>
>> +1

> Agreed.

Done.

regards, tom lane