psql's \dn versus temp schemas

psql's \dn command hides pg_temp_nn schemas, except for the current
backend's own temp schema (if any). However, when we added separate
pg_toast_temp_nn schemas for TOAST tables, \dn wasn't taught about that,
leading to such odd-looking output as this:

regression=# \dn
List of schemas
Name | Owner
--------------------+----------
information_schema | postgres
pg_catalog | postgres
pg_temp_2 | postgres
pg_toast | postgres
pg_toast_temp_1 | postgres
pg_toast_temp_2 | postgres
pg_toast_temp_3 | postgres
pg_toast_temp_4 | postgres
public | postgres
testxmlschema | postgres
(10 rows)

regression=#

This is at least inconsistent and at worst wildly misleading. ISTM
we ought to adopt some combination of the following ideas:

1. Don't show pg_toast_temp_nn schemas ever. Maybe hide pg_toast too
for consistency.

2. Show only the current backend's pg_toast_temp_nn schema. (Note:
I don't see any very easy way to implement that :-(; psql doesn't
have easy access to the backend's slot number. The way that it
identifies the pg_temp_nn schema is a hack that won't scale.)

3. Don't show either pg_temp_nn or pg_toast_temp_nn schemas, not even
for the current backend.

4. Forget about hiding these schemas at all.

With any of 1-3 we could also consider adding a rule that \dn+
doesn't hide them.

Thoughts?

regards, tom lane

On Sat, Sep 18, 2010 at 3:11 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> This is at least inconsistent and at worst wildly misleading.  ISTM
> we ought to adopt some combination of the following ideas:

I vote for this combination:

> 3. Don't show either pg_temp_nn or pg_toast_temp_nn schemas, not even
> for the current backend.

and

> With any of 1-3 we could also consider adding a rule that \dn+
> doesn't hide them.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

On Sat, Sep 18, 2010 at 6:35 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Sat, Sep 18, 2010 at 3:11 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> This is at least inconsistent and at worst wildly misleading.  ISTM
>> we ought to adopt some combination of the following ideas:
>
> I vote for this combination:
>
>> 3. Don't show either pg_temp_nn or pg_toast_temp_nn schemas, not even
>> for the current backend.
>
> and
>
>> With any of 1-3 we could also consider adding a rule that \dn+
>> doesn't hide them.

Or perhaps another option would be to make \dnS display these. Not
sure whether I like that or not.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Sat, Sep 18, 2010 at 6:35 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Sat, Sep 18, 2010 at 3:11 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> This is at least inconsistent and at worst wildly misleading. ISTM
>>> we ought to adopt some combination of the following ideas:

>> I vote for this combination:
>>
>>> 3. Don't show either pg_temp_nn or pg_toast_temp_nn schemas, not even
>>> for the current backend.
>>
>> and
>>
>>> With any of 1-3 we could also consider adding a rule that \dn+
>>> doesn't hide them.

This approach makes sense to me too; I'd be inclined to hide pg_toast as
well under the same rules. In all of these cases, the schemas are not
meant to be referred to explicitly. I think that the original
motivation for letting \dn show the backend's own pg_temp_nn schema
was that there were cases where you needed to refer to it by name.
Since then, we invented the "pg_temp" alias mechanism, which seems to
remove most of the need for that.

> Or perhaps another option would be to make \dnS display these. Not
> sure whether I like that or not.

Hmm. If we had a \dnS option, what I would sorta expect it to do is
show the "system" schemas pg_catalog and information_schema. The toast
and temp schemas seem like a different category somehow. On the other
hand, if we did it like this, then the S and + modifiers would be
orthogonal which is a nice property.

Anyone else have an opinion?

regards, tom lane

On sön, 2010-09-19 at 13:51 -0400, Tom Lane wrote:
> Hmm. If we had a \dnS option, what I would sorta expect it to do is
> show the "system" schemas pg_catalog and information_schema. The
> toast
> and temp schemas seem like a different category somehow. On the other
> hand, if we did it like this, then the S and + modifiers would be
> orthogonal which is a nice property.

Well, normally the + option shows more columns and the S option shows
more rows. Showing more "internal" objects with + might be a bit
confusing.

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> On sn, 2010-09-19 at 13:51 -0400, Tom Lane wrote:
>> Hmm. If we had a \dnS option, what I would sorta expect it to do is
>> show the "system" schemas pg_catalog and information_schema. The
>> toast
>> and temp schemas seem like a different category somehow. On the other
>> hand, if we did it like this, then the S and + modifiers would be
>> orthogonal which is a nice property.

> Well, normally the + option shows more columns and the S option shows
> more rows. Showing more "internal" objects with + might be a bit
> confusing.

Okay, it seems to be the consensus that \dn should have orthogonal
S and + options (S = show system stuff, + = show more columns).

How do we want to define "system" exactly? My original proposal was
for bare \dn to hide the temp and toast schemas. If we consider that
what it's hiding is "system" schemas then there's some merit to the
idea that it should hide pg_catalog and information_schema too.
In that case, in a fresh database you would *only* see "public".
I'm not sure that I like this though. Comments?

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> In that case, in a fresh database you would *only* see "public".
> I'm not sure that I like this though. Comments?

I sure like it! I can't count how many time I would have wanted a
"cleaned out" \dn output.

Regards,
--
dim

On fre, 2010-09-24 at 14:42 -0400, Tom Lane wrote:
> How do we want to define "system" exactly? My original proposal was
> for bare \dn to hide the temp and toast schemas. If we consider that
> what it's hiding is "system" schemas then there's some merit to the
> idea that it should hide pg_catalog and information_schema too.
> In that case, in a fresh database you would *only* see "public".
> I'm not sure that I like this though. Comments?

I think that is sensible.