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

Bruce Momjian wrote:
> Sean Chittenden wrote:
> > > I don't think that really answers my concern, since the sort of
> > > folks who are likely to get confused by not being able to see
> > > something that should be there are exactly the same ones who are not
> > > likely to have turned on a non-default "power user" setting.  If
> > > anything, adding such a setting is likely to increase confusion
> > > rather than decrease it, because people will get accustomed to
> > > differing results.
> >
> > Or overwhelmed by bits that they shouldn't be exposed to...
> >
> > > I'm not dead set on this, and will concede gracefully if there's a
> > > consensus that we should change \dn's behavior.  I'm just trying to
> > > make the point that it's a decision with pluses and minuses, not a
> > > no-brainer improvement.
> >
> > *nods* Though I do think that masking pg_temp_* would be useful as
> > I've never seen a need to look inside of a pg_temp_* schema.  Someone
> > running with -E would quickly pick up that pg_temp_* is filtered from
> > the results.
> >
> > I have a machine with over 1K persistent connections and over 1K
> > pg_temp_* entries... I've been running with the patch submitted
> > earlier and it cuts down on the visual noise/unnecessary info
> > considerably.  Switching between DBA mode and a data consumer with \P
> > sounds pretty appealing to me and would be something I'd be interested
> > in doing the leg work for.  Changing the prompt would probably be good
> > from a UI perspective and adding the necessary logic so that if the
> > connecting user had DBA privs, it'd run in a power user mode instead
> > of the normal data consumer mode.
>
> If you see a pg_temp_* for every connection, that is a little
> overwhelming.  pg_toast and stuff aren't really too bad.  Is there any
> way to access your local temp schema in a way that doesn't show the
> others?  Could we use backend_pid in the query and show them only their
> own?

I have created the following patch for 7.5.  It has \dn show only your
local pg_temp_* schema, and only if you own it --- there might be an old temp
schema around from an old backend.

This patch requires a new function pg_stat_backend_id which returns your
current slot id (not your pid) --- that would be separate addition.

--
  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
Index: src/bin/psql/describe.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/psql/describe.c,v
retrieving revision 1.86
diff -c -c -r1.86 describe.c
*** src/bin/psql/describe.c    17 Oct 2003 00:57:04 -0000    1.86
--- src/bin/psql/describe.c    26 Oct 2003 02:51:30 -0000
***************
*** 1584,1591 ****
                        "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,
--- 1584,1595 ----
                        "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"
!         "WHERE    n.spname NOT LIKE 'pg_temp_%' OR\n"
!         "        (n.spname LIKE 'pg_temp_' || CAST(pg_stat_backend_id() AS TEXT) AND\n"
!         "         u.usename = \"%s\")",
                        _("Name"),
+                       _("Owner"),
                        _("Owner"));

      processNamePattern(&buf, pattern, false, false,