Re: psql and schemas

psql's slash commands for schemas seem a little weird to me. For
example:

neilc=# \d nonexistent
Did not find any relation named "nonexistent".
neilc=# \dt nonexistent
No matching relations found.
neilc=# \dn nonexistent
List of schemas
Name | Owner
------+-------
(0 rows)

-- Is there a good reason for this inconsistency?

neilc=# create schema foo_schema;
CREATE SCHEMA
neilc=# \dn foo_schema
List of schemas
Name | Owner
------------+-------
foo_schema | neilc
(1 row)
neilc=# \dn foo_schema.
List of schemas
Name | Owner
--------------------+-------
foo_schema | neilc
information_schema | neilc
pg_catalog | neilc
pg_toast | neilc
public | neilc
(5 rows)

-- Why? (The same applies to "\dn nonexistent.")

neilc=# \d
No relations found.
neilc=# \d foo_schema.*
Did not find any relation named "foo_schema.*".

-- Why the difference in behavior? In any case, the error message is
confusing -- it suggests psql was looking for a relation with the name
"foo_schema.*", where it obviously was not:

neilc=# create table "foo_schema.*" (a int, b int);
CREATE TABLE
neilc=# \d foo_schema.*
Did not find any relation named "foo_schema.*".
neilc=# \d
List of relations
Schema | Name | Type | Owner
--------+--------------+-------+-------
public | foo_schema.* | table | neilc
(1 row)

-- When you do \d schema.*, you get the definitions of _all_ the objects
in the schema. I can see why we support this, although I can't see it
being used very often. On the other hand, I think a much more common
case would be trying to get a list of all the objects in a schema -- is
there any way to do that? \dt schema.* lists the tables in a schema, for
example, but not the other types of objects (in a similar fashion to how
"\d" displays the objects in the search path).

That's all for now :-)

-Neil

Neil Conway <neilc(at)samurai(dot)com> writes:
> psql's slash commands for schemas seem a little weird to me.

The behaviors you mention were written at different times by different
people, and mostly have nothing to do with schemas per se. I agree that
some more consistency would probably be good. Do you have a specific
proposal?

regards, tom lane

On Sun, 2004-10-31 at 05:32, Tom Lane wrote:
> The behaviors you mention were written at different times by different
> people, and mostly have nothing to do with schemas per se. I agree that
> some more consistency would probably be good. Do you have a specific
> proposal?

Sure, I just thought I'd check if there was method to psql's madness
before suggesting changes. Proposed new behavior:

\dn non_existent_schema
===> "No such schema ..."
(previously: empty list of schemas)

\d non_existent_schema.*
===> "No such schema ..."
(previously: Did not find any relation named "non_existent_schema.*".)

I'm not sure how we should handle "\dn schema_name." (notice the period;
assuming a schema with that name exists). The current behavior of
listing all schemas is obviously wrong, but I'm not sure what the right
behavior is. Perhaps we should reject the command?

I think there needs to be a way to list all the objects in a schema.
What do people think about making "\dn schema" behave like "\dn+ schema"
currently does, and changing "\dn+ schema" to list the objects in the
specified schema, like "\d" currently does for the objects in the search
path?

(BTW, I think a useful way to assess the usability of psql's schema
slash commands is trying to use them to explore the information_schema.
Perhaps I'm missing something, but with the current psql it seems almost
impossible to do that effectively without adding information_schema to
the search path.)

-Neil

Neil Conway <neilc(at)samurai(dot)com> writes:
> I'm not sure how we should handle "\dn schema_name." (notice the period;
> assuming a schema with that name exists). The current behavior of
> listing all schemas is obviously wrong, but I'm not sure what the right
> behavior is. Perhaps we should reject the command?

Seems reasonable to me. There shouldn't ever be a dot in the argument
of \dn.

> I think there needs to be a way to list all the objects in a schema.

This doesn't seem especially helpful to me, because you'd have to fit a
bunch of different object types into a one-size-fits-all output, which
would end up fitting nothing very well. You can get the effect with
\d schema_name.*
\df schema_name.*
\do schema_name.*
\da schema_name.*
etc
It's not clear to me that we need do more, especially since the user
probably knows what kinds of objects he's created, and can skip
unnecessary steps.

regards, tom lane

On Tue, 2004-11-02 at 01:44, Tom Lane wrote:
> Neil Conway <neilc(at)samurai(dot)com> writes:
> > I think there needs to be a way to list all the objects in a schema.
>
> This doesn't seem especially helpful to me, because you'd have to fit a
> bunch of different object types into a one-size-fits-all output, which
> would end up fitting nothing very well.

The same comment applies to "\d", but I think that is still a useful
command. Note that we wouldn't need to list _everything_ in a schema for
this to be useful, in the same way that "\d" doesn't list everything in
the search path.

> It's not clear to me that we need do more, especially since the user
> probably knows what kinds of objects he's created

Among other things, psql is a tool for exploring the content of a
database; assuming that the user already knows what is in the database
doesn't strike me as wise. I can think of plenty of scenarios in which
someone using psql would like to get a quick summary of the content of a
schema they know nothing about.

But in any case I'll leave this for later.

-Neil

Is there a TODO here? Or a few?

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

Neil Conway wrote:
> On Sun, 2004-10-31 at 05:32, Tom Lane wrote:
> > The behaviors you mention were written at different times by different
> > people, and mostly have nothing to do with schemas per se. I agree that
> > some more consistency would probably be good. Do you have a specific
> > proposal?
>
> Sure, I just thought I'd check if there was method to psql's madness
> before suggesting changes. Proposed new behavior:
>
> \dn non_existent_schema
> ===> "No such schema ..."
> (previously: empty list of schemas)
>
> \d non_existent_schema.*
> ===> "No such schema ..."
> (previously: Did not find any relation named "non_existent_schema.*".)
>
> I'm not sure how we should handle "\dn schema_name." (notice the period;
> assuming a schema with that name exists). The current behavior of
> listing all schemas is obviously wrong, but I'm not sure what the right
> behavior is. Perhaps we should reject the command?
>
> I think there needs to be a way to list all the objects in a schema.
> What do people think about making "\dn schema" behave like "\dn+ schema"
> currently does, and changing "\dn+ schema" to list the objects in the
> specified schema, like "\d" currently does for the objects in the search
> path?
>
> (BTW, I think a useful way to assess the usability of psql's schema
> slash commands is trying to use them to explore the information_schema.
> Perhaps I'm missing something, but with the current psql it seems almost
> impossible to do that effectively without adding information_schema to
> the search path.)
>
> -Neil
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Sat, 2004-11-27 at 23:11 -0500, Bruce Momjian wrote:
> Is there a TODO here? Or a few?

Sure: you could add a TODO item like "Improve psql schema behavior", and
assign it to me. I'll send in a patch that implements the behavior I
proposed for 8.1

-Neil

Neil Conway wrote:
> On Sat, 2004-11-27 at 23:11 -0500, Bruce Momjian wrote:
> > Is there a TODO here? Or a few?
>
> Sure: you could add a TODO item like "Improve psql schema behavior", and
> assign it to me. I'll send in a patch that implements the behavior I
> proposed for 8.1

Added to TODO:

* Fix psql's display of schema information (Neil)

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073