Re: Temporary tables and miscellaneous schemas

Oh, that's not fair --- you gave us the solution to something you don't
agree with. ;-)

Anyway, I agree a separate admin mode can cause more confusion that it
solves.

I see a few goals here:

Prevent \dn from showing lots of lines for large installs
Show the local temp schema so people can query it

Is there a solution that doesn't supress all the schemas but the local
one?

How about if we add a UNION that does:

UNION
SELECT 'non-local temp schemas skipped', NULL

That would document that we are skipping them, and even give them an
entry in the output:

List of schemas
Name | Owner
--------------------+----------
information_schema | postgres
pg_catalog | postgres
pg_temp_2 | postgres
pg_toast | postgres
public | postgres
{other pg_temp_*} | postgres
(7 rows)

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

Tom Lane wrote:
> Sean Chittenden <sean(at)chittenden(dot)org> writes:
> > Um, I forget whether or not this was given any credence or anyone
> > weighed in on it, but what about having two modes for psql? An admin
> > mode which hides nothing and is the default for superuser connections,
> > and a user mode which is the default for non-DBA connections.
>
> I thought that would be likely to create more confusion than it solves.
>
> To take just one problem, the newbies who could use the "friendly user"
> mode are very likely the same ones who do all their work as postgres,
> because it hasn't occurred to them to create any unprivileged users.
> They won't get the benefit of it if we make it act as you suggest.
>
>
> BTW, if I lose this argument, there *is* a workable way to get the
> behavior Bruce wants: use current_schemas() to detect which temp schema
> is in your search path.
>
> regression=# select nspname from pg_namespace;
> nspname
> --------------------
> pg_temp_2
> pg_toast
> pg_temp_1
> pg_catalog
> public
> information_schema
> (6 rows)
>
> regression=# select nspname from pg_namespace where nspname not like
> regression-# 'pg\\_temp\\_%' or nspname = any (current_schemas(true));
> nspname
> --------------------
> pg_toast
> pg_catalog
> public
> information_schema
> (4 rows)
>
> regression=# create temp table foo(f1 int);
> CREATE TABLE
> regression=# select nspname from pg_namespace where nspname not like
> regression-# 'pg\\_temp\\_%' or nspname = any (current_schemas(true));
> nspname
> --------------------
> pg_temp_2
> pg_toast
> pg_catalog
> public
> information_schema
> (5 rows)
>
>
> regards, tom lane
>

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