Обсуждение: psql \d commands and information_schema
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 *.*
regards,
Martin
*** a/src/bin/psql/describe.c
--- b/src/bin/psql/describe.c
***************
*** 23,28 ****
--- 23,34 ----
#include "variables.h"
+ /* Macros for exluding system objects from \d commands */
+ #define NOT_SYSTEM_OBJECT " n.nspname NOT IN ('pg_catalog', 'information_schema')\n"
+ #define AND_NOT_SYSTEM_OBJECT " AND" NOT_SYSTEM_OBJECT
+ #define WHERE_NOT_SYSTEM_OBJECT " WHERE" NOT_SYSTEM_OBJECT
+
+
static bool describeOneTableDetails(const char *schemaname,
const char *relationname,
const char *oid,
***************
*** 95,101 **** describeAggregates(const char *pattern, bool verbose, bool showSystem)
gettext_noop("Description"));
if (!showSystem)
! appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
--- 101,107 ----
gettext_noop("Description"));
if (!showSystem)
! appendPQExpBuffer(&buf, AND_NOT_SYSTEM_OBJECT);
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
***************
*** 282,288 **** describeFunctions(const char *pattern, bool verbose, bool showSystem)
" AND NOT p.proisagg\n");
if (!showSystem)
! appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
--- 288,294 ----
" AND NOT p.proisagg\n");
if (!showSystem)
! appendPQExpBuffer(&buf, AND_NOT_SYSTEM_OBJECT);
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
***************
*** 373,379 **** describeTypes(const char *pattern, bool verbose, bool showSystem)
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 */
processSQLNamePattern(pset.db, &buf, pattern, true, false,
--- 379,385 ----
appendPQExpBuffer(&buf, " AND t.typname !~ '^_'\n");
if (!showSystem)
! appendPQExpBuffer(&buf, AND_NOT_SYSTEM_OBJECT);
/* Match name pattern against either internal or external name */
processSQLNamePattern(pset.db, &buf, pattern, true, false,
***************
*** 428,434 **** describeOperators(const char *pattern, bool showSystem)
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,
--- 434,440 ----
gettext_noop("Description"));
if (!showSystem)
! appendPQExpBuffer(&buf, WHERE_NOT_SYSTEM_OBJECT);
processSQLNamePattern(pset.db, &buf, pattern, !showSystem, true,
"n.nspname", "o.oprname", NULL,
***************
*** 632,638 **** objectDescription(const char *pattern, bool showSystem)
gettext_noop("aggregate"));
if (!showSystem)
! appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
--- 638,644 ----
gettext_noop("aggregate"));
if (!showSystem)
! appendPQExpBuffer(&buf, AND_NOT_SYSTEM_OBJECT);
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
***************
*** 655,661 **** objectDescription(const char *pattern, bool showSystem)
gettext_noop("function"));
if (!showSystem)
! appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
--- 661,667 ----
gettext_noop("function"));
if (!showSystem)
! appendPQExpBuffer(&buf, AND_NOT_SYSTEM_OBJECT);
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
***************
*** 673,679 **** objectDescription(const char *pattern, bool showSystem)
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,
--- 679,685 ----
gettext_noop("operator"));
if (!showSystem)
! appendPQExpBuffer(&buf, WHERE_NOT_SYSTEM_OBJECT);
processSQLNamePattern(pset.db, &buf, pattern, !showSystem, false,
"n.nspname", "o.oprname", NULL,
***************
*** 691,697 **** objectDescription(const char *pattern, bool showSystem)
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)",
--- 697,703 ----
gettext_noop("data type"));
if (!showSystem)
! appendPQExpBuffer(&buf, WHERE_NOT_SYSTEM_OBJECT);
processSQLNamePattern(pset.db, &buf, pattern, !showSystem, false,
"n.nspname", "pg_catalog.format_type(t.oid, NULL)",
***************
*** 715,721 **** objectDescription(const char *pattern, bool showSystem)
gettext_noop("index"),
gettext_noop("sequence"));
if (!showSystem)
! appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "c.relname", NULL,
--- 721,727 ----
gettext_noop("index"),
gettext_noop("sequence"));
if (!showSystem)
! appendPQExpBuffer(&buf, AND_NOT_SYSTEM_OBJECT);
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "c.relname", NULL,
***************
*** 735,741 **** objectDescription(const char *pattern, bool showSystem)
gettext_noop("rule"));
if (!showSystem)
! 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,
--- 741,747 ----
gettext_noop("rule"));
if (!showSystem)
! appendPQExpBuffer(&buf, AND_NOT_SYSTEM_OBJECT);
/* XXX not sure what to do about visibility rule here? */
processSQLNamePattern(pset.db, &buf, pattern, true, false,
***************
*** 754,760 **** objectDescription(const char *pattern, bool showSystem)
" 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,
--- 760,766 ----
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n",
gettext_noop("trigger"));
if (!showSystem)
! appendPQExpBuffer(&buf, WHERE_NOT_SYSTEM_OBJECT);
/* XXX not sure what to do about visibility rule here? */
processSQLNamePattern(pset.db, &buf, pattern, !showSystem, false,
***************
*** 809,815 **** describeTableDetails(const char *pattern, bool verbose, bool showSystem)
" 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,
--- 815,821 ----
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");
if (!showSystem)
! appendPQExpBuffer(&buf, WHERE_NOT_SYSTEM_OBJECT);
processSQLNamePattern(pset.db, &buf, pattern, !showSystem, false,
"n.nspname", "c.relname", NULL,
***************
*** 2018,2024 **** listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
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");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
--- 2024,2030 ----
if (!showSystem)
/* Exclude system and pg_toast objects, but show temp tables */
appendPQExpBuffer(&buf,
! AND_NOT_SYSTEM_OBJECT
" AND n.nspname !~ '^pg_toast'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
***************
*** 2090,2096 **** listDomains(const char *pattern, bool showSystem)
gettext_noop("Check"));
if (!showSystem)
! appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "t.typname", NULL,
--- 2096,2102 ----
gettext_noop("Check"));
if (!showSystem)
! appendPQExpBuffer(&buf, AND_NOT_SYSTEM_OBJECT);
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "t.typname", NULL,
***************
*** 2145,2151 **** listConversions(const char *pattern, bool showSystem)
gettext_noop("Default?"));
if (!showSystem)
! appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "c.conname", NULL,
--- 2151,2157 ----
gettext_noop("Default?"));
if (!showSystem)
! appendPQExpBuffer(&buf, AND_NOT_SYSTEM_OBJECT);
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "c.conname", NULL,
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,
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");
Martin Pihlak <martin.pihlak@gmail.com> writes:
> 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 don't find this to be a pressing problem. If the user has lots of
schemas, they probably have lots of objects too, and are unlikely to
need such a thing. Moreover, with lots of schemas the behavior of the
search_path enters into it too; a simple U switch isn't going to provide
a full answer.
regards, tom lane
Tom Lane wrote: > I don't find this to be a pressing problem. If the user has lots of > schemas, they probably have lots of objects too, and are unlikely to > need such a thing. Current behaviour makes it impossible to get a quick overview of all the user defined objects. And it doesn't really matter what the number of schemas is -- it gets messy for even small number of schemas and objects. Lets assume 2 user tables in schemas "public" and "foo". \dt *.* will give: List of relations Schema | Name | Type | Owner --------------------+-------------------------+-------+---------foo | t2 | table | martinpinformation_schema| sql_features | table | martinp ...pg_catalog | pg_aggregate | table | martinp ...public | t1 | table | martinp (51 rows) This is a lot of irrelevant stuff the user has to filter out. It is much worse with functions -- \df *.* results in 1900+ functions that I usually don't want to see. The alternative is to perform a \dn first and then loop through that (this is the annoyance the U switch would remove). > search_path enters into it too; a simple U switch isn't going to provide > a full answer. > For our needs I wouldn't really consider using search_path for anything but temporary hacks. However, a psql variable that specifies a list of name patterns to be excluded from describe, could be useful. Something along the lines of: \set DESCRIBE_EXCLUDE_PATTERNS 'pg_catalog.*, information_schema.*, ...' This could be then customized to each site's needs -- add pgq, slony, etc. and put to .psqlrc. It is questionable whether the filter should be applied to default \dX (override with S to describe all). Maybe it'd be better to introduce an extra switch that applies the filters. I just noticed that something similar was already suggested by Euler in http://archives.postgresql.org/message-id/49CDB4E0.8030603@timbira.com regards, Martin
Martin Pihlak <martin.pihlak@gmail.com> writes:
> Tom Lane wrote:
>> I don't find this to be a pressing problem. If the user has lots of
>> schemas, they probably have lots of objects too, and are unlikely to
>> need such a thing.
> Current behaviour makes it impossible to get a quick overview of all the
> user defined objects. And it doesn't really matter what the number of
> schemas is -- it gets messy for even small number of schemas and objects.
Well, if they're all in your search_path then plain old \df will do
fine. If they're not in your search path then I think it gets pretty
questionable whether they're "user defined" in a real sense. It seems
more likely that you've got a pile of modules loaded, and which of those
modules is "user defined" for your immediate purposes is something that
psql can't hope to intuit.
In short I'm still not convinced that there's much use-case for a
simple "U" switch.
> For our needs I wouldn't really consider using search_path for anything
> but temporary hacks. However, a psql variable that specifies a list of
> name patterns to be excluded from describe, could be useful. Something
> along the lines of:
> \set DESCRIBE_EXCLUDE_PATTERNS 'pg_catalog.*, information_schema.*, ...'
Possibly something like this could be useful. But I'd like to see it
designed in conjunction with the "real module facility" that we keep
hoping for, because I think a situation with a number of modules loaded
is going to be exactly where you want some flexible filtering.
regards, tom lane
Tom Lane wrote: > Well, if they're all in your search_path then plain old \df will do > fine. If they're not in your search path then I think it gets pretty > questionable whether they're "user defined" in a real sense. It seems > more likely that you've got a pile of modules loaded, and which of those > modules is "user defined" for your immediate purposes is something that > psql can't hope to intuit. > I my environment schemas are used for namespace separation, so it doesn't make much sense to use search_path to pull everything back into a single namespace. Might as well use public for everything then. And these are not really modules, just ordinary user objects in separate namespaces. The main benefit of the U switch is that it enables to get a quick overview of whats deployed to the database. At the moment this is not possible as the *.* listings are polluted with system objects. PS. The original \dfU suggestion was made by you in http://archives.postgresql.org/message-id/28027.1206976227@sss.pgh.pa.us. And seems, it didn't receive too much negative feedback then. regards, Martin
Martin Pihlak wrote: > Tom Lane wrote: > > Well, if they're all in your search_path then plain old \df will do > > fine. If they're not in your search path then I think it gets pretty > > questionable whether they're "user defined" in a real sense. It seems > > more likely that you've got a pile of modules loaded, and which of those > > modules is "user defined" for your immediate purposes is something that > > psql can't hope to intuit. > > > > I my environment schemas are used for namespace separation, so it doesn't > make much sense to use search_path to pull everything back into a single > namespace. Might as well use public for everything then. And these are not > really modules, just ordinary user objects in separate namespaces. > > The main benefit of the U switch is that it enables to get a quick overview > of whats deployed to the database. At the moment this is not possible as the > *.* listings are polluted with system objects. > > PS. The original \dfU suggestion was made by you in > http://archives.postgresql.org/message-id/28027.1206976227@sss.pgh.pa.us. > And seems, it didn't receive too much negative feedback then. We already had a huge discussion over 'S' and I think we did as good as we can. I think we risk overcomplicating the API by adding U, but we can revisit this in 8.5 once we get more feedback from users. But a larger issue is that if we try to make everyone happy with the psql API, the API will be unusablely complex. We have just not seen enough demand for U yet. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On Wed, Apr 8, 2009 at 3:49 PM, Bruce Momjian <bruce@momjian.us> wrote: > We already had a huge discussion over 'S' and I think we did as good as > we can. I think we risk overcomplicating the API by adding U, but we > can revisit this in 8.5 once we get more feedback from users. I think we'll need to take stock before 8.4 actually. Tom's pointed out a whole pile of problems with the current approach and I'm becoming convinced he's right. I know I was one of the proponents of the change but I didn't realize how bad the problems were. As I understand his proposal is that \df with no pattern could list all user functions but \df <pattern> should always follow the search_path and show the same functions that would actually be called. One possibility for reducing clutter would be moving a whole slew of the system functions which are never intended for users to call explicitly to a different schema which isn't implicitly added to search_path. That would at least get all the RI functions, bt procs, maybe even the operator functions out of the way. -- greg
Greg Stark <stark@enterprisedb.com> writes: > On Wed, Apr 8, 2009 at 3:49 PM, Bruce Momjian <bruce@momjian.us> wrote: >> We already had a huge discussion over 'S' and I think we did as good as >> we can. �I think we risk overcomplicating the API by adding U, but we >> can revisit this in 8.5 once we get more feedback from users. > I think we'll need to take stock before 8.4 actually. Tom's pointed > out a whole pile of problems with the current approach and I'm > becoming convinced he's right. I know I was one of the proponents of > the change but I didn't realize how bad the problems were. > As I understand his proposal is that \df with no pattern could list > all user functions but \df <pattern> should always follow the > search_path and show the same functions that would actually be called. Uh, that change got applied last week ... http://archives.postgresql.org/pgsql-committers/2009-04/msg00014.php > One possibility for reducing clutter would be moving a whole slew of > the system functions which are never intended for users to call > explicitly to a different schema which isn't implicitly added to > search_path. That would at least get all the RI functions, bt procs, > maybe even the operator functions out of the way. Perhaps, but is it really important? I haven't noticed that those things were cluttering my \df searches anyway. BTW, I hesitate to mention this and perhaps upset a fragile consensus, but should we remove the special-case code in \df that tries to hide I/O functions by excluding functions that take or return cstring? I think that its value has largely disappeared given the new overall behavior. regards, tom lane
Tom Lane escribió: > BTW, I hesitate to mention this and perhaps upset a fragile consensus, > but should we remove the special-case code in \df that tries to hide I/O > functions by excluding functions that take or return cstring? I think > that its value has largely disappeared given the new overall behavior. +1 -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera wrote: > Tom Lane escribi?: > > > BTW, I hesitate to mention this and perhaps upset a fragile consensus, > > but should we remove the special-case code in \df that tries to hide I/O > > functions by excluding functions that take or return cstring? I think > > that its value has largely disappeared given the new overall behavior. > > +1 Agreed. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes:
> Alvaro Herrera wrote:
>> Tom Lane escribi?:
>>> BTW, I hesitate to mention this and perhaps upset a fragile consensus,
>>> but should we remove the special-case code in \df that tries to hide I/O
>>> functions by excluding functions that take or return cstring? I think
>>> that its value has largely disappeared given the new overall behavior.
>>
>> +1
> Agreed.
Done.
regards, tom lane