Re: psql \l to accept patterns

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.

On Mon, Jan 7, 2013 at 10:14 AM, Peter Eisentraut <peter_e(at)gmx(dot)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
+
| | | | |
fabrizio=CTc/fabrizio
(3 rows)

3) Now '\l[S+] [pattern]' works:

postgres=# CREATE DATABASE fabrizio;
CRECREATE DATABASE
postgres=# CREATE DATABASE postgis;
CREATE DATABASE
postgres=# CREATE DATABASE mytemplate;
CREATE DATABASE
postgres=# \l fab*
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 post*
List of databases
Name | Owner | Encoding | Collate | Ctype | Access
privileges
----------+----------+----------+-------------+-------------+-------------------
postgis | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(2 rows)

postgres=# \l *template*
List of databases
Name | Owner | Encoding | Collate | Ctype | Access
privileges
------------+----------+----------+-------------+-------------+-----------------------
mytemplate | 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=# \l *template
List of databases
Name | Owner | Encoding | Collate | Ctype | Access
privileges
------------+----------+----------+-------------+-------------+-------------------
mytemplate | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(1 row)

4) By command line all works ok too...

$ ./bin/psql -c "\l"
List of databases
Name | Owner | Encoding | Collate | Ctype | Access
privileges
------------+----------+----------+-------------+-------------+-------------------
fabrizio | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
mytemplate | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgis | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(3 rows)

$ ./bin/psql -c "\lS"
List of databases
Name | Owner | Encoding | Collate | Ctype | Access
privileges
------------+----------+----------+-------------+-------------+-----------------------
fabrizio | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
mytemplate | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgis | 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
(6 rows)

$ ./bin/psql -c "\l post*"
List of databases
Name | Owner | Encoding | Collate | Ctype | Access
privileges
----------+----------+----------+-------------+-------------+-------------------
postgis | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(2 rows)

5) Docs and psql help was updated correctly.

The attached patch is ok for me and ready for commit.

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello

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 Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Mon, Jan 7, 2013 at 7:14 AM, Peter Eisentraut <peter_e(at)gmx(dot)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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Mon, Jan 7, 2013 at 7:14 AM, Peter Eisentraut <peter_e(at)gmx(dot)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

On 1/7/13 3:53 PM, Robert Haas wrote:
> On Mon, Jan 7, 2013 at 7:14 AM, Peter Eisentraut <peter_e(at)gmx(dot)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".

On Mon, Jan 7, 2013 at 5:14 PM, Peter Eisentraut <peter_e(at)gmx(dot)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

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?

On Tue, Jan 8, 2013 at 11:36 PM, Peter Eisentraut <peter_e(at)gmx(dot)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

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> On Tue, Jan 8, 2013 at 11:36 PM, Peter Eisentraut <peter_e(at)gmx(dot)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

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.

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 <snaga(at)uptime(dot)jp>
Uptime Technologies, LLC. http://www.uptime.jp

Satoshi Nagayasu <snaga(at)uptime(dot)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

(2013/01/30 0:34), Tom Lane wrote:
> Satoshi Nagayasu <snaga(at)uptime(dot)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 <snaga(at)uptime(dot)jp>
Uptime Technologies, LLC. http://www.uptime.jp

Satoshi Nagayasu <snaga(at)uptime(dot)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

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.