Re: Temporary tables and miscellaneous schemas - Mailing list pgsql-general

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@chittenden.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@candle.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