Re: psql \d* and system objects - Mailing list pgsql-hackers

Attached patch applied, including documentation updates;  I think this
is the best we are going to do to balance usability and consistency.  I
have removed this as an open 8.4 item.

With this change \dfS and \df * do the same thing;  I assume we don't
want to remove the 'S' modifier and tell people to just use '*', or
support '*' as the modifier instead of 'S', e.g. \df*.

---------------------------------------------------------------------------

Bruce Momjian wrote:
> Robert Treat wrote:
> > Actually I find the inconsistency to hurt usability, which is typically what
> > you get with inconsistent interfaces.
> >
> > I'm not certain, but I think I would be happier if we did:
> >
> > \d*    user space objects
> > \d*S  include system objects
> >
> > For those that want system only, do
> > \d*S  pg_catalog.
> > ( if you want to argue temp/toast, adjust the search accordingly)
> >
> > I think the trick to getting this working is to enforce this with search
> > patterns *and* tab completion as well. Yes, this means that Tom's desire for
>
> I talked to Robert on IM and found two new reasons to make 'S' assumed
> if a pattern is supplied.  The first is the use of tab completion:
>
>     test=> \df si<tab><tab>
>     sign            similar_escape  sin
>     test=> \df sin
>                        List of functions
>      Schema | Name | Result data type | Argument data types
>     --------+------+------------------+---------------------
>     (0 rows)
>
> As you can see tab-tab assumes system tables are visible, but current CVS
> does not without 'S'.  The second case is:
>
>     test=> \df pg_catalog.sin
>                        List of functions
>      Schema | Name | Result data type | Argument data types
>     --------+------+------------------+---------------------
>     (0 rows)
>
> Only \dfS works in this case.
>
> I think we should move forward and assume 'S' for \d* patterns;  it is
> inconsistent, but usability requires it.
>
> Objections?
>
> --
>   Bruce Momjian  <bruce@momjian.us>        http://momjian.us
>   EnterpriseDB                             http://enterprisedb.com
>
>   + If your life is a hard drive, Christ can be your backup. +
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
  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: doc/src/sgml/ref/psql-ref.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.220
diff -c -c -r1.220 psql-ref.sgml
*** doc/src/sgml/ref/psql-ref.sgml    26 Feb 2009 16:02:37 -0000    1.220
--- doc/src/sgml/ref/psql-ref.sgml    2 Apr 2009 15:03:01 -0000
***************
*** 853,861 ****
          more information is displayed: any comments associated with the
          columns of the table are shown, as is the presence of OIDs in the
          table.
!         The letter <literal>S</literal> adds the listing of system
!         objects; without <literal>S</literal>, only non-system
!         objects are shown.
          </para>

          <note>
--- 853,861 ----
          more information is displayed: any comments associated with the
          columns of the table are shown, as is the presence of OIDs in the
          table.
!         By default, only user-created objects are shown;  supply a
!         pattern or the <literal>S</literal> modifier to include system
!         objects.
          </para>

          <note>
***************
*** 879,887 ****
          return type and the data types they operate on. If <replaceable
          class="parameter">pattern</replaceable>
          is specified, only aggregates whose names match the pattern are shown.
!         The letter <literal>S</literal> adds the listing of system
!         objects; without <literal>S</literal>, only non-system
!         objects are shown.
          </para>
          </listitem>
        </varlistentry>
--- 879,887 ----
          return type and the data types they operate on. If <replaceable
          class="parameter">pattern</replaceable>
          is specified, only aggregates whose names match the pattern are shown.
!         By default, only user-created objects are shown;  supply a
!         pattern or the <literal>S</literal> modifier to include system
!         objects.
          </para>
          </listitem>
        </varlistentry>
***************
*** 910,918 ****
          If <replaceable class="parameter">pattern</replaceable>
          is specified, only conversions whose names match the pattern are
          listed.
!         The letter <literal>S</literal> adds the listing of system
!         objects; without <literal>S</literal>, only non-system
!         objects are shown.
          </para>
          </listitem>
        </varlistentry>
--- 910,918 ----
          If <replaceable class="parameter">pattern</replaceable>
          is specified, only conversions whose names match the pattern are
          listed.
!         By default, only user-created objects are shown;  supply a
!         pattern or the <literal>S</literal> modifier to include system
!         objects.
          </para>
          </listitem>
        </varlistentry>
***************
*** 939,947 ****
          class="parameter">pattern</replaceable>, or of all visible objects if
          no argument is given.  But in either case, only objects that have
          a description are listed.
!         The letter <literal>S</literal> adds the listing of system
!         objects; without <literal>S</literal>, only non-system
!         objects are shown.
          (<quote>Object</quote> covers aggregates, functions, operators,
          types, relations (tables, views, indexes, sequences, large
          objects), rules, and triggers.) For example:
--- 939,947 ----
          class="parameter">pattern</replaceable>, or of all visible objects if
          no argument is given.  But in either case, only objects that have
          a description are listed.
!         By default, only user-created objects are shown;  supply a
!         pattern or the <literal>S</literal> modifier to include system
!         objects.
          (<quote>Object</quote> covers aggregates, functions, operators,
          types, relations (tables, views, indexes, sequences, large
          objects), rules, and triggers.) For example:
***************
*** 971,979 ****
          Lists all available domains. If <replaceable
          class="parameter">pattern</replaceable>
          is specified, only matching domains are shown.
!         The letter <literal>S</literal> adds the listing of system
!         objects; without <literal>S</literal>, only non-system
!         objects are shown.
          </para>
          </listitem>
        </varlistentry>
--- 971,979 ----
          Lists all available domains. If <replaceable
          class="parameter">pattern</replaceable>
          is specified, only matching domains are shown.
!         By default, only user-created objects are shown;  supply a
!         pattern or the <literal>S</literal> modifier to include system
!         objects.
          </para>
          </listitem>
        </varlistentry>
***************
*** 1045,1053 ****
          is specified, only functions whose names match the pattern are shown.
          If the form <literal>\df+</literal> is used, additional information about
          each function, including volatility, language, source code and description, is shown.
!         The letter <literal>S</literal> adds the listing of system
!         objects; without <literal>S</literal>, only non-system
!         objects are shown.
          </para>

          <note>
--- 1045,1053 ----
          is specified, only functions whose names match the pattern are shown.
          If the form <literal>\df+</literal> is used, additional information about
          each function, including volatility, language, source code and description, is shown.
!         By default, only user-created objects are shown;  supply a
!         pattern or the <literal>S</literal> modifier to include system
!         objects.
          </para>

          <note>
***************
*** 1155,1163 ****
          and tables.  If <literal>+</literal> is
          appended to the command name, each object is listed with its
          physical size on disk and its associated description, if any.
!         The letter <literal>S</literal> adds the listing of system
!         objects; without <literal>S</literal>, only non-system
!         objects are shown.
          </para>

          <para>
--- 1155,1163 ----
          and tables.  If <literal>+</literal> is
          appended to the command name, each object is listed with its
          physical size on disk and its associated description, if any.
!         By default, only user-created objects are shown;  supply a
!         pattern or the <literal>S</literal> modifier to include system
!         objects.
          </para>

          <para>
***************
*** 1202,1210 ****
          Lists available operators with their operand and return types.
          If <replaceable class="parameter">pattern</replaceable> is
          specified, only operators whose names match the pattern are listed.
!         The letter <literal>S</literal> adds the listing of system
!         objects; without <literal>S</literal>, only non-system
!         objects are shown.
          </para>
          </listitem>
        </varlistentry>
--- 1202,1210 ----
          Lists available operators with their operand and return types.
          If <replaceable class="parameter">pattern</replaceable> is
          specified, only operators whose names match the pattern are listed.
!         By default, only user-created objects are shown;  supply a
!         pattern or the <literal>S</literal> modifier to include system
!         objects.
          </para>
          </listitem>
        </varlistentry>
***************
*** 1237,1245 ****
          class="parameter">pattern</replaceable>. The command form
          <literal>\dT+</literal> shows extra information, namely the type's internal name, size, and
          allowed values for <type>enum</> types.
!         The letter <literal>S</literal> adds the listing of system
!         objects; without <literal>S</literal>, only non-system
!         objects are shown.
          </para>
          </listitem>
        </varlistentry>
--- 1237,1245 ----
          class="parameter">pattern</replaceable>. The command form
          <literal>\dT+</literal> shows extra information, namely the type's internal name, size, and
          allowed values for <type>enum</> types.
!         By default, only user-created objects are shown;  supply a
!         pattern or the <literal>S</literal> modifier to include system
!         objects.
          </para>
          </listitem>
        </varlistentry>
Index: src/bin/psql/describe.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.203
diff -c -c -r1.203 describe.c
*** src/bin/psql/describe.c    26 Mar 2009 22:26:07 -0000    1.203
--- src/bin/psql/describe.c    2 Apr 2009 15:03:03 -0000
***************
*** 94,100 ****
                        "WHERE p.proisagg\n",
                        gettext_noop("Description"));

!      if (!showSystem)
           appendPQExpBuffer(&buf, "      AND n.nspname <> 'pg_catalog'\n");

      processSQLNamePattern(pset.db, &buf, pattern, true, false,
--- 94,100 ----
                        "WHERE p.proisagg\n",
                        gettext_noop("Description"));

!      if (!showSystem && !pattern)
           appendPQExpBuffer(&buf, "      AND n.nspname <> 'pg_catalog'\n");

      processSQLNamePattern(pset.db, &buf, pattern, true, false,
***************
*** 281,287 ****
                        "      AND p.proargtypes[0] IS DISTINCT FROM 'pg_catalog.cstring'::pg_catalog.regtype\n"
                        "      AND NOT p.proisagg\n");

!      if (!showSystem)
           appendPQExpBuffer(&buf, "      AND n.nspname <> 'pg_catalog'\n");

      processSQLNamePattern(pset.db, &buf, pattern, true, false,
--- 281,287 ----
                        "      AND p.proargtypes[0] IS DISTINCT FROM 'pg_catalog.cstring'::pg_catalog.regtype\n"
                        "      AND NOT p.proisagg\n");

!      if (!showSystem && !pattern)
           appendPQExpBuffer(&buf, "      AND n.nspname <> 'pg_catalog'\n");

      processSQLNamePattern(pset.db, &buf, pattern, true, false,
***************
*** 372,378 ****
      else
          appendPQExpBuffer(&buf, "  AND t.typname !~ '^_'\n");

!      if (!showSystem)
           appendPQExpBuffer(&buf, "      AND n.nspname <> 'pg_catalog'\n");

      /* Match name pattern against either internal or external name */
--- 372,378 ----
      else
          appendPQExpBuffer(&buf, "  AND t.typname !~ '^_'\n");

!      if (!showSystem && !pattern)
           appendPQExpBuffer(&buf, "      AND n.nspname <> 'pg_catalog'\n");

      /* Match name pattern against either internal or external name */
***************
*** 427,436 ****
                        gettext_noop("Result type"),
                        gettext_noop("Description"));

!      if (!showSystem)
           appendPQExpBuffer(&buf, "      WHERE n.nspname <> 'pg_catalog'\n");

!     processSQLNamePattern(pset.db, &buf, pattern, !showSystem, true,
                            "n.nspname", "o.oprname", NULL,
                            "pg_catalog.pg_operator_is_visible(o.oid)");

--- 427,436 ----
                        gettext_noop("Result type"),
                        gettext_noop("Description"));

!      if (!showSystem && !pattern)
           appendPQExpBuffer(&buf, "      WHERE n.nspname <> 'pg_catalog'\n");

!     processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, true,
                            "n.nspname", "o.oprname", NULL,
                            "pg_catalog.pg_operator_is_visible(o.oid)");

***************
*** 631,637 ****
                        "  WHERE p.proisagg\n",
                        gettext_noop("aggregate"));

!      if (!showSystem)
           appendPQExpBuffer(&buf, "      AND n.nspname <> 'pg_catalog'\n");

      processSQLNamePattern(pset.db, &buf, pattern, true, false,
--- 631,637 ----
                        "  WHERE p.proisagg\n",
                        gettext_noop("aggregate"));

!      if (!showSystem && !pattern)
           appendPQExpBuffer(&buf, "      AND n.nspname <> 'pg_catalog'\n");

      processSQLNamePattern(pset.db, &buf, pattern, true, false,
***************
*** 654,660 ****
                        "      AND NOT p.proisagg\n",
                        gettext_noop("function"));

!      if (!showSystem)
           appendPQExpBuffer(&buf, "      AND n.nspname <> 'pg_catalog'\n");

      processSQLNamePattern(pset.db, &buf, pattern, true, false,
--- 654,660 ----
                        "      AND NOT p.proisagg\n",
                        gettext_noop("function"));

!      if (!showSystem && !pattern)
           appendPQExpBuffer(&buf, "      AND n.nspname <> 'pg_catalog'\n");

      processSQLNamePattern(pset.db, &buf, pattern, true, false,
***************
*** 672,681 ****
      "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
                        gettext_noop("operator"));

!      if (!showSystem)
           appendPQExpBuffer(&buf, "      WHERE n.nspname <> 'pg_catalog'\n");

!     processSQLNamePattern(pset.db, &buf, pattern, !showSystem, false,
                            "n.nspname", "o.oprname", NULL,
                            "pg_catalog.pg_operator_is_visible(o.oid)");

--- 672,681 ----
      "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
                        gettext_noop("operator"));

!      if (!showSystem && !pattern)
           appendPQExpBuffer(&buf, "      WHERE n.nspname <> 'pg_catalog'\n");

!     processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false,
                            "n.nspname", "o.oprname", NULL,
                            "pg_catalog.pg_operator_is_visible(o.oid)");

***************
*** 690,699 ****
      "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n",
                        gettext_noop("data type"));

!      if (!showSystem)
           appendPQExpBuffer(&buf, "      WHERE n.nspname <> 'pg_catalog'\n");

!     processSQLNamePattern(pset.db, &buf, pattern, !showSystem, false,
                            "n.nspname", "pg_catalog.format_type(t.oid, NULL)",
                            NULL,
                            "pg_catalog.pg_type_is_visible(t.oid)");
--- 690,699 ----
      "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n",
                        gettext_noop("data type"));

!      if (!showSystem && !pattern)
           appendPQExpBuffer(&buf, "      WHERE n.nspname <> 'pg_catalog'\n");

!     processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false,
                            "n.nspname", "pg_catalog.format_type(t.oid, NULL)",
                            NULL,
                            "pg_catalog.pg_type_is_visible(t.oid)");
***************
*** 714,720 ****
                        gettext_noop("view"),
                        gettext_noop("index"),
                        gettext_noop("sequence"));
!      if (!showSystem)
           appendPQExpBuffer(&buf, "      AND n.nspname <> 'pg_catalog'\n");

      processSQLNamePattern(pset.db, &buf, pattern, true, false,
--- 714,720 ----
                        gettext_noop("view"),
                        gettext_noop("index"),
                        gettext_noop("sequence"));
!      if (!showSystem && !pattern)
           appendPQExpBuffer(&buf, "      AND n.nspname <> 'pg_catalog'\n");

      processSQLNamePattern(pset.db, &buf, pattern, true, false,
***************
*** 734,740 ****
                        "  WHERE r.rulename != '_RETURN'\n",
                        gettext_noop("rule"));

!      if (!showSystem)
           appendPQExpBuffer(&buf, "      AND n.nspname <> 'pg_catalog'\n");

      /* XXX not sure what to do about visibility rule here? */
--- 734,740 ----
                        "  WHERE r.rulename != '_RETURN'\n",
                        gettext_noop("rule"));

!      if (!showSystem && !pattern)
           appendPQExpBuffer(&buf, "      AND n.nspname <> 'pg_catalog'\n");

      /* XXX not sure what to do about visibility rule here? */
***************
*** 753,763 ****
                     "       JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid\n"
      "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n",
                        gettext_noop("trigger"));
!      if (!showSystem)
           appendPQExpBuffer(&buf, "      WHERE n.nspname <> 'pg_catalog'\n");

      /* XXX not sure what to do about visibility rule here? */
!     processSQLNamePattern(pset.db, &buf, pattern, !showSystem, false,
                            "n.nspname", "t.tgname", NULL,
                            "pg_catalog.pg_table_is_visible(c.oid)");

--- 753,763 ----
                     "       JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid\n"
      "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n",
                        gettext_noop("trigger"));
!      if (!showSystem && !pattern)
           appendPQExpBuffer(&buf, "      WHERE n.nspname <> 'pg_catalog'\n");

      /* XXX not sure what to do about visibility rule here? */
!     processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false,
                            "n.nspname", "t.tgname", NULL,
                            "pg_catalog.pg_table_is_visible(c.oid)");

***************
*** 808,817 ****
                        "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)");

--- 808,817 ----
                        "FROM pg_catalog.pg_class c\n"
       "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");

!      if (!showSystem && !pattern)
           appendPQExpBuffer(&buf, "      WHERE n.nspname <> 'pg_catalog'\n");

!     processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false,
                            "n.nspname", "c.relname", NULL,
                            "pg_catalog.pg_table_is_visible(c.oid)");

***************
*** 2008,2019 ****
          appendPQExpBuffer(&buf, "'i',");
      if (showSeq)
          appendPQExpBuffer(&buf, "'S',");
!     if (showSystem)
          appendPQExpBuffer(&buf, "'s',");    /* was RELKIND_SPECIAL in <= 8.1 */
      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"
--- 2008,2019 ----
          appendPQExpBuffer(&buf, "'i',");
      if (showSeq)
          appendPQExpBuffer(&buf, "'S',");
!     if (showSystem || pattern)
          appendPQExpBuffer(&buf, "'s',");    /* was RELKIND_SPECIAL in <= 8.1 */
      appendPQExpBuffer(&buf, "''");        /* dummy */
      appendPQExpBuffer(&buf, ")\n");

!     if (!showSystem && !pattern)
          /* Exclude system and pg_toast objects, but show temp tables */
          appendPQExpBuffer(&buf,
                            "  AND n.nspname <> 'pg_catalog'\n"
***************
*** 2087,2093 ****
                        gettext_noop("Modifier"),
                        gettext_noop("Check"));

!      if (!showSystem)
           appendPQExpBuffer(&buf, "  AND n.nspname <> 'pg_catalog'\n");

      processSQLNamePattern(pset.db, &buf, pattern, true, false,
--- 2087,2093 ----
                        gettext_noop("Modifier"),
                        gettext_noop("Check"));

!      if (!showSystem && !pattern)
           appendPQExpBuffer(&buf, "  AND n.nspname <> 'pg_catalog'\n");

      processSQLNamePattern(pset.db, &buf, pattern, true, false,
***************
*** 2142,2148 ****
                        gettext_noop("yes"), gettext_noop("no"),
                        gettext_noop("Default?"));

!      if (!showSystem)
           appendPQExpBuffer(&buf, "      AND n.nspname <> 'pg_catalog'\n");

      processSQLNamePattern(pset.db, &buf, pattern, true, false,
--- 2142,2148 ----
                        gettext_noop("yes"), gettext_noop("no"),
                        gettext_noop("Default?"));

!      if (!showSystem && !pattern)
           appendPQExpBuffer(&buf, "      AND n.nspname <> 'pg_catalog'\n");

      processSQLNamePattern(pset.db, &buf, pattern, true, false,