Gregory Stark wrote:
> The behaviour of \dt in the face of tables which shadow system tables is
> actually even stranger:
>
> postgres=# create table pg_proc (t text);
> CREATE TABLE
> postgres=# commit;
> COMMIT
> postgres=# \dt pg_proc
> No matching relations found.
>
>
> And I don't see any reason aggregates, operators, etc, shouldn't be any more
> susceptible the shadowing problem.
The inconsistency between \d and \dt is not defensible, and no one said
they liked it. Here is an example:
test=> \d pg_language
--> Table "pg_catalog.pg_language"
Column | Type | Modifiers
---------------+-----------+-----------
lanname | name | not null
lanowner | oid | not null
lanispl | boolean | not null
lanpltrusted | boolean | not null
lanplcallfoid | oid | not null
lanvalidator | oid | not null
lanacl | aclitem[] |
Indexes:
"pg_language_name_index" UNIQUE, btree (lanname)
"pg_language_oid_index" UNIQUE, btree (oid)
test=> \dt pg_language
--> No matching relations found.
As you can see, \d shows system tables, while \dt does not. The
attached patch makes \d and \dt consistent:
test=> \d pg_language
Did not find any relation named "pg_language".
test=> \dt pg_language
No matching relations found.
test=> \dS pg_language
Table "pg_catalog.pg_language"
Column | Type | Modifiers
---------------+-----------+-----------
lanname | name | not null
lanowner | oid | not null
lanispl | boolean | not null
lanpltrusted | boolean | not null
lanplcallfoid | oid | not null
lanvalidator | oid | not null
lanacl | aclitem[] |
Indexes:
"pg_language_name_index" UNIQUE, btree (lanname)
"pg_language_oid_index" UNIQUE, btree (oid)
test=> \dtS pg_language
List of relations
Schema | Name | Type | Owner
------------+-------------+-------+----------
pg_catalog | pg_language | table | postgres
(1 row)
In pre-8.4, 'S' was recognised only by \dt.
The other part of the patch shows system and _user_ tables when \dtS is
used, to be consistent with the rest of the \d* commands.
I know we don't like the current behavior, but I think we need to make
them consistent first for easy testing and so when we change it, it will
remain consistent.
Applied. I will work on a consensus patch soon for the new behavior.
--
Bruce Momjian <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: src/bin/psql/command.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/psql/command.c,v
retrieving revision 1.201
diff -c -c -r1.201 command.c
*** src/bin/psql/command.c 6 Jan 2009 21:10:30 -0000 1.201
--- src/bin/psql/command.c 20 Jan 2009 02:02:17 -0000
***************
*** 334,347 ****
OT_NORMAL, NULL, true);
show_verbose = strchr(cmd, '+') ? true : false;
! show_system = strchr(cmd, 'S') ? true: false;
switch (cmd[1])
{
case '\0':
case '+':
if (pattern)
! success = describeTableDetails(pattern, show_verbose);
else
/* standard listing of interesting things */
success = listTables("tvs", NULL, show_verbose, show_system);
--- 334,348 ----
OT_NORMAL, NULL, true);
show_verbose = strchr(cmd, '+') ? true : false;
! show_system = strchr(cmd, 'S') ? true : false;
switch (cmd[1])
{
case '\0':
case '+':
+ case 'S':
if (pattern)
! success = describeTableDetails(pattern, show_verbose, show_system);
else
/* standard listing of interesting things */
success = listTables("tvs", NULL, show_verbose, show_system);
***************
*** 390,396 ****
case 'v':
case 'i':
case 's':
- case 'S':
success = listTables(&cmd[1], pattern, show_verbose, show_system);
break;
case 'u':
--- 391,396 ----
Index: src/bin/psql/describe.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.196
diff -c -c -r1.196 describe.c
*** src/bin/psql/describe.c 19 Jan 2009 18:44:32 -0000 1.196
--- src/bin/psql/describe.c 20 Jan 2009 02:02:17 -0000
***************
*** 782,788 ****
* verbose: if true, this is \d+
*/
bool
! describeTableDetails(const char *pattern, bool verbose)
{
PQExpBufferData buf;
PGresult *res;
--- 782,788 ----
* verbose: if true, this is \d+
*/
bool
! describeTableDetails(const char *pattern, bool verbose, bool showSystem)
{
PQExpBufferData buf;
PGresult *res;
***************
*** 797,803 ****
"FROM pg_catalog.pg_class c\n"
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");
! processSQLNamePattern(pset.db, &buf, pattern, false, false,
"n.nspname", "c.relname", NULL,
"pg_catalog.pg_table_is_visible(c.oid)");
--- 797,806 ----
"FROM pg_catalog.pg_class c\n"
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");
! if (!showSystem)
! appendPQExpBuffer(&buf, " WHERE n.nspname <> 'pg_catalog'\n");
!
! processSQLNamePattern(pset.db, &buf, pattern, !showSystem, false,
"n.nspname", "c.relname", NULL,
"pg_catalog.pg_table_is_visible(c.oid)");
***************
*** 1961,1980 ****
appendPQExpBuffer(&buf, "'i',");
if (showSeq)
appendPQExpBuffer(&buf, "'S',");
! if (showSystem && showTables)
appendPQExpBuffer(&buf, "'s',"); /* was RELKIND_SPECIAL in <= 8.1.X */
appendPQExpBuffer(&buf, "''"); /* dummy */
appendPQExpBuffer(&buf, ")\n");
! /*
! * If showSystem is specified, show only system objects (those in
! * pg_catalog). Otherwise, suppress system objects, including those in
! * pg_catalog and pg_toast. (We don't want to hide temp tables though.)
! */
! if (showSystem)
! appendPQExpBuffer(&buf,
! " AND n.nspname = 'pg_catalog'\n");
! else
appendPQExpBuffer(&buf,
" AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname !~ '^pg_toast'\n");
--- 1964,1976 ----
appendPQExpBuffer(&buf, "'i',");
if (showSeq)
appendPQExpBuffer(&buf, "'S',");
! if (showSystem)
appendPQExpBuffer(&buf, "'s',"); /* was RELKIND_SPECIAL in <= 8.1.X */
appendPQExpBuffer(&buf, "''"); /* dummy */
appendPQExpBuffer(&buf, ")\n");
! if (!showSystem)
! /* Exclude system and pg_toast objects, but show temp tables */
appendPQExpBuffer(&buf,
" AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname !~ '^pg_toast'\n");
Index: src/bin/psql/describe.h
===================================================================
RCS file: /cvsroot/pgsql/src/bin/psql/describe.h,v
retrieving revision 1.38
diff -c -c -r1.38 describe.h
*** src/bin/psql/describe.h 6 Jan 2009 21:10:30 -0000 1.38
--- src/bin/psql/describe.h 20 Jan 2009 02:02:17 -0000
***************
*** 34,40 ****
extern bool objectDescription(const char *pattern, bool showSystem);
/* \d foo */
! extern bool describeTableDetails(const char *pattern, bool verbose);
/* \dF */
extern bool listTSConfigs(const char *pattern, bool verbose);
--- 34,40 ----
extern bool objectDescription(const char *pattern, bool showSystem);
/* \d foo */
! extern bool describeTableDetails(const char *pattern, bool verbose, bool showSystem);
/* \dF */
extern bool listTSConfigs(const char *pattern, bool verbose);
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
