Thread: Temporary tables and miscellaneous schemas

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

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

<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

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

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

Tom Lane wrote:
> 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.)

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

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

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

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

Bruce Momjian <pgman@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

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

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 <dave@fastcrypt.com>
fastcrypt
--
Dave Cramer <Dave@micro-automation.net>

Sean Chittenden <sean@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

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 <dave@fastcrypt.com>
fastcrypt

On Mon, Oct 27, 2003 at 16:58:50 -0500,
  Dave Cramer <dave@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.

Tom Lane wrote:
> Bruce Momjian <pgman@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
  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