Re: psql \d commands and information_schema - Mailing list pgsql-hackers

Bruce Momjian wrote:
> I have applied a simplified version of your patch, attached, that adds
> just  a "AND <>" line to the query;  I was a little concerned that IN
> might affect performance, and the macros seemed kind of complicated.
>

Thanks.

> Also, since my patch this morning any pattern will also trigger
> information_schema lookups, not just 'S'.
>

Hmm, this is a problem -- "\dX *.*" now shows all objects, and there is
no way to list only user objects. This is especially a problem if user
objects are scattered in different schemas.

I'd suggest that the U option to be reintroduced for the purpouse to describe
only user objects. One possible solution is to modifiy exec_command so that
show_system is set to true if S or pattern present, and false if U is
specified.

Proposed patch attached.

regards,
Martin




*** a/src/bin/psql/command.c
--- b/src/bin/psql/command.c
***************
*** 334,340 **** exec_command(const char *cmd,
                                           OT_NORMAL, NULL, true);

          show_verbose = strchr(cmd, '+') ? true : false;
!         show_system = strchr(cmd, 'S') ? true : false;

          switch (cmd[1])
          {
--- 334,348 ----
                                           OT_NORMAL, NULL, true);

          show_verbose = strchr(cmd, '+') ? true : false;
!
!         /*
!          * Show system objects if S or pattern present. Use U to
!          * describe only user objects.
!          */
!         if ((strchr(cmd, 'S') || pattern) && !strchr(cmd, 'U'))
!             show_system = true;
!         else
!             show_system = false;

          switch (cmd[1])
          {
*** a/src/bin/psql/describe.c
--- b/src/bin/psql/describe.c
***************
*** 94,100 **** describeAggregates(const char *pattern, bool verbose, bool showSystem)
                        "WHERE p.proisagg\n",
                        gettext_noop("Description"));

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

--- 94,100 ----
                        "WHERE p.proisagg\n",
                        gettext_noop("Description"));

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

***************
*** 282,288 **** describeFunctions(const char *pattern, bool verbose, bool showSystem)
                        "      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"
                                   "      AND n.nspname <> 'information_schema'\n");

--- 282,288 ----
                        "      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"
                                   "      AND n.nspname <> 'information_schema'\n");

***************
*** 374,380 **** describeTypes(const char *pattern, bool verbose, bool showSystem)
      else
          appendPQExpBuffer(&buf, "  AND t.typname !~ '^_'\n");

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

--- 374,380 ----
      else
          appendPQExpBuffer(&buf, "  AND t.typname !~ '^_'\n");

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

***************
*** 430,440 **** describeOperators(const char *pattern, bool showSystem)
                        gettext_noop("Result type"),
                        gettext_noop("Description"));

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

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

--- 430,440 ----
                        gettext_noop("Result type"),
                        gettext_noop("Description"));

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

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

***************
*** 635,641 **** objectDescription(const char *pattern, bool showSystem)
                        "  WHERE p.proisagg\n",
                        gettext_noop("aggregate"));

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

--- 635,641 ----
                        "  WHERE p.proisagg\n",
                        gettext_noop("aggregate"));

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

***************
*** 659,665 **** objectDescription(const char *pattern, bool showSystem)
                        "      AND NOT p.proisagg\n",
                        gettext_noop("function"));

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

--- 659,665 ----
                        "      AND NOT p.proisagg\n",
                        gettext_noop("function"));

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

***************
*** 678,688 **** objectDescription(const char *pattern, bool showSystem)
      "       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"
                                   "      AND n.nspname <> 'information_schema'\n");

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

--- 678,688 ----
      "       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"
                                   "      AND n.nspname <> 'information_schema'\n");

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

***************
*** 697,707 **** objectDescription(const char *pattern, bool showSystem)
      "       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"
                                   "      AND n.nspname <> 'information_schema'\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)");
--- 697,707 ----
      "       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"
                                   "      AND n.nspname <> 'information_schema'\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)");
***************
*** 723,729 **** objectDescription(const char *pattern, bool showSystem)
                        gettext_noop("index"),
                        gettext_noop("sequence"));

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

--- 723,729 ----
                        gettext_noop("index"),
                        gettext_noop("sequence"));

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

***************
*** 744,750 **** objectDescription(const char *pattern, bool showSystem)
                        "  WHERE r.rulename != '_RETURN'\n",
                        gettext_noop("rule"));

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

--- 744,750 ----
                        "  WHERE r.rulename != '_RETURN'\n",
                        gettext_noop("rule"));

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

***************
*** 765,776 **** objectDescription(const char *pattern, bool showSystem)
      "       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"
                                   "      AND n.nspname <> 'information_schema'\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)");

--- 765,776 ----
      "       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"
                                   "      AND n.nspname <> 'information_schema'\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)");

***************
*** 821,831 **** describeTableDetails(const char *pattern, bool verbose, bool showSystem)
                        "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"
                                   "      AND n.nspname <> 'information_schema'\n");

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

--- 821,831 ----
                        "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"
                                   "      AND n.nspname <> 'information_schema'\n");

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

***************
*** 2027,2033 **** listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
      appendPQExpBuffer(&buf, "''");        /* dummy */
      appendPQExpBuffer(&buf, ")\n");

!      if (!showSystem && !pattern)
           appendPQExpBuffer(&buf, "      AND n.nspname <> 'pg_catalog'\n"
                                   "      AND n.nspname <> 'information_schema'\n"
                                  "      AND n.nspname !~ '^pg_toast'\n");
--- 2027,2033 ----
      appendPQExpBuffer(&buf, "''");        /* dummy */
      appendPQExpBuffer(&buf, ")\n");

!      if (!showSystem)
           appendPQExpBuffer(&buf, "      AND n.nspname <> 'pg_catalog'\n"
                                   "      AND n.nspname <> 'information_schema'\n"
                                  "      AND n.nspname !~ '^pg_toast'\n");
***************
*** 2100,2106 **** listDomains(const char *pattern, bool showSystem)
                        gettext_noop("Modifier"),
                        gettext_noop("Check"));

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

--- 2100,2106 ----
                        gettext_noop("Modifier"),
                        gettext_noop("Check"));

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

***************
*** 2156,2162 **** listConversions(const char *pattern, bool showSystem)
                        gettext_noop("yes"), gettext_noop("no"),
                        gettext_noop("Default?"));

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

--- 2156,2162 ----
                        gettext_noop("yes"), gettext_noop("no"),
                        gettext_noop("Default?"));

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