Consistent \d commands in psql

Attached is an updated version of my psql patch that makes the \d
backslash commands perform in an intuitive, consistent way.
Specifically, the following objects will be treated as first class
citizens (as tables and indexes currently are) by showing all the
non-system objects by default and requiring a "S" to see the system
ones.

aggregates
conversions
comments
domains
operators
functions
types

Currently, there is no way to view all the non-system functions in a
database using backslash commands, as you can with \dt, unless all of
the functions happen to be in a single schema ("\df myschema."). With
this patch, it would be as simple as "\df", and the current behavior
would be done with "\dfS".

This patch also adds a few new things to the tab-completion table, such
as comments and conversions.

Comments welcome. Last time the patch withered on the vine, despite
strong support from -general, hence I'm going to try again, as I really
want a way to view my functions without querying the pg_proc tables
directly. :)

--
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200711042003
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

This has been saved for the 8.4 release:

http://momjian.postgresql.org/cgi-bin/pgpatches_hold

---------------------------------------------------------------------------

Greg Sabino Mullane wrote:
> Attached is an updated version of my psql patch that makes the \d
> backslash commands perform in an intuitive, consistent way.
> Specifically, the following objects will be treated as first class
> citizens (as tables and indexes currently are) by showing all the
> non-system objects by default and requiring a "S" to see the system
> ones.
>
> aggregates
> conversions
> comments
> domains
> operators
> functions
> types
>
> Currently, there is no way to view all the non-system functions in a
> database using backslash commands, as you can with \dt, unless all of
> the functions happen to be in a single schema ("\df myschema."). With
> this patch, it would be as simple as "\df", and the current behavior
> would be done with "\dfS".
>
> This patch also adds a few new things to the tab-completion table, such
> as comments and conversions.
>
> Comments welcome. Last time the patch withered on the vine, despite
> strong support from -general, hence I'm going to try again, as I really
> want a way to view my functions without querying the pg_proc tables
> directly. :)
>
> --
> Greg Sabino Mullane greg(at)turnstep(dot)com
> PGP Key: 0x14964AC8 200711042003
> http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
>

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

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

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

Greg Sabino Mullane wrote:
> Attached is an updated version of my psql patch that makes the \d
> backslash commands perform in an intuitive, consistent way.
> Specifically, the following objects will be treated as first class
> citizens (as tables and indexes currently are) by showing all the
> non-system objects by default and requiring a "S" to see the system
> ones.
>
> aggregates
> conversions
> comments
> domains
> operators
> functions
> types
>
> Currently, there is no way to view all the non-system functions in a
> database using backslash commands, as you can with \dt, unless all of
> the functions happen to be in a single schema ("\df myschema."). With
> this patch, it would be as simple as "\df", and the current behavior
> would be done with "\dfS".

Yes, that seems like a good idea. \df in particular has been too noisy
to be usable. Not sure about conversions and domains; I doubt anyone
creates custom conversions in practice, and there's no system domains in
a standard installation.

Does anyone want to argue that there's a backward-compatibility problem
with changing \df? I don't think there is; you shouldn't be using psql
backslash commands in an application.

> This patch also adds a few new things to the tab-completion table, such
> as comments and conversions.

There's a bunch of merge conflicts in the diff.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Greg Sabino Mullane <greg(at)turnstep(dot)com> writes:
> Attached is an updated version of my psql patch that makes the \d
> backslash commands perform in an intuitive, consistent way.
> Specifically, the following objects will be treated as first class
> citizens (as tables and indexes currently are) by showing all the
> non-system objects by default and requiring a "S" to see the system
> ones.

> aggregates
> conversions
> comments
> domains
> operators
> functions
> types

> Currently, there is no way to view all the non-system functions in a
> database using backslash commands, as you can with \dt, unless all of
> the functions happen to be in a single schema ("\df myschema."). With
> this patch, it would be as simple as "\df", and the current behavior
> would be done with "\dfS".

Hmm. Personally, most of my uses of \df are for the purpose of looking
for built-in functions, and so this'd be a step backwards for my usage.
Likewise for operators. Maybe I'm in the minority or maybe not.
The only one of these things for which the argument seems entirely
compelling is comments. I do understand the appeal of consistency but
sometimes it's not such a great thing.

Also, if we're going to try to enforce such a policy consistently,
shouldn't the text-search-related \d commands be changed likewise?

In any case, this patch fails to apply cleanly, and did even when it
was generated, because it contains merge conflict reports, eg

> "FROM pg_catalog.pg_proc p\n"
> + <<<<<<< describe.c
> + "JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
> + =======
> " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
> + >>>>>>> 1.160
> "WHERE p.proisagg\n",

I'm not going to risk trying to decipher the intended changes from this.

regards, tom lane

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Hmm. Personally, most of my uses of \df are for the purpose of looking
> for built-in functions, and so this'd be a step backwards for my usage.
> Likewise for operators. Maybe I'm in the minority or maybe not.
> The only one of these things for which the argument seems entirely
> compelling is comments. I do understand the appeal of consistency but
> sometimes it's not such a great thing.

The problem is that there's absolutely no way to do the equivalent of a plain
\dt and get a list of just your user functions. That's a real headache and it
gets worse as we add more and more system functions too.

It might be cute to see if the pattern matches any user functions and if not
try again with system functions. So you would still get results if you did
"\df rtrim" for example.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning

On 31/03/2008, Gregory Stark <stark(at)enterprisedb(dot)com> wrote:
> It might be cute to see if the pattern matches any user functions and if not
> try again with system functions. So you would still get results if you did
> "\df rtrim" for example.
>

Nice idea. +1 for this behaviour.

Cheers,
BJ

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> It might be cute to see if the pattern matches any user functions and if not
> try again with system functions. So you would still get results if you did
> "\df rtrim" for example.

Interesting idea. IIUC, \df would give you either all user functions
*or* all system functions depending on the actual catalog contents,
while \dfS would always give you just system functions. That means
there'd be no way to replicate the all-functions-of-both-types behavior
that has been the default in every prior release. That sounds like
a recipe for getting complaints --- changing the default behavior is
one thing, but making it so that that behavior isn't available at
all is surely going to break somebody's code or habitual usage.

How about

\dfS -> sys functions only
\dfU -> user functions only
\dfSU -> all functions (should allow \dfUS spelling too)
\df -> behavior proposed by Greg

(and similarly for all other \d commands of course). Then anyone
who's depending on the old behavior can still get it with a couple
more keystrokes.

BTW, should we remove the special hack that discriminates against
showing I/O functions (or really anything that touches cstring) in \df?
ISTM that was mostly there to reduce clutter, and this proposal solves
that problem more neatly. I know I've cursed that behavior under my
breath more than once, but again maybe my usage isn't typical.

regards, tom lane

On 01/04/2008, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> ... That means
> there'd be no way to replicate the all-functions-of-both-types behavior
> that has been the default in every prior release.

> \dfS -> sys functions only
> \dfU -> user functions only
> \dfSU -> all functions (should allow \dfUS spelling too)
> \df -> behavior proposed by Greg

How about \df* rather than (or in addition to) \dfSU & \dfUS?

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Gregory Stark <stark(at)enterprisedb(dot)com> writes:
>> It might be cute to see if the pattern matches any user functions and if not
>> try again with system functions. So you would still get results if you did
>> "\df rtrim" for example.
>
> Interesting idea. IIUC, \df would give you either all user functions
> *or* all system functions depending on the actual catalog contents,
> while \dfS would always give you just system functions. That means
> there'd be no way to replicate the all-functions-of-both-types behavior
> that has been the default in every prior release. That sounds like
> a recipe for getting complaints --- changing the default behavior is
> one thing, but making it so that that behavior isn't available at
> all is surely going to break somebody's code or habitual usage.

Actually on further thought I wonder if it wouldn't be simpler (and perhaps
more consistent with \d) to just list *all* matches iff a pattern is provided
but list only user functions if *no* pattern is provided.

That would effectively be exactly the current behaviour except that you would
have to do \dfS to get a list of system functions. And yeah, you wouldn't be
able to get a list of all functions whether system or user functions. I
suppose you could do \df *

One --perhaps nice, perhaps not-- property of this is that if you defined a
function named "rtrim" and then did "\df rtrim" it would show you _both_ the
system and user function and make it easier to see the conflict. Whereas the
other behaviour I proposed would hide the system function which might
exacerbate the user's confusion.

> BTW, should we remove the special hack that discriminates against
> showing I/O functions (or really anything that touches cstring) in \df?
> ISTM that was mostly there to reduce clutter, and this proposal solves
> that problem more neatly. I know I've cursed that behavior under my
> breath more than once, but again maybe my usage isn't typical.

. o O Ohh! That's why I can never find them!

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> One --perhaps nice, perhaps not-- property of this is that if you defined a
> function named "rtrim" and then did "\df rtrim" it would show you _both_ the
> system and user function and make it easier to see the conflict. Whereas the
> other behaviour I proposed would hide the system function which might
> exacerbate the user's confusion.

Yeah, that is a very good point indeed.

Another way we could approach this is

\df -> all functions
\dfS -> sys functions only
\dfU -> user functions only

which avoids falling into the trap Greg mentions.

regards, tom lane

Tom Lane wrote:

> BTW, should we remove the special hack that discriminates against
> showing I/O functions (or really anything that touches cstring) in \df?
> ISTM that was mostly there to reduce clutter, and this proposal solves
> that problem more neatly. I know I've cursed that behavior under my
> breath more than once, but again maybe my usage isn't typical.

+1

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Gregory Stark <stark(at)enterprisedb(dot)com> writes:
>> One --perhaps nice, perhaps not-- property of this is that if you defined a
>> function named "rtrim" and then did "\df rtrim" it would show you _both_ the
>> system and user function and make it easier to see the conflict. Whereas the
>> other behaviour I proposed would hide the system function which might
>> exacerbate the user's confusion.
>
> Yeah, that is a very good point indeed.
>
> Another way we could approach this is
>
> \df -> all functions
> \dfS -> sys functions only
> \dfU -> user functions only
>
> which avoids falling into the trap Greg mentions.

That doesn't satisfy the original source of the annoyance which is that \df
spams your terminal with ten screens of system functions with your user
functions hidden amongst them.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
>>> One --perhaps nice, perhaps not-- property of this is that if you defined a
>>> function named "rtrim" and then did "\df rtrim" it would show you _both_ the
>>> system and user function and make it easier to see the conflict. Whereas the
>>> other behaviour I proposed would hide the system function which might
>>> exacerbate the user's confusion.

>> Another way we could approach this is
>> ...

> That doesn't satisfy the original source of the annoyance which is that \df
> spams your terminal with ten screens of system functions with your user
> functions hidden amongst them.

Sure, but I think the core objection there is that there is no easy way
to see only the user-defined functions. Given your point quoted first
above, I'm unconvinced that should be the default behavior.

regards, tom lane

On Tue, Apr 01, 2008 at 12:58:44AM -0400, Tom Lane wrote:
> Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> >>> One --perhaps nice, perhaps not-- property of this is that if
> >>> you defined a function named "rtrim" and then did "\df rtrim" it
> >>> would show you _both_ the system and user function and make it
> >>> easier to see the conflict. Whereas the other behaviour I
> >>> proposed would hide the system function which might exacerbate
> >>> the user's confusion.
>
> >> Another way we could approach this is ...
>
> > That doesn't satisfy the original source of the annoyance which is
> > that \df spams your terminal with ten screens of system functions
> > with your user functions hidden amongst them.
>
> Sure, but I think the core objection there is that there is no easy
> way to see only the user-defined functions. Given your point quoted
> first above, I'm unconvinced that should be the default behavior.

When we have a bad default--and I'd argue that for anyone not
developing PostgreSQL itself, showing system functions is a bad
default--we should change it to something sane.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter <david(at)fetter(dot)org> writes:
> When we have a bad default--and I'd argue that for anyone not
> developing PostgreSQL itself, showing system functions is a bad
> default--we should change it to something sane.

I disagree with your parenthetical argument here, mainly on the strength
of Greg's point about how that might hide the existence of conflicts.
But in any case the discussion here is first about what set of behaviors
we need to provide, and only second about which one should be default.

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/04/2008, Tom Lane wrote:
> David Fetter writes:
> > When we have a bad default--and I'd argue that for anyone not
> > developing PostgreSQL itself, showing system functions is a bad
> > default--we should change it to something sane.
>
> I disagree with your parenthetical argument here, mainly on the strength
> of Greg's point about how that might hide the existence of conflicts.
> But in any case the discussion here is first about what set of behaviors
> we need to provide, and only second about which one should be default.
>

If I read Greg's latter proposal correctly, he was suggesting

\df Lists all user functions
\df [pattern] Lists both system and user functions matching [pattern]
\df * Lists all system and user functions

This doesn't provide is "all system functions only", but:

1. That list is way too long to be of much use in a psql context
2. You can still do a \df pg_catalog.* if you're really that keen.

It also doesn't provide "only user functions matching [pattern]", but
is that really a problem? I suppose you could conceive of a situation
where somebody is looking for all the user funcs matching "int*" and
getting annoyed by having to scroll past ~200 system funcs, but you
can always refine your pattern, or clamp it to a particular schema.

Regards,
BJ
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (GNU/Linux)
Comment: http://getfiregpg.org

iD8DBQFH8qtm5YBsbHkuyV0RAkXlAKCH8lL9H8XInLRvlbKnh84XafXyZwCg2Qom
a3TuUMKHH7Yq/zZaA4MI7hk=
=yLQJ
-----END PGP SIGNATURE-----

"Brendan Jurd" <direvus(at)gmail(dot)com> writes:
> If I read Greg's latter proposal correctly, he was suggesting

> \df Lists all user functions
> \df [pattern] Lists both system and user functions matching [pattern]
> \df * Lists all system and user functions

Hmm, I must've misread it, because I didn't understand it quite like
that. That seems like a nice simple minimal-featuritis approach.

One question: should \df really list *all* nonsystem functions? Or just
the ones that are visible in your search path? I'd be inclined to say
the second.

regards, tom lane

Tom Lane wrote:
> One question: should \df really list *all* nonsystem functions? Or just
> the ones that are visible in your search path? I'd be inclined to say
> the second.
>
>
>

+1 (although maybe that discussion belongs on -hackers, or even -general)

cheers

andrew

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> "Brendan Jurd" <direvus(at)gmail(dot)com> writes:
>> If I read Greg's latter proposal correctly, he was suggesting
>
>> \df Lists all user functions
>> \df [pattern] Lists both system and user functions matching [pattern]
>> \df * Lists all system and user functions
>
> Hmm, I must've misread it, because I didn't understand it quite like
> that. That seems like a nice simple minimal-featuritis approach.

Sorry if was confusing but yes, that is what I intended by my second proposal.
I prefer it to my own first proposal or any of the others.

I admit I was thinking primarily of non-globbing cases for pattern. As in, I
would want \df rtrim to "work". I suppose it could be annoying to have to type
\df public.* -- there's nothing stopping us from having \dfU and \dfS too I
suppose, though I doubt most people would find them.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!

Andrew Dunstan wrote:
>
>
> Tom Lane wrote:
> > One question: should \df really list *all* nonsystem functions? Or
> > just the ones that are visible in your search path? I'd be
> > inclined to say the second.
> >
> >
> >
>
>
> +1 (although maybe that discussion belongs on -hackers, or even
> -general)

+1. It seems to be what would follow from the principle of least
surprise that this works the same way as \dt does - as close as
possible, that is.

//Magnus

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

> \df Lists all user functions
> \df [pattern] Lists both system and user functions matching [pattern]
> \df * Lists all system and user functions

I don't like this for two reasons: the items returned changes based on
the existence of args, rather than on the command itself, and more
importantly, this would make it inconsistent with the other backslash
commands.

> One question: should \df really list *all* nonsystem functions? Or just
> the ones that are visible in your search path? I'd be inclined to say
> the second.

Agreed, we should respect the search path like the other commands do.
Although I wonder if a long-term idea would be to at least indicate
that there are other same-named things in your path?

- --
Greg Sabino Mullane greg(at)endpoint(dot)com greg(at)turnstep(dot)com
End Point Corporation 610-983-9073
PGP Key: 0x14964AC8 200804020915
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkfzh0IACgkQvJuQZxSWSsgTCgCdEv1vl7c0uIpIU3pSiH/VLvEg
m5wAni5/gJ0K2w1gpaNaWpVFCRJX200f
=ZnTm
-----END PGP SIGNATURE-----

"Greg Sabino Mullane" <greg(at)turnstep(dot)com> writes:
>> \df Lists all user functions
>> \df [pattern] Lists both system and user functions matching [pattern]
>> \df * Lists all system and user functions

> I don't like this for two reasons: the items returned changes based on
> the existence of args, rather than on the command itself, and more
> importantly, this would make it inconsistent with the other backslash
> commands.

I think you misunderstood the context of the discussion. Whatever we do
will be done to the whole family of \d commands --- we are just using
\df as an exemplar.

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 03/04/2008, Greg Sabino Mullane wrote:
> > \df Lists all user functions
> > \df [pattern] Lists both system and user functions matching [pattern]
> > \df * Lists all system and user functions
>
> I don't like this for two reasons: the items returned changes based on
> the existence of args, rather than on the command itself, and more
> importantly, this would make it inconsistent with the other backslash
> commands.
>

To address your first complaint, I think it makes perfect sense to
behave differently based on whether an argument has been supplied.

The existence of a pattern tells you something about the user's
intention in issuing the command. If they provide a pattern, they
probably want to /search/ for something. If they don't provide a
pattern they probably want to get a /listing/ of some set.

Regards,
BJ
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (GNU/Linux)
Comment: http://getfiregpg.org

iD8DBQFH86u15YBsbHkuyV0RAmcMAKCy44zQaEcPA+QDpXr2+3vrSPucDwCgg3FG
8cq7P2DvI/ogqrHwM9Zpzx0=
=IEvi
-----END PGP SIGNATURE-----

The patch author has been given feedback, so this has been saved for the
next commit-fest:

http://momjian.postgresql.org/cgi-bin/pgpatches_hold

---------------------------------------------------------------------------

Greg Sabino Mullane wrote:
[ There is text before PGP section. ]
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: RIPEMD160
>
>
> > \df Lists all user functions
> > \df [pattern] Lists both system and user functions matching [pattern]
> > \df * Lists all system and user functions
>
> I don't like this for two reasons: the items returned changes based on
> the existence of args, rather than on the command itself, and more
> importantly, this would make it inconsistent with the other backslash
> commands.
>
> > One question: should \df really list *all* nonsystem functions? Or just
> > the ones that are visible in your search path? I'd be inclined to say
> > the second.
>
> Agreed, we should respect the search path like the other commands do.
> Although I wonder if a long-term idea would be to at least indicate
> that there are other same-named things in your path?
>
> - --
> Greg Sabino Mullane greg(at)endpoint(dot)com greg(at)turnstep(dot)com
> End Point Corporation 610-983-9073
> PGP Key: 0x14964AC8 200804020915
> http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
> -----BEGIN PGP SIGNATURE-----
>
> iEYEAREDAAYFAkfzh0IACgkQvJuQZxSWSsgTCgCdEv1vl7c0uIpIU3pSiH/VLvEg
> m5wAni5/gJ0K2w1gpaNaWpVFCRJX200f
> =ZnTm
> -----END PGP SIGNATURE-----
>
>
>
> --
> Sent via pgsql-patches mailing list (pgsql-patches(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-patches

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

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> "Greg Sabino Mullane" <greg(at)turnstep(dot)com> writes:
>>> \df Lists all user functions
>>> \df [pattern] Lists both system and user functions matching [pattern]
>>> \df * Lists all system and user functions
>
>> I don't like this for two reasons: the items returned changes based on
>> the existence of args, rather than on the command itself, and more
>> importantly, this would make it inconsistent with the other backslash
>> commands.
>
> I think you misunderstood the context of the discussion. Whatever we do
> will be done to the whole family of \d commands --- we are just using
> \df as an exemplar.

Hm, I didn't realize that. I thought the reason \df was special was that users
often need to refer to "system" functions. Whereas they never need to refer to
system tables or system sequences etc unless they know that's what they're
looking for.

However, now that I look at the list of \d commands that argument kind of
falls flat. Users also need to find "system" operators, data types, etc.

And I think the same logic as \df applies for those other things too. \dt
pg_class should "just work". And if you create a macaddr data type it doesn't
seem like too much of an imposition to play it safe and have \dT macaddr show
the user that there are now two matching data types.

So I guess I should just play along and pretend that's what I meant all along
:)

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!