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

Martin Pihlak wrote:
> Attached is a patch that modifies psql \dX commands to treat objects
> in information_schema as "system objects". This prevents them from
> showing up in \dX *.* and polluting the user objects list. This is
> especially annoying if user objects are in multiple schemas, and
> one wants to get a quick overview by running \dX *.*

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.

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

Thanks.

--
  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.204
diff -c -c -r1.204 describe.c
*** src/bin/psql/describe.c    2 Apr 2009 15:15:32 -0000    1.204
--- src/bin/psql/describe.c    2 Apr 2009 17:34:23 -0000
***************
*** 95,101 ****
                        gettext_noop("Description"));

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

      processSQLNamePattern(pset.db, &buf, pattern, true, false,
                            "n.nspname", "p.proname", NULL,
--- 95,102 ----
                        gettext_noop("Description"));

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

      processSQLNamePattern(pset.db, &buf, pattern, true, false,
                            "n.nspname", "p.proname", NULL,
***************
*** 282,288 ****
                        "      AND NOT p.proisagg\n");

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

      processSQLNamePattern(pset.db, &buf, pattern, true, false,
                            "n.nspname", "p.proname", NULL,
--- 283,290 ----
                        "      AND NOT p.proisagg\n");

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

      processSQLNamePattern(pset.db, &buf, pattern, true, false,
                            "n.nspname", "p.proname", NULL,
***************
*** 373,379 ****
          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 */
      processSQLNamePattern(pset.db, &buf, pattern, true, false,
--- 375,382 ----
          appendPQExpBuffer(&buf, "  AND t.typname !~ '^_'\n");

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

      /* Match name pattern against either internal or external name */
      processSQLNamePattern(pset.db, &buf, pattern, true, false,
***************
*** 428,434 ****
                        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,
--- 431,438 ----
                        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,
***************
*** 632,638 ****
                        gettext_noop("aggregate"));

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

      processSQLNamePattern(pset.db, &buf, pattern, true, false,
                            "n.nspname", "p.proname", NULL,
--- 636,643 ----
                        gettext_noop("aggregate"));

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

      processSQLNamePattern(pset.db, &buf, pattern, true, false,
                            "n.nspname", "p.proname", NULL,
***************
*** 655,661 ****
                        gettext_noop("function"));

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

      processSQLNamePattern(pset.db, &buf, pattern, true, false,
                            "n.nspname", "p.proname", NULL,
--- 660,667 ----
                        gettext_noop("function"));

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

      processSQLNamePattern(pset.db, &buf, pattern, true, false,
                            "n.nspname", "p.proname", NULL,
***************
*** 673,679 ****
                        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,
--- 679,686 ----
                        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,
***************
*** 691,697 ****
                        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)",
--- 698,705 ----
                        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)",
***************
*** 714,721 ****
                        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,
                            "n.nspname", "c.relname", NULL,
--- 722,731 ----
                        gettext_noop("view"),
                        gettext_noop("index"),
                        gettext_noop("sequence"));
+
       if (!showSystem && !pattern)
!          appendPQExpBuffer(&buf, "      AND n.nspname <> 'pg_catalog'\n"
!                                  "      AND n.nspname <> 'information_schema'\n");

      processSQLNamePattern(pset.db, &buf, pattern, true, false,
                            "n.nspname", "c.relname", NULL,
***************
*** 735,741 ****
                        gettext_noop("rule"));

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

      /* XXX not sure what to do about visibility rule here? */
      processSQLNamePattern(pset.db, &buf, pattern, true, false,
--- 745,752 ----
                        gettext_noop("rule"));

       if (!showSystem && !pattern)
!          appendPQExpBuffer(&buf, "      AND 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, true, false,
***************
*** 753,760 ****
                     "       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,
--- 764,773 ----
                     "       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"
!                                  "      AND n.nspname <> 'information_schema'\n");

      /* XXX not sure what to do about visibility rule here? */
      processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false,
***************
*** 809,815 ****
       "     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,
--- 822,829 ----
       "     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,
***************
*** 2013,2023 ****
      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"
!                           "  AND n.nspname !~ '^pg_toast'\n");

      processSQLNamePattern(pset.db, &buf, pattern, true, false,
                            "n.nspname", "c.relname", NULL,
--- 2027,2036 ----
      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");

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

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

      processSQLNamePattern(pset.db, &buf, pattern, true, false,
                            "n.nspname", "t.typname", NULL,
--- 2101,2108 ----
                        gettext_noop("Check"));

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

      processSQLNamePattern(pset.db, &buf, pattern, true, false,
                            "n.nspname", "t.typname", NULL,
***************
*** 2143,2149 ****
                        gettext_noop("Default?"));

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

      processSQLNamePattern(pset.db, &buf, pattern, true, false,
                            "n.nspname", "c.conname", NULL,
--- 2157,2164 ----
                        gettext_noop("Default?"));

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

      processSQLNamePattern(pset.db, &buf, pattern, true, false,
                            "n.nspname", "c.conname", NULL,