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

Maybe the best we are going to do is to have any pattern supplied to \d*
assume 'S' (include system objects).  I actually have a patch that does
that, attached. (It is from January so might need adjustment.)

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

Robert Haas wrote:
> On Sun, Mar 29, 2009 at 1:32 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Now I *have* a user function named sin(), it's not getting called
> > (which might surprise me if I didn't know there was a conflicting
> > system function) and \df doesn't show me either one.
> >
> > I actually was expecting the above example to show me the user function,
> > which I was then going to rant about being a lie. ?But the actual
> > behavior is even worse than that.
>
> Well, that is clearly a bug.
>
> > There is not anything that is not broken about HEAD's behavior,
> > and the sooner we admit that the sooner we can get to a fix.
> > Slicing the categorization more finely or in different ways is
> > not going to improve matters: the concept that there is a categorization
> > that will make it hide requested objects is wrong to begin with.
>
> Well, by that argument, 8.3 is broken, too, because it hides
> pg_catalog tables, views, sequences, and indices.  It's fair to say
> that the system shouldn't hide "requested" objects, but sometimes
> people want request only the objects that they created, and not the
> ones that are part of the system.  In 8.3, if you want to list all of
> the functions you've defined (as opposed to the ones that came with
> the system), you have a couple of not-so-fun options:
>
> 1. pg_dump -s | grep 'CREATE.*FUNCTION'
> 2. looking up the **40-line** query that \df issues, modifying it to
> exclude system functions, and running it by hand
>
> This has been a huge irritation to me for many years, and (whatever
> else you can say about the patch that started all this) it makes this
> particular thing a whole lot easier.  I'd like to find a way to still
> have that be easy while fixing some of the other issues.
>
> Even in 8.3, we have this oddness:
>
> \dt pg_index
> No matching relations found.
> select sum(1) from pg_index;
>  sum
> -----
>  332
> (1 row)
>
> One idea I had is to issue some kind of a warning if a \d command
> matches system objects that are excluded from the output, like this:
>
> note: %d system objects also found, use %s to display
>
> ...Robert

--
  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/describe.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.195
diff -c -c -r1.195 describe.c
*** src/bin/psql/describe.c    6 Jan 2009 23:01:57 -0000    1.195
--- src/bin/psql/describe.c    15 Jan 2009 16:50:45 -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)");

***************
*** 620,626 ****
                        "  WHERE p.proisagg\n",
                        gettext_noop("aggregate"));

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

      processSQLNamePattern(pset.db, &buf, pattern, true, false,
--- 620,626 ----
                        "  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,
***************
*** 643,649 ****
                        "      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,
--- 643,649 ----
                        "      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,
***************
*** 661,670 ****
      "       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)");

--- 661,670 ----
      "       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)");

***************
*** 679,688 ****
      "       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)");
--- 679,688 ----
      "       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)");
***************
*** 703,709 ****
                        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,
--- 703,709 ----
                        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,
***************
*** 723,729 ****
                        "  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? */
--- 723,729 ----
                        "  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? */
***************
*** 742,752 ****
                     "       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)");

--- 742,752 ----
                     "       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)");

***************
*** 1961,1967 ****
          appendPQExpBuffer(&buf, "'i',");
      if (showSeq)
          appendPQExpBuffer(&buf, "'S',");
!     if (showSystem && showTables)
          appendPQExpBuffer(&buf, "'s',");
      appendPQExpBuffer(&buf, "''");        /* dummy */
      appendPQExpBuffer(&buf, ")\n");
--- 1961,1967 ----
          appendPQExpBuffer(&buf, "'i',");
      if (showSeq)
          appendPQExpBuffer(&buf, "'S',");
!     if ((showSystem || pattern) && showTables)
          appendPQExpBuffer(&buf, "'s',");
      appendPQExpBuffer(&buf, "''");        /* dummy */
      appendPQExpBuffer(&buf, ")\n");
***************
*** 1971,1983 ****
       * 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");

      processSQLNamePattern(pset.db, &buf, pattern, true, false,
                            "n.nspname", "c.relname", NULL,
--- 1971,1986 ----
       * pg_catalog).  Otherwise, suppress system objects, including those in
       * pg_catalog and pg_toast.  (We don't want to hide temp tables though.)
       */
!     if (!pattern)
!     {
!         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");
!     }

      processSQLNamePattern(pset.db, &buf, pattern, true, false,
                            "n.nspname", "c.relname", NULL,
***************
*** 2046,2052 ****
                        gettext_noop("Modifier"),
                        gettext_noop("Check"));

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

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

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

      processSQLNamePattern(pset.db, &buf, pattern, true, false,
***************
*** 2101,2107 ****
                        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,
--- 2104,2110 ----
                        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,