Thread: Re: Add psql command to list constraints
"David G. Johnston" <david.g.johnston@gmail.com> writes: > On Monday, November 15, 2021, Tatsuro Yamada > <tatsuro.yamada.tf@nttcom.co.jp> wrote: > > I don't know if this is a good example, but if you look at > StackOverflow, > it seems that people who want to see a list of constraints appear > regularly. > > https://stackoverflow.com/questions/62987794/how-to-list-all-constraints- > of-a-table-in-postgresql > > > Given the questioner restricted their question to “for a given table” > I’d say it supports leaving the status quo, not changing it. > > If, as you suppose, these come up regularly then finding one that asks > for it “in the entire database”, ideally with some stated goal, should > be doable. > > David J. > > This is my review of the patch in https://commitfest.postgresql.org/37/3468/ The patch adds the command "\dco" to list constraints in psql. This seems useful to me. The patch applies cleanly to HEAD, although some hunks have rather large offsets. As far as I can tell, the "\dco" command works as documented. I have however found the following issues with the patch: * A TAB character has been added to doc/src/sgml/ref/psql-ref.sgml - this should be replaced with spaces. * The call to listConstraints in line src/bin/psql/command.c 794 refers to &cmd[2], this should rather be &cmd[3]. * The patch kills the "\dc" command in src/bin/psql/command.c This can be fixed by adding the following at line 800: else success = listConversions(pattern, show_verbose, show_system); Another comment is that the "\dco" command outputs quite a lot of information, which only fits in a wide terminal window. Would it be an idea to only display the columns "Schema" and "Name" by default, and use "+" to specify inclusion of the columns "Definition" and "Table". Best regards Dag Lem
Development of this seems to have stalled with the only review of this patch expressing some skepticism about whether it's needed at all. Unless anyone steps forward and says it's interesting I'm going to mark it rejected? I don't actually think it's a terrible idea myself but I think every use case I might have had for it was solved better by directly querying catalog tables.
On Fri, Mar 25, 2022 at 12:28 AM Greg Stark <stark@mit.edu> wrote:
> Development of this seems to have stalled with the only review of this
> patch expressing some skepticism about whether it's needed at all.
My opinion on this patch is that we typically handle objects that are
essentially table properties by showing the output in \d+ on the
individual table. And that already works just fine:
rhaas=# create table duck (quack int unique, check (quack > 0));
CREATE TABLE
rhaas=# \d+ duck
Table "public.duck"
Column | Type | Collation | Nullable | Default | Storage |
Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
quack | integer | | | | plain |
| |
Indexes:
"duck_quack_key" UNIQUE CONSTRAINT, btree (quack)
Check constraints:
"duck_quack_check" CHECK (quack > 0)
Access method: heap
Now, there is some precedent for the idea of providing a command that
lists everything globally. Specifically, we have a \dp command, also
known as \z, to list privileges across all objects in the database.
However, I have found that command to be relatively useless, because
if you've got any significant number of grants in the database it just
produces too much output. I think this would have the same problem.
The other thing that we have that is somewhat similar to this is \dd,
which lists comments "for object types which do not have their
comments displayed by their own backslash commands." However, it says
that the object types that it covers are "constraint, operator class,
operator family, rule, and trigger," and that list is out of date,
because operator classes and families got their own backslash commands
two years ago. We could update that, but honestly I can't see anyone
being too excited about a command that lists comments on every
constraint, rule, and trigger in the system: it would be a lot more
useful to show those commands in the \d+ output for the table to which
they are bound, and get rid of \dd (and maybe \dp and \z too).
Now that is not to say that what is being proposed here is completely
useless. It clearly isn't. It's totally debatable whether we ought to
start having commands like this, and maybe we should. It would make
for more commands, and that's not entirely great because the command
names are increasingly alphabet soup. Who can remember what \drds or
\dAc does? Only real power users. If we add more, it's going to get
even more difficult, but some people will use it and like it and those
people will be happy. It's kind of hard to say whether we'd come out
ahead or not. What I think is fairly certain is that it would
represent a reversal of our current policy.
--
Robert Haas
EDB: http://www.enterprisedb.com
On Fri, Mar 25, 2022 at 03:11:47PM -0400, Robert Haas wrote: > On Fri, Mar 25, 2022 at 12:28 AM Greg Stark <stark@mit.edu> wrote: > > Development of this seems to have stalled with the only review of this > > patch expressing some skepticism about whether it's needed at all. > > Now, there is some precedent for the idea of providing a command that > lists everything globally. Specifically, we have a \dp command, also > known as \z, to list privileges across all objects in the database. > The other thing that we have that is somewhat similar to this is \dd, \dX is similar, and I remember wondering whether it was really useful/needed. The catalog tables are exposed and documented for a reason, and power-users will learn to use them. +0.5 to mark the patch as RWF or rejected. -- Justin