Thread: Re: [GENERAL] Temporary tables and miscellaneous schemas
pgman wrote:
> 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.
This patch uses current_schemas('true') to display only the schemas in
the current search path and implicit schemas.
It uses an array with =ANY(), which we already do in psql's describe.c
for groups. I also had to use :: for casting because that's the only
way to cast function parameters, I think.
--
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: doc/src/sgml/ref/psql-ref.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.101
diff -c -c -r1.101 psql-ref.sgml
*** doc/src/sgml/ref/psql-ref.sgml 1 Dec 2003 22:21:54 -0000 1.101
--- doc/src/sgml/ref/psql-ref.sgml 21 Dec 2003 04:04:35 -0000
***************
*** 954,960 ****
<listitem>
<para>
! Lists all available schemas (namespaces). If <replaceable
class="parameter">pattern</replaceable> (a regular expression)
is specified, only schemas whose names match the pattern are listed.
</para>
--- 954,960 ----
<listitem>
<para>
! Lists all visible schemas (namespaces). If <replaceable
class="parameter">pattern</replaceable> (a regular expression)
is specified, only schemas whose names match the pattern are listed.
</para>
Index: src/bin/psql/describe.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/psql/describe.c,v
retrieving revision 1.90
diff -c -c -r1.90 describe.c
*** src/bin/psql/describe.c 1 Dec 2003 22:21:54 -0000 1.90
--- src/bin/psql/describe.c 21 Dec 2003 04:04:37 -0000
***************
*** 1629,1639 ****
"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,
NULL, "n.nspname", NULL,
NULL);
--- 1629,1641 ----
"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"
! " (SELECT current_schemas('t'::boolean)) AS curr_schemas(name)\n"
! "WHERE n.nspname = ANY(curr_schemas.name)\n",
_("Name"),
_("Owner"));
! processNamePattern(&buf, pattern, true, false,
NULL, "n.nspname", NULL,
NULL);
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> This patch uses current_schemas('true') to display only the schemas in
> the current search path and implicit schemas.
The more I look at this, the sillier it looks. It converts \dn into
an expensive substitute for "select current_schemas(true)". In
practical situations this will mean that \dn shows hardly anything of
interest.
I think the original complaint was misguided and we should not do
anything about it. IIRC the complaint amounted to "I have hundreds of
schemas and it annoys me that \dn shows them all". How is this
different from putting hundreds of tables into one schema and then being
annoyed because \dt shows them all? We have other mechanisms available
for making \dn selective (ie, you can use a name pattern). If \dn is
restricted to showing only schemas in your search path, it will become
useless.
regards, tom lane
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > This patch uses current_schemas('true') to display only the schemas in
> > the current search path and implicit schemas.
>
> The more I look at this, the sillier it looks. It converts \dn into
> an expensive substitute for "select current_schemas(true)". In
> practical situations this will mean that \dn shows hardly anything of
> interest.
>
> I think the original complaint was misguided and we should not do
> anything about it. IIRC the complaint amounted to "I have hundreds of
> schemas and it annoys me that \dn shows them all". How is this
> different from putting hundreds of tables into one schema and then being
> annoyed because \dt shows them all? We have other mechanisms available
> for making \dn selective (ie, you can use a name pattern). If \dn is
> restricted to showing only schemas in your search path, it will become
> useless.
Agreed showing just search path and implicit schemas is pretty dumb. I
think the issue was that every backend with a temp table was showing up,
pretty much swamping the actual schemas he is using. The original
approach was to supress all temp schemas _except_ the ones already
visible, but was a hack using backend id. The patch could easily be
modified to use current_schemas to restrict temp table display if people
think it is a good idea.
--
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:
> Agreed showing just search path and implicit schemas is pretty dumb. I
> think the issue was that every backend with a temp table was showing up,
> pretty much swamping the actual schemas he is using.
Oh, okay. I would not object to suppressing pg_temp_NNN schemas from
the \dn display. That isn't what this patch does, however.
regards, tom lane
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Agreed showing just search path and implicit schemas is pretty dumb. I
> > think the issue was that every backend with a temp table was showing up,
> > pretty much swamping the actual schemas he is using.
>
> Oh, okay. I would not object to suppressing pg_temp_NNN schemas from
> the \dn display. That isn't what this patch does, however.
OK. I read the TODO and it says only:
* Have psql \dn show only visible schemas using current_schemas()
so that's what I did, but I think now I have to add a test so only
non-visible temp schemas are suppressed, and I think we have good logic
for doing this because they really didn't create those schemas themselves ---
the schemas are just an artifact of how we implement temp tables.
Having one is fine, but having tons that obscure user-created schemas is
probably bad.
I will do that and repost.
--
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:
>> Oh, okay. I would not object to suppressing pg_temp_NNN schemas from
>> the \dn display. That isn't what this patch does, however.
> OK. I read the TODO and it says only:
> * Have psql \dn show only visible schemas using current_schemas()
That TODO was your interpretation of the discussion; I'm not sure anyone
else bought into it.
> so that's what I did, but I think now I have to add a test so only
> non-visible temp schemas are suppressed,
You are complicating something that could be simple. Why not just
suppress schemas named 'pg_temp_XXX', period? I don't see any strong
reason to display them, whether they are your own backend's temp schema
or not. Arguably, the fact that temp tables are kept in a special
schema is an implementation detail that most people won't care about.
And there is no data that \dn can show that is really important for temp
schemas. The owner column is at best misleading...
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Tom Lane wrote: > >> Oh, okay. I would not object to suppressing pg_temp_NNN schemas from > >> the \dn display. That isn't what this patch does, however. > > > OK. I read the TODO and it says only: > > * Have psql \dn show only visible schemas using current_schemas() > > That TODO was your interpretation of the discussion; I'm not sure anyone > else bought into it. > > > so that's what I did, but I think now I have to add a test so only > > non-visible temp schemas are suppressed, > > You are complicating something that could be simple. Why not just > suppress schemas named 'pg_temp_XXX', period? I don't see any strong > reason to display them, whether they are your own backend's temp schema > or not. Arguably, the fact that temp tables are kept in a special > schema is an implementation detail that most people won't care about. > And there is no data that \dn can show that is really important for temp > schemas. The owner column is at best misleading... Also, how do we know something is a temp schema? Just the prefix pg_temp_*? -- 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:
> Also, how do we know something is a temp schema? Just the prefix
> pg_temp_*?
Yeah. Remember that all schemas named pg_XXX are reserved for system
use. For the moment, testing for pg_temp_XXX is a bulletproof test,
and we can certainly adapt psql's test if we ever add schemas that might
conflict.
Looking at the present output of \dn, I wonder whether we should not
suppress the pg_toast schema as well. That could be done (at the
moment) by bouncing all schemas 'pg_t*' ...
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Also, how do we know something is a temp schema? Just the prefix > > pg_temp_*? > > Yeah. Remember that all schemas named pg_XXX are reserved for system > use. For the moment, testing for pg_temp_XXX is a bulletproof test, > and we can certainly adapt psql's test if we ever add schemas that might > conflict. > > Looking at the present output of \dn, I wonder whether we should not > suppress the pg_toast schema as well. That could be done (at the > moment) by bouncing all schemas 'pg_t*' ... Yea, probably. I think the "implementation artifact" logic is a good approach. If someone wants to get into implementation details, they should query 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
> > so that's what I did, but I think now I have to add a test so only > > non-visible temp schemas are suppressed, > > You are complicating something that could be simple. Why not just > suppress schemas named 'pg_temp_XXX', period? Because that's what I originally did and you shot it down as a bad patch because you thought it wasn't in PostgreSQL's interest to filter what we showed the user. "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?" I'm glad to see you've come around on this given temp structures show up regardless of the temp schema (which is, as you point out, an implementation detail that users need not concern themselves with). Patch/thread: http://archives.postgresql.org/pgsql-general/2003-10/msg00613.php -sc -- Sean Chittenden
Sean Chittenden <sean@chittenden.org> writes:
> Because that's what I originally did and you shot it down as a bad
> patch because you thought it wasn't in PostgreSQL's interest to filter
> what we showed the user.
I'm still unconvinced on that, actually ... but it beats the heck out of
filtering everything not in your search path ...
regards, tom lane
> > Because that's what I originally did and you shot it down as a bad
> > patch because you thought it wasn't in PostgreSQL's interest to filter
> > what we showed the user.
>
> I'm still unconvinced on that, actually ... but it beats the heck out of
> filtering everything not in your search path ...
Well, for the sake of clarifying your opinion, would you be in favor
of a set of rules for the information_schema.* views that would update
the pg_catalog.* tables, as the pg_catalog.* tables are an
implementation detail? That's going to the extreme, but where do you
see the middle ground in terms of simplifying a user experience and
hiding users from PostgreSQL's nuts and bolts?
Hiding pg_temp_* schemas seems like a good idea to me given temp
objects are visible in every schema and the path of a temp object is
subject to change... an overly diligent admin might try and hard code
in the schema of a temp object only to find that path not portable,
thus exposing that information would strike me as a liability and not
an asset. And then there's the idea of providing an admin-mode that
exposes all of the implementation details (Hint, hint. I'd do the leg
work on this if it wouldn't be categorically dropped at the front
door). Anyway, I know we've covered this in the archives so I'll drop
it.
As an FYI, I just updated to an Opteron box and have been enjoying a
little over 1500 temp schemas and a paltry ~30 non-temp schemas.
Getting this patch in would be oh so very appreciated as maintaining
local copies of psql(1) is getting old. I know it's not my decision
to make, but I'd settle and shut up if there was an indirect proof for
why this shouldn't be included as a patch (ie, a valid usecase for an
admin or programmer who would need to see any or all of the pg_temp_*
schemas without using that data to extract more bits from the
pg_catalogs. If they know how to go through the catalogs, why do they
need \dn to display the temp schemas?).
As always, --Sean
--
Sean Chittenden
> I think the original complaint was misguided and we should not do > anything about it. IIRC the complaint amounted to "I have hundreds of > schemas and it annoys me that \dn shows them all". How is this > different from putting hundreds of tables into one schema and then being > annoyed because \dt shows them all? We have other mechanisms available > for making \dn selective (ie, you can use a name pattern). If \dn is > restricted to showing only schemas in your search path, it will become > useless. I completely agree. It's like saying that \l should only show databases you are currently connected to... Chris
> > I think the original complaint was misguided and we should not do > > anything about it. IIRC the complaint amounted to "I have > > hundreds of schemas and it annoys me that \dn shows them all". > > How is this different from putting hundreds of tables into one > > schema and then being annoyed because \dt shows them all? We have > > other mechanisms available for making \dn selective (ie, you can > > use a name pattern). If \dn is restricted to showing only schemas > > in your search path, it will become useless. > > I completely agree. It's like saying that \l should only show > databases you are currently connected to... For the record and if Tom was referring to me, the example I gave was to have \dn show all schemas that you have any permissions for, not that were in the search path. -sc -- Sean Chittenden
Sean Chittenden wrote: > Hiding pg_temp_* schemas seems like a good idea to me given temp > objects are visible in every schema and the path of a temp object is > subject to change... an overly diligent admin might try and hard code > in the schema of a temp object only to find that path not portable, > thus exposing that information would strike me as a liability and not > an asset. And then there's the idea of providing an admin-mode that > exposes all of the implementation details (Hint, hint. I'd do the leg > work on this if it wouldn't be categorically dropped at the front > door). Anyway, I know we've covered this in the archives so I'll drop > it. > > As an FYI, I just updated to an Opteron box and have been enjoying a > little over 1500 temp schemas and a paltry ~30 non-temp schemas. > Getting this patch in would be oh so very appreciated as maintaining > local copies of psql(1) is getting old. I know it's not my decision > to make, but I'd settle and shut up if there was an indirect proof for > why this shouldn't be included as a patch (ie, a valid usecase for an > admin or programmer who would need to see any or all of the pg_temp_* > schemas without using that data to extract more bits from the > pg_catalogs. If they know how to go through the catalogs, why do they > need \dn to display the temp schemas?). OK, the following patch uses UNION and an =ANY() join to the current_schemas() array to suppress non-local temp schemas, but display all other schemas. There is now cleaner way to join to the current_schemas() array, right? -- 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.90 diff -c -c -r1.90 describe.c *** src/bin/psql/describe.c 1 Dec 2003 22:21:54 -0000 1.90 --- src/bin/psql/describe.c 22 Dec 2003 06:58:48 -0000 *************** *** 1626,1639 **** initPQExpBuffer(&buf); printfPQExpBuffer(&buf, ! "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, NULL, "n.nspname", NULL, NULL); --- 1626,1650 ---- initPQExpBuffer(&buf); printfPQExpBuffer(&buf, ! "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.nspname NOT LIKE 'pg_temp_%%'\n" ! "UNION ALL\n" /* show only local temp schema */ ! "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" ! " (SELECT current_schemas('t'::boolean)) AS curr_schemas(name)\n" ! "WHERE n.nspname LIKE 'pg_temp_%%' AND\n" ! " n.nspname = ANY(curr_schemas.name)\n", ! _("Name"), ! _("Owner"), _("Name"), _("Owner")); ! processNamePattern(&buf, pattern, true, false, NULL, "n.nspname", NULL, NULL);
Bruce Momjian wrote: > > As an FYI, I just updated to an Opteron box and have been enjoying a > > little over 1500 temp schemas and a paltry ~30 non-temp schemas. > > Getting this patch in would be oh so very appreciated as maintaining > > local copies of psql(1) is getting old. I know it's not my decision > > to make, but I'd settle and shut up if there was an indirect proof for > > why this shouldn't be included as a patch (ie, a valid usecase for an > > admin or programmer who would need to see any or all of the pg_temp_* > > schemas without using that data to extract more bits from the > > pg_catalogs. If they know how to go through the catalogs, why do they > > need \dn to display the temp schemas?). > > OK, the following patch uses UNION and an =ANY() join to the > current_schemas() array to suppress non-local temp schemas, but display > all other schemas. > > There is now cleaner way to join to the current_schemas() array, right? Sorry, here is the proper patch. I had forgotten to call processNamePattern() twice for the UNION. -- 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.90 diff -c -c -r1.90 describe.c *** src/bin/psql/describe.c 1 Dec 2003 22:21:54 -0000 1.90 --- src/bin/psql/describe.c 22 Dec 2003 07:11:30 -0000 *************** *** 1626,1639 **** initPQExpBuffer(&buf); printfPQExpBuffer(&buf, ! "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, NULL, "n.nspname", NULL, NULL); --- 1626,1654 ---- initPQExpBuffer(&buf); printfPQExpBuffer(&buf, ! "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.nspname NOT LIKE 'pg_temp_%%'\n", _("Name"), _("Owner")); + processNamePattern(&buf, pattern, true, false, + NULL, "n.nspname", NULL, + NULL); ! appendPQExpBuffer(&buf, ! "UNION ALL\n" /* show only local temp schema */ ! "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" ! " (SELECT current_schemas('t'::boolean)) AS curr_schemas(name)\n" ! "WHERE n.nspname LIKE 'pg_temp_%%' AND\n" ! " n.nspname = ANY(curr_schemas.name)\n", ! _("Name"), ! _("Owner")); ! processNamePattern(&buf, pattern, true, false, NULL, "n.nspname", NULL, NULL);
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> OK, the following patch uses UNION and an =ANY() join to the
> current_schemas() array to suppress non-local temp schemas, but display
> all other schemas.
Why are you doing any of this? We had agreed to suppress all temp
schemas, period. The query should be simple.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > OK, the following patch uses UNION and an =ANY() join to the > > current_schemas() array to suppress non-local temp schemas, but display > > all other schemas. > > Why are you doing any of this? We had agreed to suppress all temp > schemas, period. The query should be simple. I know some feel that showing any temporary schemas is wrong, but it seems that the local temp schema has valuable information. If I do \d pg_temp_1.*, I see all my temporary tables. I know we have a TODO to show all existing prepared statements, and giving people a way to see their temp tables seems important. In fact, it seems more valuable than the information containted in pg_toast. The attached patch documents that non-local temp tables are suppressed. As for people accidentally hardcoding the temp table schema in their scripts, I don't see how someone would make that mistake with a schema called pg_temp_##. It is sort of like assuming a file will always exist in /tmp. One nifty idea would be for pg_temp.* to alway refer to your local temp schema. Is that a TODO? -- 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: doc/src/sgml/ref/psql-ref.sgml =================================================================== RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/psql-ref.sgml,v retrieving revision 1.101 diff -c -c -r1.101 psql-ref.sgml *** doc/src/sgml/ref/psql-ref.sgml 1 Dec 2003 22:21:54 -0000 1.101 --- doc/src/sgml/ref/psql-ref.sgml 22 Dec 2003 19:18:16 -0000 *************** *** 957,962 **** --- 957,963 ---- Lists all available schemas (namespaces). If <replaceable class="parameter">pattern</replaceable> (a regular expression) is specified, only schemas whose names match the pattern are listed. + Non-local temporary schemas are suppressed. </para> </listitem> </varlistentry> Index: src/bin/psql/describe.c =================================================================== RCS file: /cvsroot/pgsql-server/src/bin/psql/describe.c,v retrieving revision 1.90 diff -c -c -r1.90 describe.c *** src/bin/psql/describe.c 1 Dec 2003 22:21:54 -0000 1.90 --- src/bin/psql/describe.c 22 Dec 2003 19:18:19 -0000 *************** *** 1626,1639 **** initPQExpBuffer(&buf); printfPQExpBuffer(&buf, ! "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, NULL, "n.nspname", NULL, NULL); --- 1626,1654 ---- initPQExpBuffer(&buf); printfPQExpBuffer(&buf, ! "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.nspname NOT LIKE 'pg_temp_%%'\n", _("Name"), _("Owner")); + processNamePattern(&buf, pattern, true, false, + NULL, "n.nspname", NULL, + NULL); ! appendPQExpBuffer(&buf, ! "UNION ALL\n" /* show only local temp schema */ ! "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" ! " (SELECT current_schemas('t'::boolean)) AS curr_schemas(name)\n" ! "WHERE n.nspname LIKE 'pg_temp_%%' AND\n" ! " n.nspname = ANY(curr_schemas.name)\n", ! _("Name"), ! _("Owner")); ! processNamePattern(&buf, pattern, true, false, NULL, "n.nspname", NULL, NULL);
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> Why are you doing any of this? We had agreed to suppress all temp
>> schemas, period. The query should be simple.
> I know some feel that showing any temporary schemas is wrong, but it
> seems that the local temp schema has valuable information. If I do \d
> pg_temp_1.*, I see all my temporary tables. I know we have a TODO to
> show all existing prepared statements, and giving people a way to see
> their temp tables seems important.
We already have a way to find out your temp table schema name:
current_schemas.
regression=# create temp table foo(f1 int);
CREATE TABLE
regression=# select current_schemas(true);
current_schemas
-------------------------------
{pg_temp_1,pg_catalog,public}
(1 row)
regression=# select (current_schemas(true))[1];
current_schemas
-----------------
pg_temp_1
(1 row)
regression=# select relname from pg_class c join pg_namespace n
regression-# on relnamespace = n.oid
regression-# where nspname = (current_schemas(true))[1];
relname
---------
foo
(1 row)
I don't think it's reasonable to complicate \dn so much in order to
provide an alternative way of learning your temp schema name.
regards, tom lane
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> Why are you doing any of this? We had agreed to suppress all temp
> >> schemas, period. The query should be simple.
>
> > I know some feel that showing any temporary schemas is wrong, but it
> > seems that the local temp schema has valuable information. If I do \d
> > pg_temp_1.*, I see all my temporary tables. I know we have a TODO to
> > show all existing prepared statements, and giving people a way to see
> > their temp tables seems important.
>
> We already have a way to find out your temp table schema name:
> current_schemas.
>
> regression=# create temp table foo(f1 int);
> CREATE TABLE
> regression=# select current_schemas(true);
> current_schemas
> -------------------------------
> {pg_temp_1,pg_catalog,public}
> (1 row)
>
> regression=# select (current_schemas(true))[1];
> current_schemas
> -----------------
> pg_temp_1
> (1 row)
>
> regression=# select relname from pg_class c join pg_namespace n
> regression-# on relnamespace = n.oid
> regression-# where nspname = (current_schemas(true))[1];
> relname
> ---------
> foo
> (1 row)
>
>
> I don't think it's reasonable to complicate \dn so much in order to
> provide an alternative way of learning your temp schema name.
Is current_schemas(true))[1] always the temp schema name? That would
clean things up nicely.
--
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:
> Is current_schemas(true))[1] always the temp schema name?
If you have a temp schema; otherwise it'll be whatever is the front of
your search path.
> That would clean things up nicely.
If you're intent on doing this in \dn, something like
... AND (nspname not like 'pg\\_temp%'
OR nspname = (current_schemas(true))[1]) ...
would probably work well enough.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Is current_schemas(true))[1] always the temp schema name? > > If you have a temp schema; otherwise it'll be whatever is the front of > your search path. > > > That would clean things up nicely. > > If you're intent on doing this in \dn, something like > > ... AND (nspname not like 'pg\\_temp%' > OR nspname = (current_schemas(true))[1]) ... > > would probably work well enough. Yep, works fine. Patch attached. I wasn't so worried about telling people the name of their temporary schema as I was giving them a complete, reasonable \dn output. -- 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: doc/src/sgml/ref/psql-ref.sgml =================================================================== RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/psql-ref.sgml,v retrieving revision 1.101 diff -c -c -r1.101 psql-ref.sgml *** doc/src/sgml/ref/psql-ref.sgml 1 Dec 2003 22:21:54 -0000 1.101 --- doc/src/sgml/ref/psql-ref.sgml 23 Dec 2003 04:03:33 -0000 *************** *** 957,962 **** --- 957,963 ---- Lists all available schemas (namespaces). If <replaceable class="parameter">pattern</replaceable> (a regular expression) is specified, only schemas whose names match the pattern are listed. + Non-local temporary schemas are suppressed. </para> </listitem> </varlistentry> Index: src/bin/psql/describe.c =================================================================== RCS file: /cvsroot/pgsql-server/src/bin/psql/describe.c,v retrieving revision 1.90 diff -c -c -r1.90 describe.c *** src/bin/psql/describe.c 1 Dec 2003 22:21:54 -0000 1.90 --- src/bin/psql/describe.c 23 Dec 2003 04:03:35 -0000 *************** *** 1626,1639 **** initPQExpBuffer(&buf); printfPQExpBuffer(&buf, ! "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, NULL, "n.nspname", NULL, NULL); --- 1626,1640 ---- initPQExpBuffer(&buf); printfPQExpBuffer(&buf, ! "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.nspname NOT LIKE 'pg_temp_%%' OR\n" ! " n.nspname = (current_schemas(true))[1]\n", /* temp schema is first */ _("Name"), _("Owner")); ! processNamePattern(&buf, pattern, true, false, NULL, "n.nspname", NULL, NULL);
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> ! "WHERE n.nspname NOT LIKE 'pg_temp_%%' OR\n"
You forgot that '_' is a special character for LIKE. You need some
backslashes there (4 apiece, I think).
Also, there had better be parentheses around the whole OR clause;
else I'm not sure that the precedence will work correctly if
processNamePattern plasters an AND phrase after this.
regards, tom lane
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> ! " n.nspname = (current_schemas(true))[1]\n", /* temp schema is first */
One more thing: that needs to be pg_catalog.current_schemas to
be search-path-proof.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > ! " n.nspname = (current_schemas(true))[1]\n", /* temp schema is first */ > > One more thing: that needs to be pg_catalog.current_schemas to > be search-path-proof. Changes made, with underscores escaped and parentheses added, all good ideas. Patch attached. -- 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: doc/src/sgml/ref/psql-ref.sgml =================================================================== RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/psql-ref.sgml,v retrieving revision 1.101 diff -c -c -r1.101 psql-ref.sgml *** doc/src/sgml/ref/psql-ref.sgml 1 Dec 2003 22:21:54 -0000 1.101 --- doc/src/sgml/ref/psql-ref.sgml 23 Dec 2003 04:56:21 -0000 *************** *** 957,962 **** --- 957,963 ---- Lists all available schemas (namespaces). If <replaceable class="parameter">pattern</replaceable> (a regular expression) is specified, only schemas whose names match the pattern are listed. + Non-local temporary schemas are suppressed. </para> </listitem> </varlistentry> Index: src/bin/psql/describe.c =================================================================== RCS file: /cvsroot/pgsql-server/src/bin/psql/describe.c,v retrieving revision 1.90 diff -c -c -r1.90 describe.c *** src/bin/psql/describe.c 1 Dec 2003 22:21:54 -0000 1.90 --- src/bin/psql/describe.c 23 Dec 2003 04:56:22 -0000 *************** *** 1626,1639 **** initPQExpBuffer(&buf); printfPQExpBuffer(&buf, ! "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, NULL, "n.nspname", NULL, NULL); --- 1626,1640 ---- initPQExpBuffer(&buf); printfPQExpBuffer(&buf, ! "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.nspname NOT LIKE 'pg\\\\_temp\\\\_%%' OR\n" ! " n.nspname = (pg_catalog.current_schemas(true))[1])\n", /* temp schema is first */ _("Name"), _("Owner")); ! processNamePattern(&buf, pattern, true, false, NULL, "n.nspname", NULL, NULL);
> initPQExpBuffer(&buf);
> printfPQExpBuffer(&buf,
> ! "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.nspname NOT LIKE 'pg_temp_%%' OR\n"
> ! " n.nspname = (current_schemas(true))[1]\n", /* temp schema is first */
> _("Name"),
> _("Owner"));
> ! processNamePattern(&buf, pattern, true, false,
> NULL, "n.nspname", NULL,
> NULL);
Not that this is incorrect. You need to go:
LIKE 'pg\\\\_temp\\\\_%' probably.
Chris
Christopher Kings-Lynne wrote:
> > initPQExpBuffer(&buf);
> > printfPQExpBuffer(&buf,
> > ! "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.nspname NOT LIKE 'pg_temp_%%' OR\n"
> > ! " n.nspname = (current_schemas(true))[1]\n", /* temp schema is first */
> > _("Name"),
> > _("Owner"));
> > ! processNamePattern(&buf, pattern, true, false,
> > NULL, "n.nspname", NULL,
> > NULL);
>
> Not that this is incorrect. You need to go:
>
> LIKE 'pg\\\\_temp\\\\_%' probably.
Got it.
--
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
>>Not that this is incorrect. You need to go: >> >>LIKE 'pg\\\\_temp\\\\_%' probably. > > > Got it. Yes. Sigh. It's so annoying living about 15 hours out from when all this stuff happens :P I read a thread, come across something, and then make a contribution, only to read about 40 threads down that the question has been answered about 4 times already :( Chris