Обсуждение: psql \d* and system objects
The psql system object display issue has not been completely resolved for 8.4; see 8.4 open items: http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Open_Items#Changes So what is the proposal? Have U/S/A flags for all commands and have different system display default for each command? I think this: http://archives.postgresql.org/pgsql-hackers/2009-01/msg01443.php makes a strong argument that having a pattern control system object display will be too confusing. FYI, right now 'S' is "include-system"; we have no system-only display option. -- 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 escreveu: > The psql system object display issue has not been completely resolved > for 8.4; see 8.4 open items: > > http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Open_Items#Changes > > So what is the proposal? Have U/S/A flags for all commands and have > different system display default for each command? > [I don't read the whole thread but...] Why don't we use a DISPLAY_OBJECTS psql variable? The point in providing metacommands is facility. If we're using a psql variable, we don't need to add another character to distinguish between system, user, or both. Also, I don't think people frequently change the search class (system, user, or both) so I don't buy the argument that it is more easier to type another letter each time than typing a '\set FOO bar' once per dozens of commands. -- Euler Taveira de Oliveira http://www.timbira.com/
Euler Taveira de Oliveira wrote: > Bruce Momjian escreveu: > > The psql system object display issue has not been completely resolved > > for 8.4; see 8.4 open items: > > > > http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Open_Items#Changes > > > > So what is the proposal? Have U/S/A flags for all commands and have > > different system display default for each command? > > > [I don't read the whole thread but...] Why don't we use a DISPLAY_OBJECTS psql > variable? The point in providing metacommands is facility. If we're using a > psql variable, we don't need to add another character to distinguish between > system, user, or both. Also, I don't think people frequently change the search > class (system, user, or both) so I don't buy the argument that it is more > easier to type another letter each time than typing a '\set FOO bar' once per > dozens of commands. I think the problem is that people often use \dt and then \df or \dT, and odds are they would want different system-visible behavior for those. I think we could have a setting that would choose a default of 'user-only', or 'all', but I still think we would need the ability to override it at command time. -- 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 Sat, Mar 28, 2009 at 1:25 AM, Euler Taveira de Oliveira <euler@timbira.com> wrote: > Bruce Momjian escreveu: >> The psql system object display issue has not been completely resolved >> for 8.4; see 8.4 open items: >> >> http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Open_Items#Changes >> >> So what is the proposal? Have U/S/A flags for all commands and have >> different system display default for each command? >> > [I don't read the whole thread but...] Why don't we use a DISPLAY_OBJECTS psql > variable? The point in providing metacommands is facility. If we're using a > psql variable, we don't need to add another character to distinguish between > system, user, or both. Also, I don't think people frequently change the search > class (system, user, or both) so I don't buy the argument that it is more > easier to type another letter each time than typing a '\set FOO bar' once per > dozens of commands. I think you should reconsider your non-buying of that argument. That would be really, really annoying for me. Most of the time I want to look at a user object. But every now and then I want to look at a system object. Having to type two commands to get that would be completely annoying. ...Robert
On Sat, Mar 28, 2009 at 4:25 PM, Bruce Momjian <bruce@momjian.us> wrote: > Euler Taveira de Oliveira wrote: >> Bruce Momjian escreveu: >> > The psql system object display issue has not been completely resolved >> > for 8.4; see 8.4 open items: >> > >> > http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Open_Items#Changes >> > >> > So what is the proposal? Have U/S/A flags for all commands and have >> > different system display default for each command? >> > >> [I don't read the whole thread but...] Why don't we use a DISPLAY_OBJECTS psql >> variable? The point in providing metacommands is facility. If we're using a >> psql variable, we don't need to add another character to distinguish between >> system, user, or both. Also, I don't think people frequently change the search >> class (system, user, or both) so I don't buy the argument that it is more >> easier to type another letter each time than typing a '\set FOO bar' once per >> dozens of commands. > > I think the problem is that people often use \dt and then \df or \dT, > and odds are they would want different system-visible behavior for > those. I think we could have a setting that would choose a default of > 'user-only', or 'all', but I still think we would need the ability to > override it at command time. Yeah, I like this. I think we could even make it a little more fine-tuned. For example you might have an option whose values is a list of object types for which system tables are excluded by default, with * meaning all. So you could have: tsv = exclude system tables, sequences, and views by default tsvf = exclude system tables, sequences, views, and functions by default * = don't show any system objects by default empty string = show all system objects by default I don't really care about the details, just suggesting it would be useful to be able to tune by object type. ...Robert
Robert Haas <robertmhaas@gmail.com> writes:
> I think you should reconsider your non-buying of that argument. That
> would be really, really annoying for me. Most of the time I want to
> look at a user object. But every now and then I want to look at a
> system object.
I still think that this argument is fundamentally misguided, at least
with respect to the case where a pattern is given. If I say "\df sin"
I should see the definition of sin(). I do not care in the slightest
whether it's a system or user function.
This behavior (seen in HEAD) is absolutely bogus:
regression=# select sin(0.5); sin
-------------------0.479425538604203
(1 row)
regression=# \df sin List of functionsSchema | Name | Result data type | Argument data types
--------+------+------------------+---------------------
(0 rows)
But it gets worse:
regression=# create function sin(float8) returns float8 as
regression-# 'select $1 + 1' language sql;
CREATE FUNCTION
regression=# select sin(0.5); sin
-------------------0.479425538604203 -- user might be expecting 1.5 here
(1 row)
regression=# \df sin List of functionsSchema | Name | Result data type | Argument data types
--------+------+------------------+---------------------
(0 rows)
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.
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.
regards, tom lane
Tom Lane wrote: > 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. > > 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. You can say it is broken, but what is your proposal? -- 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 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
Robert Haas <robertmhaas@gmail.com> writes:
> That still has the problem that "\df a*" is horribly inconsistent with
> "\df". It might be reasonable to assume that if a name without
> wildcards is given to any \d command, it should display whatever
> object it finds, user or system - but I can't see doing it for any
> wildcard at all.
Why not? Seems "horribly inconsistent" to me to treat those cases
differently.
It seems entirely explainable to me to say that "if you specify
no pattern, the default behavior is to list all non-system functions".
Where the patch went wrong is in fooling with the behavior when a
pattern is specified.
regards, tom lane
On Mon, Mar 30, 2009 at 10:13 AM, Bruce Momjian <bruce@momjian.us> wrote: > 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.) That still has the problem that "\df a*" is horribly inconsistent with "\df". It might be reasonable to assume that if a name without wildcards is given to any \d command, it should display whatever object it finds, user or system - but I can't see doing it for any wildcard at all. ...Robert
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,
On Mon, Mar 30, 2009 at 10:29 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> That still has the problem that "\df a*" is horribly inconsistent with >> "\df". It might be reasonable to assume that if a name without >> wildcards is given to any \d command, it should display whatever >> object it finds, user or system - but I can't see doing it for any >> wildcard at all. > > Why not? Seems "horribly inconsistent" to me to treat those cases > differently. > > It seems entirely explainable to me to say that "if you specify > no pattern, the default behavior is to list all non-system functions". > Where the patch went wrong is in fooling with the behavior when a > pattern is specified. Well, what am I supposed to do when I have a large number of user-defined functions and want only the ones whose names start with a? What I like about this patch is that you can search by function (or aggregate, etc.) name, and, independently of whether you search or display all, you can include or exclude system functions. I think that's a good design. Now, we can argue about what the default behavior should be, but at the very minimum I think all combinations of <search pattern, no search pattern> and <user-defined only, all> should be possible with some relatively small number of keystrokes. ...Robert
Robert Haas wrote: > On Mon, Mar 30, 2009 at 10:13 AM, Bruce Momjian <bruce@momjian.us> wrote: > > 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.) > > That still has the problem that "\df a*" is horribly inconsistent with > "\df". It might be reasonable to assume that if a name without > wildcards is given to any \d command, it should display whatever > object it finds, user or system - but I can't see doing it for any > wildcard at all. I think you are re-iterating the URL I referenced when I started this thread: http://archives.postgresql.org/pgsql-hackers/2009-01/msg01443.php I think the big question is whether the inconsistency (pattern implies 'S') is worth accepting for greater usability. -- 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 Mon, Mar 30, 2009 at 10:52 AM, Bruce Momjian <bruce@momjian.us> wrote: > Robert Haas wrote: >> On Mon, Mar 30, 2009 at 10:13 AM, Bruce Momjian <bruce@momjian.us> wrote: >> > 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.) >> >> That still has the problem that "\df a*" is horribly inconsistent with >> "\df". It might be reasonable to assume that if a name without >> wildcards is given to any \d command, it should display whatever >> object it finds, user or system - but I can't see doing it for any >> wildcard at all. > > I think you are re-iterating the URL I referenced when I started this > thread: > > http://archives.postgresql.org/pgsql-hackers/2009-01/msg01443.php > > I think the big question is whether the inconsistency (pattern implies > 'S') is worth accepting for greater usability. The inconsistency would be less objectionable if there were a way to override it when it's not what you want. ...Robert
Hi, Le 30 mars 09 à 16:52, Bruce Momjian a écrit : > I think the big question is whether the inconsistency (pattern implies > 'S') is worth accepting for greater usability. My answer is yes, please, definitely, go for it. We don't need idiot-proof easy to remember semantics, we need useful ones... The former category is already taken care of by some other open source database software, have I been told... What about a mail with some content? Look, a user-level proposal draft! :) \dt lists user tables only \dtS lists system tables only \dt pattern lists matching user andsystem tables \dfS pattern lists matching system tables only \df lists user functions only \dfS lists system functions only \df pattern lists matching functions asper backend resolution (search_path) \dfS pattern lists matching system functions only, bypass search_path? I think it's kind of easy to decline the concept, and I don't think this will make unanimity. But what about dropping the consistency idea (Tom is saying that it proved to be a damn bad one already) and from there defining a usable tool? Regards, -- dim
On Mon, Mar 30, 2009 at 09:59:41PM +0200, Dimitri Fontaine wrote: > Hi, > > Le 30 mars 09 à 16:52, Bruce Momjian a écrit : >> I think the big question is whether the inconsistency (pattern implies >> 'S') is worth accepting for greater usability. > > > My answer is yes, please, definitely, go for it. > We don't need idiot-proof easy to remember semantics, we need useful > ones... The former category is already taken care of by some other open > source database software, have I been told... > > What about a mail with some content? Look, a user-level proposal draft! > :) > \dt lists user tables only > \dtS lists system tables only All of the S ones should probably mean, "include system objects" rather than "only system objects." Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Monday 30 March 2009 10:52:47 Bruce Momjian wrote: > Robert Haas wrote: > > On Mon, Mar 30, 2009 at 10:13 AM, Bruce Momjian <bruce@momjian.us> wrote: > > > 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.) > > > > That still has the problem that "\df a*" is horribly inconsistent with > > "\df". It might be reasonable to assume that if a name without > > wildcards is given to any \d command, it should display whatever > > object it finds, user or system - but I can't see doing it for any > > wildcard at all. > > I think you are re-iterating the URL I referenced when I started this > thread: > > http://archives.postgresql.org/pgsql-hackers/2009-01/msg01443.php > > I think the big question is whether the inconsistency (pattern implies > 'S') is worth accepting for greater usability. > Actually I find the iconsistency 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 sin has to become \dfS sin, but this maintains consistency (one of the issues I've been running into is finding something via tab completion that I can't actually see in the output listing, which is very annoying). Further, should you create a function called sin and do \df sin, you should only see your function. This can lead to confusion where you are calling a built in function but you dont see it in \df, or you see a different function in \df, but I haven't run into that case yet; in the work I've been doing in 8.4, the above is how I've been wanting it to work, and swapping to \df* to see system hasn't been much of an issue. BTW, I often do \df *.sin when looking for a function I'm not sure of where it lives exactly; this being on current (<=8.3) releases, but many of the systems involve a fair number of schemas, that might not be a common practice, but perhaps should be. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.com
Robert Treat wrote: > On Monday 30 March 2009 10:52:47 Bruce Momjian wrote: > > Robert Haas wrote: > > > On Mon, Mar 30, 2009 at 10:13 AM, Bruce Momjian <bruce@momjian.us> wrote: > > > > 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.) > > > > > > That still has the problem that "\df a*" is horribly inconsistent with > > > "\df". It might be reasonable to assume that if a name without > > > wildcards is given to any \d command, it should display whatever > > > object it finds, user or system - but I can't see doing it for any > > > wildcard at all. > > > > I think you are re-iterating the URL I referenced when I started this > > thread: > > > > http://archives.postgresql.org/pgsql-hackers/2009-01/msg01443.php > > > > I think the big question is whether the inconsistency (pattern implies > > 'S') is worth accepting for greater usability. > > > > Actually I find the iconsistency 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 This is how CVS HEAD works now. > For those that want system only, do > \d*S pg_catalog. > ( if you want to argue temp/toast, adjust the search accordingly) Yep, same. > 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 > sin has to become \dfS sin, but this maintains consistency (one of the issues > I've been running into is finding something via tab completion that I can't > actually see in the output listing, which is very annoying). > Further, should you create a function called sin and do \df sin, you should > only see your function. This can lead to confusion where you are calling a > built in function but you dont see it in \df, or you see a different function > in \df, but I haven't run into that case yet; in the work I've been doing in > 8.4, the above is how I've been wanting it to work, and swapping to \df* to > see system hasn't been much of an issue. I agree, Tom's case where he creates public.sin() is not going to show without specifying public.sin. In fact Tom requested months ago that psql follow the search_path in showing objects, so it is hard to see how he can consider it wrong now. > BTW, I often do \df *.sin when looking for a function I'm not sure of where it > lives exactly; this being on current (<=8.3) releases, but many of the > systems involve a fair number of schemas, that might not be a common > practice, but perhaps should be. The big question is whether '\d* pattern' should exclude looking for system object, assuming search_path would show the system object, so '\df cos' would show a function. I think usability supports this behavior. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
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 sintest=> \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--------+------+------------------+---------------------(0rows) 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. +
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,