Temporary tables and miscellaneous schemas

[btober]

Whenever I create a temporary table, with something like

CREATE TEMPORARY TABLE temptable1 AS SELECT * FROM paid.ad_hoc_query;

New schemas appear, with names like "pg_temp_1". I guess the appearance
of these schemas with "temp" in the name indicates that they are
"temporary" schemas and related to the temporary table creation, but the
schemas persist even after the end of the session in which the temporary
table was created.

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?

~Berend Tober


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

<bt****@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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[btober]

<bt****@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


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

<bt****@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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Sean Chittenden]

> > 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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

Sean Chittenden <se**@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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Sean Chittenden]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

Sean Chittenden <se**@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Sean Chittenden]

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

Sean Chittenden <se**@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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Sean Chittenden]

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Bruce Momjian]

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
pg***@candle.pha.pa.us | [phone]
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Andrew Sullivan]

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
<an****@libertyrms.info> M2P 2A8
[phone] x110
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Andrew Sullivan]

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
<an****@libertyrms.info> M2P 2A8
[phone] x110
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Bruce Momjian]

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
pg***@candle.pha.pa.us | [phone]
+ 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,
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Sean Chittenden]

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Bruce Momjian]

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
pg***@candle.pha.pa.us | [phone]
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Tom Lane]

Bruce Momjian <pg***@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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Bruce Momjian]

Tom Lane wrote:

Bruce Momjian <pg***@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
pg***@candle.pha.pa.us | [phone]
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

Bruce Momjian <pg***@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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Sean Chittenden]

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Bruce Momjian]

Tom Lane wrote:

Bruce Momjian <pg***@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.)

The main problem is that someone with 1k connection is seeing 1k
pg_temp_* schemas lists, which certainly isn't good.

Maybe we could do a UNION and add a "pg_temp_*" line to stand for all
pg_temp_ schemas. Another idea would be to print a message at the
bottom saying other temp schemas were supressed. By showing the temp
schema name, you can see all your temp tables:

test=> create temp table x(y int);
CREATE TABLE
test=> \dn
List of schemas
Name | Owner
--------------------+----------
information_schema | postgres
pg_catalog | postgres
pg_temp_1 | postgres
pg_temp_2 | postgres
pg_toast | postgres
public | postgres
(6 rows)

test=> \d pg_temp_1.*
Table "pg_temp_1.x"
Column | Type | Modifiers
--------+---------+-----------
y | integer |

This seems like a good reason for the patch so people can see their own
schemas --- I don't think people are using \dn as an authorative result
--- they can always select from pg_namespace.

--
Bruce Momjian | http://candle.pha.pa.us
pg***@candle.pha.pa.us | [phone]
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Bruce Momjian]

Sean Chittenden wrote:

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

I would like to see a big reason before making psql behave differently
for different people/modes.

--
Bruce Momjian | http://candle.pha.pa.us
pg***@candle.pha.pa.us | [phone]
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

Sean Chittenden <se**@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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Bruce Momjian]

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 <se**@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
pg***@candle.pha.pa.us | [phone]
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

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

How about if we add a UNION that does:
UNION
SELECT 'non-local temp schemas skipped', NULL
I think showing that would only be appropriate if we actually *did* skip
some. Finding that out would complicate the query unduly IMHO.
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

If those are agreed to be the goals then we end up with your original
solution (or a working implementation of same anyway).

I'd like to see some input from other people about what they want...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Sean Chittenden]

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

Hrm, well, two flaws with that argument being:

1) Users who (ab)use DBA accounts aren't likely the ones with
gazillions of pg_temp_* tables and probably don't even make use of
temp tables or care about pg_toast. No harm, no foul, as the
feature isn't likely used.

2) Queries that are written by a DBA and given to a user will still
work when executed by the user, so the confusion is limited to a
\command not showing the same results that a DBA sees.

Seeing extra info if your prompt is '#' and not '%' shouldn't surprise
anyone. Few complain about tab completion in shells not listing
programs that aren't readable by the current user.

eg:

% /usr/local/bin/root_only_cmd[TAB]
*system beeps, root_only_cmd_here isn't executable by $USER*
# /usr/local/bin/root_only_cmd[TAB]
# /usr/local/bin/root_only_cmd_here
-sc

--
Sean Chittenden

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Dave Cramer]

I have a challenge to be able to grant all to the database, and then
have subsequent tables accessible by all users.

It seems to me that this is how a database should work. I do realize
that postgres doesn't do this now. Is there a way around this? Using
rules or some other mechanism?

Dave
--
Dave Cramer <da**@fastcrypt.com>
fastcrypt
--
Dave Cramer <Da**@micro-automation.net>
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

Sean Chittenden <se**@chittenden.org> writes:

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.

Hrm, well, two flaws with that argument being: 1) Users who (ab)use DBA accounts aren't likely the ones with
gazillions of pg_temp_* tables and probably don't even make use of
temp tables or care about pg_toast. No harm, no foul, as the
feature isn't likely used.

Perhaps, but you were arguing (I thought) for instituting a bunch of
differences in behavior between user and DBA modes, not only this one.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Bruno Wolff III]

On Mon, Oct 27, 2003 at 16:58:50 -0500,
Dave Cramer <da**@fastcrypt.com> wrote:

I have a challenge to be able to grant all to the database, and then
have subsequent tables accessible by all users.
Granting access to a database does specifically what the documentation
says it does, which does affect the default access rights for newly
created objects.
It seems to me that this is how a database should work. I do realize
that postgres doesn't do this now. Is there a way around this? Using
rules or some other mechanism?

Currently there really isn't a way to do this. You could run a cron script
that sets protections for tables on a regular schedule.

What it seems you really want is a per user or per database value that
specifies a default access mode for newly created objects roughly
similar to umask on Unix systems.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Bruce Momjian]

Tom Lane wrote:

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

How about if we add a UNION that does:
UNION
SELECT 'non-local temp schemas skipped', NULL

I think showing that would only be appropriate if we actually *did* skip
some. Finding that out would complicate the query unduly IMHO.

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

If those are agreed to be the goals then we end up with your original
solution (or a working implementation of same anyway).

I'd like to see some input from other people about what they want...

I have added this to the TODO list:

* Have psql \dn show only visible schemas using current_schemas()

I know there was talk of showing all schemas only in admin mode, but I
don't think we want to implement different behavior until we have a more
practical reason to have such a mode distiction. Of course, \dn will
have to be documented that is supresses non-visible schemas, and admins
can always do a select from pg_namespace.

--
Bruce Momjian | http://candle.pha.pa.us
pg***@candle.pha.pa.us | [phone]
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org