Re: FWD: Re: Updated backslash consistency patch - Mailing list pgsql-hackers

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  <bruce@momjian.us>        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);