Re: [HACKERS] psql \l to accept patterns
On Wed, 2013-01-30 at 00:20 +0900, Satoshi Nagayasu wrote: It seems working well with the latest git master. I think it's good enough to be committed. Committed, thanks. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql \l to accept patterns
Hi, I have tried this patch. https://commitfest.postgresql.org/action/patch_view?id=1051 2013/01/29 14:48, Peter Eisentraut wrote: On Mon, 2013-01-07 at 07:14 -0500, Peter Eisentraut wrote: Here is a patch for psql's \l command to accept patterns, like \d commands do. While at it, I also added an S option to show system objects and removed system objects from the default display. This might be a bit controversial, but it's how it was decided some time ago that the \d commands should act. Most people didn't like the S option, so here is a revised patch that just adds the pattern support. It seems working well with the latest git master. I think it's good enough to be committed. BTW, is there any good place to put new regression test for the psql command? I couldn't find it out. Any comment or suggestion? Regards, -- Satoshi Nagayasu sn...@uptime.jp Uptime Technologies, LLC. http://www.uptime.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql \l to accept patterns
Satoshi Nagayasu sn...@uptime.jp writes: On Mon, 2013-01-07 at 07:14 -0500, Peter Eisentraut wrote: Here is a patch for psql's \l command to accept patterns, like \d BTW, is there any good place to put new regression test for the psql command? I couldn't find it out. As far as a test for this specific feature goes, I'd be against adding one, because it'd be likely to result in random failures in make installcheck mode, depending on what other databases are in the installation. More generally, we've tended to put tests of \d-style psql features together with the relevant backend-side tests. The proposed patch to add \gset adds a separate regression test file specifically for psql. I've been debating whether that was worth committing; but if there's near-term interest in adding any more tests for psql features that aren't closely connected to backend features, maybe it's worth having such a file. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql \l to accept patterns
(2013/01/30 0:34), Tom Lane wrote: Satoshi Nagayasu sn...@uptime.jp writes: On Mon, 2013-01-07 at 07:14 -0500, Peter Eisentraut wrote: Here is a patch for psql's \l command to accept patterns, like \d BTW, is there any good place to put new regression test for the psql command? I couldn't find it out. As far as a test for this specific feature goes, I'd be against adding one, because it'd be likely to result in random failures in make installcheck mode, depending on what other databases are in the installation. More generally, we've tended to put tests of \d-style psql features together with the relevant backend-side tests. Yes, I think so too. First of all, I was looking for some regression tests for CREATE/ALTER/DROP DATABASE commands, but I couldn't find them in the test/regress/sql/ directory. So, I asked the question. I guess these database tests are in pg_regress.c. Right? The proposed patch to add \gset adds a separate regression test file specifically for psql. I've been debating whether that was worth committing; but if there's near-term interest in adding any more tests for psql features that aren't closely connected to backend features, maybe it's worth having such a file. Personally, I'm interested in having regression tests whatever the target is, because software tends to be more complicated. So, if we reach consensus to have dedicated tests for the psql command (or other client-side commands), I wish to contribute to it. Regards, -- Satoshi Nagayasu sn...@uptime.jp Uptime Technologies, LLC. http://www.uptime.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql \l to accept patterns
Satoshi Nagayasu sn...@uptime.jp writes: First of all, I was looking for some regression tests for CREATE/ALTER/DROP DATABASE commands, but I couldn't find them in the test/regress/sql/ directory. So, I asked the question. I guess these database tests are in pg_regress.c. Right? Yeah, we don't bother with explicit tests of CREATE/DROP DATABASE because that's inherently tested by creating/replacing the regression database(s). And these actions are expensive enough that I'm not eager to add several more of them to the test sequence without darn good reason. I'm not sure how much of ALTER DATABASE's functionality we're testing, though as you say pg_regress itself does some of that. It might be reasonable to add some more tests of ALTER cases. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql \l to accept patterns
On Mon, 2013-01-07 at 07:14 -0500, Peter Eisentraut wrote:
Here is a patch for psql's \l command to accept patterns, like \d
commands do. While at it, I also added an S option to show system
objects and removed system objects from the default display. This might
be a bit controversial, but it's how it was decided some time ago that
the \d commands should act.
Most people didn't like the S option, so here is a revised patch that
just adds the pattern support.
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 4c87d8a..8d0095e 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1689,12 +1689,13 @@ titleMeta-Commands/title
varlistentry
-termliteral\l/literal (or literal\list/literal)/term
-termliteral\l+/literal (or literal\list+/literal)/term
+termliteral\l[+]/literal or literal\list[+]/literal/term
listitem
para
-List the names, owners, character set encodings, and access privileges
-of all the databases in the server.
+List the databases in the server and show their names, owners,
+character set encodings, and access privileges.
+If replaceable class=parameterpattern/replaceable is specified,
+only databases whose names match the pattern are listed.
If literal+/literal is appended to the command name, database
sizes, default tablespaces, and descriptions are also displayed.
(Size information is only available for databases that the current
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 20c45e2..e40f8b2 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -804,10 +804,22 @@ static bool do_edit(const char *filename_arg, PQExpBuffer query_buf,
}
/* \l is list databases */
- else if (strcmp(cmd, l) == 0 || strcmp(cmd, list) == 0)
- success = listAllDbs(false);
- else if (strcmp(cmd, l+) == 0 || strcmp(cmd, list+) == 0)
- success = listAllDbs(true);
+ else if (strcmp(cmd, l) == 0 || strcmp(cmd, list) == 0 ||
+ strcmp(cmd, l+) == 0 || strcmp(cmd, list+) == 0)
+ {
+ char *pattern;
+ bool show_verbose;
+
+ pattern = psql_scan_slash_option(scan_state,
+ OT_NORMAL, NULL, true);
+
+ show_verbose = strchr(cmd, '+') ? true : false;
+
+ success = listAllDbs(pattern, show_verbose);
+
+ if (pattern)
+ free(pattern);
+ }
/*
* large object things
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 8064a3d..046513d 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -641,7 +641,7 @@ static bool describeOneTSConfig(const char *oid, const char *nspname,
* for \l, \list, and -l switch
*/
bool
-listAllDbs(bool verbose)
+listAllDbs(const char *pattern, bool verbose)
{
PGresult *res;
PQExpBufferData buf;
@@ -684,6 +684,11 @@ static bool describeOneTSConfig(const char *oid, const char *nspname,
if (verbose pset.sversion = 8)
appendPQExpBuffer(buf,
JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid\n);
+
+ if (pattern)
+ processSQLNamePattern(pset.db, buf, pattern, false, false,
+ NULL, d.datname, NULL, NULL);
+
appendPQExpBuffer(buf, ORDER BY 1;);
res = PSQLexec(buf.data, false);
termPQExpBuffer(buf);
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 9e71a88..09b6237 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -55,7 +55,7 @@ extern bool listTSDictionaries(const char *pattern, bool verbose);
extern bool listTSTemplates(const char *pattern, bool verbose);
/* \l */
-extern bool listAllDbs(bool verbose);
+extern bool listAllDbs(const char *pattern, bool verbose);
/* \dt, \di, \ds, \dS, etc. */
extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index fd7effa..2cbdd83 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -233,7 +233,7 @@
fprintf(output, _( \\dE[S+] [PATTERN] list foreign tables\n));
fprintf(output, _( \\dx[+] [PATTERN] list extensions\n));
fprintf(output, _( \\dy [PATTERN] list event triggers\n));
- fprintf(output, _( \\l[+] list all databases\n));
+ fprintf(output, _( \\l[+] [PATTERN] list databases\n));
fprintf(output, _( \\sf[+] FUNCNAMEshow a function's definition\n));
fprintf(output, _( \\z [PATTERN] same as \\dp\n));
fprintf(output, \n);
diff --git a/src/bin/psql/startup.c b/src/bin/psql/startup.c
index a59f45b..5cb6b5f 100644
--- a/src/bin/psql/startup.c
+++ b/src/bin/psql/startup.c
@@ -260,7 +260,7 @@ static void parse_psql_options(int argc, char *argv[],
if (!options.no_psqlrc)
process_psqlrc(argv[0]);
- success = listAllDbs(false);
+ success = listAllDbs(NULL, false);
PQfinish(pset.db);
exit(success ? EXIT_SUCCESS : EXIT_FAILURE);
}
--
Sent via pgsql-hackers mailing list
Re: [HACKERS] psql \l to accept patterns
On Tue, Jan 8, 2013 at 11:36 PM, Peter Eisentraut pete...@gmx.net wrote: On Mon, 2013-01-07 at 17:37 -0500, Robert Haas wrote: If we make the postgres database undroppable, unrenamable, and strictly read-only, I will happily support a proposal to consider it a system object. Until then, it's no more a system object than the public schema - which, you will note, \dn has no compunctions about displaying, even without S. Good point. What about the other suggestion about only displaying databases by default that you can connect to? I would tend not to adopt that suggestion, on the grounds that it has no obvious parallel with anything else psql hides by default. However, I don't feel quite as strongly about that case. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql \l to accept patterns
* Robert Haas (robertmh...@gmail.com) wrote: On Tue, Jan 8, 2013 at 11:36 PM, Peter Eisentraut pete...@gmx.net wrote: Good point. What about the other suggestion about only displaying databases by default that you can connect to? I would tend not to adopt that suggestion, on the grounds that it has no obvious parallel with anything else psql hides by default. However, I don't feel quite as strongly about that case. In the past, haven't we done this through the catalog tables themselves rather than hacking up psql..? pg_stats being a prime example? With the row-level-security discussion, there was talk about if we might be able to apply that capability to catalogs also. That strikes me as a better option/approach than doing any of this in one particular application (psql in this case) which connects to PG. tbh, I'm not entirely against excluding databases that don't allow *any* connections (key'd off datallowconns) to clear out template0/template1 from the default list, but I see that as different from things I don't have permissions to. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] psql \l to accept patterns
On Mon, 2013-01-07 at 17:37 -0500, Robert Haas wrote: If we make the postgres database undroppable, unrenamable, and strictly read-only, I will happily support a proposal to consider it a system object. Until then, it's no more a system object than the public schema - which, you will note, \dn has no compunctions about displaying, even without S. Good point. What about the other suggestion about only displaying databases by default that you can connect to? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql \l to accept patterns
On Mon, Jan 7, 2013 at 10:14 AM, Peter Eisentraut pete...@gmx.net wrote: Here is a patch for psql's \l command to accept patterns, like \d commands do. While at it, I also added an S option to show system objects and removed system objects from the default display. This might be a bit controversial, but it's how it was decided some time ago that the \d commands should act. I applied the attached patch to the current master branch and everything is ok. When build all works fine too... and I do some tests: 1) Now '\l' list only regular databases postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges --+---+--+-+---+--- (0 rows) postgres=# CREATE DATABASE fabrizio; CREATE DATABASE postgres=# \l List of databases Name | Owner | Encoding | Collate |Ctype| Access privileges --+--+--+-+-+--- fabrizio | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | (1 row) postgres=# \l+ List of databases Name | Owner | Encoding | Collate |Ctype| Access privileges | Size | Tablespace | Description --+--+--+-+-+---+-++- fabrizio | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 5945 kB | pg_default | (1 row) postgres=# DROP DATABASE fabrizio; DROP DATABASE postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges --+---+--+-+---+--- (0 rows) 2) The new sub-command '\lS' list regular and systems databases postgres=# \lS List of databases Name| Owner | Encoding | Collate |Ctype| Access privileges ---+--+--+-+-+--- postgres | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio + | | | | | fabrizio=CTc/fabrizio template1 | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio + | | | | | fabrizio=CTc/fabrizio (3 rows) postgres=# CREATE DATABASE fabrizio; CREATE DATABASE postgres=# \lS List of databases Name| Owner | Encoding | Collate |Ctype| Access privileges ---+--+--+-+-+--- fabrizio | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio + | | | | | fabrizio=CTc/fabrizio template1 | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio + | | | | | fabrizio=CTc/fabrizio (4 rows) postgres=# \lS+ List of databases Name| Owner | Encoding | Collate |Ctype| Access privileges | Size | Tablespace |Description ---+--+--+-+-+---+-++ fabrizio | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 5945 kB | pg_default | postgres | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 6041 kB | pg_default | default administrative connecti on database template0 | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio +| 5945 kB | pg_default | unmodifiable empty database | | | | | fabrizio=CTc/fabrizio | || template1 | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio +| 5945 kB | pg_default | default template for new databa ses | | | | | fabrizio=CTc/fabrizio | || (4 rows) postgres=# DROP DATABASE fabrizio ; DROP DATABASE postgres=# \lS List of databases Name| Owner | Encoding | Collate |Ctype| Access privileges ---+--+--+-+-+--- postgres | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio + | | | | | fabrizio=CTc/fabrizio template1 | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio
Re: [HACKERS] psql \l to accept patterns
Peter Eisentraut wrote: Here is a patch for psql's \l command to accept patterns, like \d commands do. While at it, I also added an S option to show system objects and removed system objects from the default display. This might be a bit controversial, but it's how it was decided some time ago that the \d commands should act. How does this affect psql -l? Should it, for instance, hide system DBs? Accept an optional pattern? -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql \l to accept patterns
On Mon, Jan 7, 2013 at 7:14 AM, Peter Eisentraut pete...@gmx.net wrote: Here is a patch for psql's \l command to accept patterns, like \d commands do. While at it, I also added an S option to show system objects and removed system objects from the default display. This might be a bit controversial, but it's how it was decided some time ago that the \d commands should act. -1 from me on that last bit. I don't think that you can really compare the postgres or template1 database to, say, the pg_toast schema. There's no real reason for people to ever care about objects in the pg_toast schema, but the same cannot be said about template1, which it's often necessary to connect to when running many of the commands (vacuumdb -a, etc.) we ship with our distribution. I think this will just be confusing to users without any real upside. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql \l to accept patterns
Robert Haas robertmh...@gmail.com writes: On Mon, Jan 7, 2013 at 7:14 AM, Peter Eisentraut pete...@gmx.net wrote: Here is a patch for psql's \l command to accept patterns, like \d commands do. While at it, I also added an S option to show system objects and removed system objects from the default display. This might be a bit controversial, but it's how it was decided some time ago that the \d commands should act. -1 from me on that last bit. I don't think that you can really compare the postgres or template1 database to, say, the pg_toast schema. There's no real reason for people to ever care about objects in the pg_toast schema, but the same cannot be said about template1, which it's often necessary to connect to when running many of the commands (vacuumdb -a, etc.) we ship with our distribution. I think this will just be confusing to users without any real upside. Suppressing the postgres DB is even worse. I think that it might be sensible to have an S option and define system DBs as those without datallowconn, which ordinarily would only hide template0. But I can't get real excited about that. People do need to know about the existence of template0 (for use in CREATE DATABASE ... TEMPLATE ...), which is not so true of, say, pg_temp_NNN schemas. The it reduces clutter argument also seems pretty weak if we're only hiding one database, or even three of them. On the whole I lean towards not adding this notion. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql \l to accept patterns
On 1/7/13 3:53 PM, Robert Haas wrote: On Mon, Jan 7, 2013 at 7:14 AM, Peter Eisentraut pete...@gmx.net wrote: Here is a patch for psql's \l command to accept patterns, like \d commands do. While at it, I also added an S option to show system objects and removed system objects from the default display. This might be a bit controversial, but it's how it was decided some time ago that the \d commands should act. -1 from me on that last bit. I don't think that you can really compare the postgres or template1 database to, say, the pg_toast schema. There's no real reason for people to ever care about objects in the pg_toast schema, but the same cannot be said about template1, which it's often necessary to connect to when running many of the commands (vacuumdb -a, etc.) we ship with our distribution. I think this will just be confusing to users without any real upside. We removed showing system functions and operators from \df and \do without S. Those are needed all the time. This was controversial at the time, but it's the way it is now. The definition of S, I suppose, is more like is there after database is created, not typical users care about these objects. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql \l to accept patterns
On Mon, Jan 7, 2013 at 5:14 PM, Peter Eisentraut pete...@gmx.net wrote: We removed showing system functions and operators from \df and \do without S. Those are needed all the time. This was controversial at the time, but it's the way it is now. The definition of S, I suppose, is more like is there after database is created, not typical users care about these objects. System functions and operators are needed all the time, but so are system tables and views, and the old behavior was that the latter were suppressed by default and the former were included by default. So I consider that change to be well-justified on consistency grounds. There's a practical consideration, as well. Out of the box, there are 2400 entries for functions and 3 for databases. This means that the old \df behavior made it very hard to figure out what user-defined functions exist in your database, but there's no corresponding problem with \l. Finally, note that you can drop the postgres database (and everything else will still work) but you cannot drop pg_table_is_visible(oid), because, as the error message will inform you, it is required by the database system. If we make the postgres database undroppable, unrenamable, and strictly read-only, I will happily support a proposal to consider it a system object. Until then, it's no more a system object than the public schema - which, you will note, \dn has no compunctions about displaying, even without S. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
