Re: psql \d* and system objects

On Sun, Mar 29, 2009 at 1:32 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Now I *have* a user function named sin(), it's not getting called
> (which might surprise me if I didn't know there was a conflicting
> system function) and \df doesn't show me either one.
>
> I actually was expecting the above example to show me the user function,
> which I was then going to rant about being a lie.  But the actual
> behavior is even worse than that.

Well, that is clearly a bug.

> There is not anything that is not broken about HEAD's behavior,
> and the sooner we admit that the sooner we can get to a fix.
> Slicing the categorization more finely or in different ways is
> not going to improve matters: the concept that there is a categorization
> that will make it hide requested objects is wrong to begin with.

Well, by that argument, 8.3 is broken, too, because it hides
pg_catalog tables, views, sequences, and indices. It's fair to say
that the system shouldn't hide "requested" objects, but sometimes
people want request only the objects that they created, and not the
ones that are part of the system. In 8.3, if you want to list all of
the functions you've defined (as opposed to the ones that came with
the system), you have a couple of not-so-fun options:

1. pg_dump -s | grep 'CREATE.*FUNCTION'
2. looking up the **40-line** query that \df issues, modifying it to
exclude system functions, and running it by hand

This has been a huge irritation to me for many years, and (whatever
else you can say about the patch that started all this) it makes this
particular thing a whole lot easier. I'd like to find a way to still
have that be easy while fixing some of the other issues.

Even in 8.3, we have this oddness:

\dt pg_index
No matching relations found.
select sum(1) from pg_index;
sum
-----
332
(1 row)

One idea I had is to issue some kind of a warning if a \d command
matches system objects that are excluded from the output, like this:

note: %d system objects also found, use %s to display

...Robert