Re: [PATCHES] [GENERAL] Temporary tables and miscellaneous schemas
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] [GENERAL] Temporary tables and miscellaneous schemas
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] [GENERAL] Temporary tables and miscellaneous schemas
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
[EMAIL PROTECTED] | (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 - 1.90
--- src/bin/psql/describe.c 22 Dec 2003 06:58:48 -
***
*** 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);
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] [GENERAL] Temporary tables and miscellaneous schemas
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
[EMAIL PROTECTED] | (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 - 1.90
--- src/bin/psql/describe.c 22 Dec 2003 07:11:30 -
***
*** 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);
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [PATCHES] [GENERAL] Temporary tables and miscellaneous schemas
Bruce Momjian [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] [GENERAL] Temporary tables and miscellaneous schemas
Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] 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
[EMAIL PROTECTED] | (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 - 1.101
--- doc/src/sgml/ref/psql-ref.sgml 22 Dec 2003 19:18:16 -
***
*** 957,962
--- 957,963
Lists all available schemas (namespaces). If replaceable
class=parameterpattern/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 - 1.90
--- src/bin/psql/describe.c 22 Dec 2003 19:18:19 -
***
*** 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);
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] [GENERAL] Temporary tables and miscellaneous schemas
Bruce Momjian [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [PATCHES] [GENERAL] Temporary tables and miscellaneous schemas
Bruce Momjian [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] [GENERAL] Temporary tables and miscellaneous schemas
Bruce Momjian [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] [GENERAL] Temporary tables and miscellaneous schemas
Bruce Momjian [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] [GENERAL] Temporary tables and miscellaneous schemas
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 ---(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
Re: [PATCHES] [GENERAL] Temporary tables and miscellaneous schemas
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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] [GENERAL] Temporary tables and miscellaneous schemas
pgman wrote:
Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] 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
[EMAIL PROTECTED] | (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 - 1.101
--- doc/src/sgml/ref/psql-ref.sgml 21 Dec 2003 04:04:35 -
***
*** 954,960
listitem
para
! Lists all available schemas (namespaces). If replaceable
class=parameterpattern/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=parameterpattern/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 - 1.90
--- src/bin/psql/describe.c 21 Dec 2003 04:04:37 -
***
*** 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);
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [PATCHES] [GENERAL] Temporary tables and miscellaneous schemas
Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] 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
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] [GENERAL] Temporary tables and miscellaneous schemas
Bruce Momjian [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] [GENERAL] Temporary tables and miscellaneous schemas
Bruce Momjian [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] [GENERAL] Temporary tables and miscellaneous schemas
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] 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 [EMAIL PROTECTED] | (610) 359-1001 + 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 [EMAIL PROTECTED]
Re: [PATCHES] [GENERAL] Temporary tables and miscellaneous schemas
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] 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 [EMAIL PROTECTED] | (610) 359-1001 + 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
Re: [PATCHES] [GENERAL] Temporary tables and miscellaneous schemas
Sean Chittenden [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [GENERAL] Temporary tables and miscellaneous 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, 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
