pgman wrote:
This patch uses current_schemas('true') to display only the schemas in
the current search path and implicit schemas.

It uses an array with =ANY(), which we already do in psql's describe.c
for groups. I also had to use :: for casting because that's the only
way to cast function parameters, I think.

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

Index: doc/src/sgml/ref/psql-ref.sgml
================================================== =================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.101
diff -c -c -r1.101 psql-ref.sgml
*** doc/src/sgml/ref/psql-ref.sgml 1 Dec 2003 22:21:54 -0000 1.101
--- doc/src/sgml/ref/psql-ref.sgml 21 Dec 2003 04:04:35 -0000
***************
*** 954,960 ****

<listitem>
<para>
! Lists all available schemas (namespaces). If <replaceable
class="parameter">pattern</replaceable> (a regular expression)
is specified, only schemas whose names match the pattern are listed.
</para>
--- 954,960 ----

<listitem>
<para>
! Lists all visible schemas (namespaces). If <replaceable
class="parameter">pattern</replaceable> (a regular expression)
is specified, only schemas whose names match the pattern are listed.
</para>
Index: src/bin/psql/describe.c
================================================== =================
RCS file: /cvsroot/pgsql-server/src/bin/psql/describe.c,v
retrieving revision 1.90
diff -c -c -r1.90 describe.c
*** src/bin/psql/describe.c 1 Dec 2003 22:21:54 -0000 1.90
--- src/bin/psql/describe.c 21 Dec 2003 04:04:37 -0000
***************
*** 1629,1639 ****
"SELECT n.nspname AS \"%s\",\n"
" u.usename AS \"%s\"\n"
"FROM pg_catalog.pg_namespace n LEFT JOIN pg_catalog.pg_user u\n"
! " ON n.nspowner=u.usesysid\n",
_("Name"),
_("Owner"));

! processNamePattern(&buf, pattern, false, false,
NULL, "n.nspname", NULL,
NULL);

--- 1629,1641 ----
"SELECT n.nspname AS \"%s\",\n"
" u.usename AS \"%s\"\n"
"FROM pg_catalog.pg_namespace n LEFT JOIN pg_catalog.pg_user u\n"
! " ON n.nspowner=u.usesysid,\n"
! " (SELECT current_schemas('t'::boolean)) AS curr_schemas(name)\n"
! "WHERE n.nspname = ANY(curr_schemas.name)\n",
_("Name"),
_("Owner"));

! processNamePattern(&buf, pattern, true, false,
NULL, "n.nspname", NULL,
NULL);



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Bruce Momjian <pgman (AT) candle (DOT) pha.pa.us> writes:
The more I look at this, the sillier it looks. It converts \dn into
an expensive substitute for "select current_schemas(true)". In
practical situations this will mean that \dn shows hardly anything of
interest.

I think the original complaint was misguided and we should not do
anything about it. IIRC the complaint amounted to "I have hundreds of
schemas and it annoys me that \dn shows them all". How is this
different from putting hundreds of tables into one schema and then being
annoyed because \dt shows them all? We have other mechanisms available
for making \dn selective (ie, you can use a name pattern). If \dn is
restricted to showing only schemas in your search path, it will become
useless.

regards, tom lane

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

Tom Lane wrote:
Agreed showing just search path and implicit schemas is pretty dumb. I
think the issue was that every backend with a temp table was showing up,
pretty much swamping the actual schemas he is using. The original
approach was to supress all temp schemas _except_ the ones already
visible, but was a hack using backend id. The patch could easily be
modified to use current_schemas to restrict temp table display if people
think it is a good idea.

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Bruce Momjian <pgman (AT) candle (DOT) pha.pa.us> writes:
Oh, okay. I would not object to suppressing pg_temp_NNN schemas from
the \dn display. That isn't what this patch does, however.

regards, tom lane

---------------------------(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)

Tom Lane wrote:
OK. I read the TODO and it says only:

* Have psql \dn show only visible schemas using current_schemas()

so that's what I did, but I think now I have to add a test so only
non-visible temp schemas are suppressed, and I think we have good logic
for doing this because they really didn't create those schemas themselves ---
the schemas are just an artifact of how we implement temp tables.
Having one is fine, but having tons that obscure user-created schemas is
probably bad.

I will do that and repost.

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Bruce Momjian <pgman (AT) candle (DOT) pha.pa.us> writes:
That TODO was your interpretation of the discussion; I'm not sure anyone
else bought into it.

You are complicating something that could be simple. Why not just
suppress schemas named 'pg_temp_XXX', period? I don't see any strong
reason to display them, whether they are your own backend's temp schema
or not. Arguably, the fact that temp tables are kept in a special
schema is an implementation detail that most people won't care about.
And there is no data that \dn can show that is really important for temp
schemas. The owner column is at best misleading...

regards, tom lane

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

Tom Lane wrote:
Also, how do we know something is a temp schema? Just the prefix
pg_temp_*?

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Bruce Momjian <pgman (AT) candle (DOT) pha.pa.us> writes:
Yeah. Remember that all schemas named pg_XXX are reserved for system
use. For the moment, testing for pg_temp_XXX is a bulletproof test,
and we can certainly adapt psql's test if we ever add schemas that might
conflict.

Looking at the present output of \dn, I wonder whether we should not
suppress the pg_toast schema as well. That could be done (at the
moment) by bouncing all schemas 'pg_t*' ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Tom Lane wrote:
Yea, probably. I think the "implementation artifact" logic is a good
approach. If someone wants to get into implementation details, they
should query pg_namespace.

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Because that's what I originally did and you shot it down as a bad
patch because you thought it wasn't in PostgreSQL's interest to filter
what we showed the user.

"What have you got against pg_temp? If we think \dn shouldn't show those
schemas, shouldn't it suppress *all* system schemas, including
pg_catalog and pg_toast? Maybe information_schema as well?"

I'm glad to see you've come around on this given temp structures show
up regardless of the temp schema (which is, as you point out, an
implementation detail that users need not concern themselves with).

Patch/thread:
http://archives.postgresql.org/pgsql...0/msg00613.php

-sc

--
Sean Chittenden

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org