Consistent \d commands in psql

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@turnstep.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@momjian.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@turnstep.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@sss.pgh.pa.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@enterprisedb.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@enterprisedb.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@sss.pgh.pa.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@sss.pgh.pa.us> writes:

Gregory Stark <stark@enterprisedb.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@enterprisedb.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@sss.pgh.pa.us> writes:

Gregory Stark <stark@enterprisedb.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@enterprisedb.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@enterprisedb.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@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

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

David Fetter <david@fetter.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@gmail.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@sss.pgh.pa.us> writes:

"Brendan Jurd" <direvus@gmail.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