Re: Temporary tables and miscellaneous schemas

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