Temporary tables and miscellaneous schemas

<btober@seaworthysys.com> writes:

What's up with these miscellaneous schemas? Are they in fact related to
the creation of temporary tables? Should they disappear when the session
closes, as should the temporary table? If they continue persisting after
the session closes, how do I get rid of them?

They're implementation details, yes, no, and you don't.

regards, tom lane

<btober@seaworthysys.com> writes:

To follow-up then, if the temp schemas do not disappear, then over time
what happens (as temp tables are instantiated during normal application
usage), does the database end up with an ever-increasing number of these
temp schemas?

No, you will never have more than max_connections of them.

regards, tom lane

<btober@seaworthysys.com> writes:

What's up with these miscellaneous schemas? Are they in fact related
to the creation of temporary tables? Should they disappear when the
session closes, as should the temporary table? If they continue
persisting after the session closes, how do I get rid of them?

They're implementation details, yes, no, and you don't.

Thanks. Maybe my thinking in regards to the usefulness of temporary
tables needs to be adjusted. I had been formulating a plan to make use of
a temporary table that would be created at run time under certain
conditions depending on selections made by the end-user of a database
application.

To follow-up then, if the temp schemas do not disappear, then over time
what happens (as temp tables are instantiated during normal application
usage), does the database end up with an ever-increasing number of these
temp schemas? It would seem to me that that is not a good thing to have
happening on a permanent, continuing basis.

~Berend Tober

To follow-up then, if the temp schemas do not disappear, then over
time what happens (as temp tables are instantiated during normal
application usage), does the database end up with an
ever-increasing number of these temp schemas?

No, you will never have more than max_connections of them.

This implementation detail really annoys me when using psql. The
attached patch fixes \dn to not show pg_temp_*. Any chance someone
could apply this? -sc

--
Sean Chittenden

Sean Chittenden <sean@chittenden.org> writes:

This implementation detail really annoys me when using psql. The
attached patch fixes \dn to not show pg_temp_*. Any chance someone
could apply this? -sc

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?

regards, tom lane

This implementation detail really annoys me when using psql. The
attached patch fixes \dn to not show pg_temp_*. Any chance
someone could apply this? -sc

What have you got against pg_temp?

What value does it provide to have it shown in a \dn listing? Temp
tables are globally visible across schemas and there's no point to
looking inside of a temp schema that isn't a proc's temp schema.

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?

There is only one pg_catalog, pg_toast, and information_schema schema.
Those schemas yield useful information that can only be fetched via
their respective schemas. pg_temp_* doesn't meet this criteria since
pg_temp_*'s tables are visible outside of their schema. Why wouldn't
you want to hide pg_temp_*?

-sc

--
Sean Chittenden

Sean Chittenden <sean@chittenden.org> writes:

Why wouldn't you want to hide pg_temp_*?

So you could see your own temp tables, for instance.

I dislike putting random restrictions on what the \d displays will show.
We have done this in the past (eg, \df doesn't show things it thinks are
I/O functions) and by and large it's been a mistake; I think it's
created more confusion than it's prevented.

I certainly don't think there is any justification for exposing pg_toast
if we are going to hide other "system" schemas. There is no normal
reason for needing to access toast tables directly, and it's only an
implementation artifact that they have names at all.

regards, tom lane

Why wouldn't you want to hide pg_temp_*?

So you could see your own temp tables, for instance.

I dislike putting random restrictions on what the \d displays will
show. We have done this in the past (eg, \df doesn't show things it
thinks are I/O functions) and by and large it's been a mistake; I
think it's created more confusion than it's prevented.

I certainly don't think there is any justification for exposing
pg_toast if we are going to hide other "system" schemas. There is
no normal reason for needing to access toast tables directly, and
it's only an implementation artifact that they have names at all.

Hrm... psql's unfortunately an SQL interface to PostgreSQL and an
administration tool. What would you say to adding a -P switch (power
user) to psql that'd disable any information hiding: the default would
be to hide non-critical areas including pg_catalog, pg_toast,
template1, and template0. \set POWERUSER would also work to toggle
this.. or just have \P toggle this mode. Thoughts?

-sc

--
Sean Chittenden

Sean Chittenden <sean@chittenden.org> writes:

I dislike putting random restrictions on what the \d displays will
show. We have done this in the past (eg, \df doesn't show things it
thinks are I/O functions) and by and large it's been a mistake; I
think it's created more confusion than it's prevented.

Hrm... psql's unfortunately an SQL interface to PostgreSQL and an
administration tool. What would you say to adding a -P switch (power
user) to psql that'd disable any information hiding: the default would
be to hide non-critical areas including pg_catalog, pg_toast,
template1, and template0. \set POWERUSER would also work to toggle
this.. or just have \P toggle this mode. Thoughts?

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.

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.

Anyone else out there have an opinion?

regards, tom lane

I dislike putting random restrictions on what the \d displays
will show. We have done this in the past (eg, \df doesn't show
things it thinks are I/O functions) and by and large it's been a
mistake; I think it's created more confusion than it's prevented.

Hrm... psql's unfortunately an SQL interface to PostgreSQL and an
administration tool. What would you say to adding a -P switch (power
user) to psql that'd disable any information hiding: the default would
be to hide non-critical areas including pg_catalog, pg_toast,
template1, and template0. \set POWERUSER would also work to toggle
this.. or just have \P toggle this mode. Thoughts?

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.

-sc

--
Sean Chittenden

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?

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

On Mon, Oct 13, 2003 at 04:33:22PM -0400, Tom Lane 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

Hmm. What about adding a "suppress" setting or something like that?
Then people could alias psql to psql --suppress if it made their
lives easier? (FWIW, I agree with Tom. Suppressing stuff that \d
shows just makes people have to resort to grovelling through the
system tables themselves, after struggling with trying to figure out
why they couldn't see, oh, the I/O function. Ask me how I know for a
mini-rant about consistency in interfaces.)

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Afilias Canada                        Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

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

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.

If by slot, you mean connection ID, then this sounds like a good
compromise/patch to me. -sc

--
Sean Chittenden

Sean Chittenden wrote:

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.

If by slot, you mean connection ID, then this sounds like a good
compromise/patch to me. -sc

Yep, that's what it is.

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

Bruce Momjian <pgman@candle.pha.pa.us> writes:

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 will certainly not work, since you don't own your pg_temp_* schema
(the bootstrap UID does). I disagree with the goal anyway ...

regards, tom lane

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

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 will certainly not work, since you don't own your pg_temp_* schema
(the bootstrap UID does). I disagree with the goal anyway ...

OK, others liked the goal of showing only your local schema --- what is
your proposal?

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

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom Lane wrote:

This will certainly not work, since you don't own your pg_temp_* schema
(the bootstrap UID does). I disagree with the goal anyway ...

OK, others liked the goal of showing only your local schema --- what is
your proposal?

My proposal is to do nothing ;-).

If you want to suppress *all* pg_temp_ schemas from the \dn listing,
that would be defensible maybe. I'd be inclined to say that pg_toast
should be hidden as well if that approach is taken, because then you are
basically saying that \dn is not the truth but only the stuff we think
you should be interested in. (This is why I don't agree with it.)

regards, tom lane

This will certainly not work, since you don't own your pg_temp_*
schema (the bootstrap UID does). I disagree with the goal anyway
...

OK, others liked the goal of showing only your local schema ---
what is your proposal?

My proposal is to do nothing ;-).

If you want to suppress *all* pg_temp_ schemas from the \dn listing,
that would be defensible maybe. I'd be inclined to say that
pg_toast should be hidden as well if that approach is taken, because
then you are basically saying that \dn is not the truth but only the
stuff we think you should be interested in. (This is why I don't
agree with it.)

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. Then we
could pretty easily rationalize hiding various schemas as they may or
may not be relevant. In the case where a normal user would want their
\command to show admin tables, schemas, etc., they could \set
ADMIN_MODE or toggle it on/off with a \command like \P.

I've got the psql foo to pull this off pretty easily, but don't
recall a thumbsup/down on the idea. -sc

--
Sean Chittenden