Thread: Add psql command to list constraints

Add psql command to list constraints

From
Tatsuro Yamada
Date:
Hi,

I have been wondering why there is no meta-command for listing
constraints in psql. So, I created a POC patch by using my
experience developing \dX command in PG14.

This feature is helpful for DBAs when they want to check or
modify the definition of constraints.

The current status of the POC patch is as follows:

   - Add "\dco" command to list constraints from pg_constraint

   - Not implemented yet:
     - NOT NULL constraint, and so on (based on pg_attribute)
     - Tab completion
     - Regression test
     - Document

The following is test results (See attached test_list_con.sql)
====================================================================
postgres=# \dco
                                          List of constsraints
  Schema |          Name           |                       Definition                        |  Table
--------+-------------------------+---------------------------------------------------------+----------
  public | t01_chk_price_check     | CHECK ((price > (0)::numeric))                          | t01_chk
  public | t02_uniq_product_no_key | UNIQUE (product_no)                                     | t02_uniq
  public | t03_pk1_pkey            | PRIMARY KEY (product_no)                                | t03_pk1
  public | t03_pk2_product_no_key  | UNIQUE (product_no)                                     | t03_pk2
  public | t04_fk_pkey             | PRIMARY KEY (order_id)                                  | t04_fk
  public | t04_fk_product_no_fkey  | FOREIGN KEY (product_no) REFERENCES t03_pk1(product_no) | t04_fk
  public | t05_ex_c_excl           | EXCLUDE USING gist (c WITH &&)                          | t05_ex
(7 rows)
====================================================================


I have the following two questions that need to be discussed.

Questions:
(1) What strings should be assigned as meta-command for this feature?
    Currently, \dc and \dC are not available, so I tentatively
    assigned \dco. However, I do not have a strong opinion, so please
    let me know if you have any suggestions.

(2) About domain constraints
    There is the \dD command to show a list of domain constraints.
    So I think this feature should not include it. Is it Okay?


If I can get "+1" for this new feature development, I would like to
improve the patch by adding NOT NULL constraints, and so on.
Any advice or comments would be appreciated.


Thanks,
Tatsuro Yamada

Attachment

Re: Add psql command to list constraints

From
Justin Pryzby
Date:
Hi,

On Mon, Nov 15, 2021 at 10:38:55AM +0900, Tatsuro Yamada wrote:
> postgres=# \dco
>                                          List of constsraints
>  Schema |          Name           |                       Definition                        |  Table
> --------+-------------------------+---------------------------------------------------------+----------
>  public | t01_chk_price_check     | CHECK ((price > (0)::numeric))                          | t01_chk
>  public | t02_uniq_product_no_key | UNIQUE (product_no)                                     | t02_uniq
>  public | t03_pk1_pkey            | PRIMARY KEY (product_no)                                | t03_pk1
>  public | t03_pk2_product_no_key  | UNIQUE (product_no)                                     | t03_pk2
>  public | t04_fk_pkey             | PRIMARY KEY (order_id)                                  | t04_fk
>  public | t04_fk_product_no_fkey  | FOREIGN KEY (product_no) REFERENCES t03_pk1(product_no) | t04_fk
>  public | t05_ex_c_excl           | EXCLUDE USING gist (c WITH &&)                          | t05_ex
> (7 rows)
> ====================================================================

Maybe it ought to be possible to choose the type of constraints to show.
Similar to how \dt shows tables and \di shows indexes and \dti shows
tables+inds, you could run \dcoc for check constraints and \dcof for foreign
keys.  But I think "\dco" is too long of a prefix...

> +    initPQExpBuffer(&buf);
> +    printfPQExpBuffer(&buf,
> +                      "SELECT \n"
> +                      "n.nspname AS \"%s\", \n"
> +                      "cst.conname AS \"%s\", \n"
> +                      "pg_catalog.pg_get_constraintdef(cst.oid) AS \"%s\", \n"
> +                      "c.relname AS \"%s\" \n"
> +                      "FROM pg_constraint cst \n"
> +                      "JOIN pg_namespace n ON n.oid = cst.connamespace \n"
> +                      "JOIN pg_class c ON c.oid = cst.conrelid \n",

You should write "pg_catalog." prefix for the tables (in addition to the
function).

Rather than join to pg_class, you can write conrelid::pg_catalog.regclass,
since regclass is supported since at least v7.3 (but ::regnamespace was
introduced in v9.5, so the join against pg_namespace is still necessary).
https://www.postgresql.org/docs/9.5/datatype-oid.html

> +    myopt.title = _("List of constsraints");

spelling: constraints

I'm not confident that if I would use this, so let's wait to see if someone
else wants to give a +1.

-- 
Justin



Re: Add psql command to list constraints

From
Tatsuro Yamada
Date:
Hi Justin,

Thanks for your comments and review!

> Maybe it ought to be possible to choose the type of constraints to show.
> Similar to how \dt shows tables and \di shows indexes and \dti shows
> tables+inds, you could run \dcoc for check constraints and \dcof for foreign
> keys.  But I think "\dco" is too long of a prefix...


Yeah, agreed.
I added a filter based on the type of constraints:
   - c for check
   - f for foreign key
   - p for primary key
   - t for trigger
   - u for unique
   - x for exclude c, f, p, u, t, and x.

The following is examples of \dcop, \dcof, and \dcopf.

========================================================================
postgres=# \dcop
                     List of constraints
  Schema |     Name     |        Definition        |  Table
--------+--------------+--------------------------+---------
  public | t03_pk1_pkey | PRIMARY KEY (product_no) | t03_pk1
  public | t04_fk_pkey  | PRIMARY KEY (order_id)   | t04_fk
(2 rows)

postgres=# \dcof
                                         List of constraints
  Schema |          Name          |                       Definition                        | Table
--------+------------------------+---------------------------------------------------------+--------
  public | t04_fk_product_no_fkey | FOREIGN KEY (product_no) REFERENCES t03_pk1(product_no) | t04_fk
(1 row)

postgres=# \dcopf
                                          List of constraints
  Schema |          Name          |                       Definition                        |  Table
--------+------------------------+---------------------------------------------------------+---------
  public | t03_pk1_pkey           | PRIMARY KEY (product_no)                                | t03_pk1
  public | t04_fk_pkey            | PRIMARY KEY (order_id)                                  | t04_fk
  public | t04_fk_product_no_fkey | FOREIGN KEY (product_no) REFERENCES t03_pk1(product_no) | t04_fk
(3 rows)
========================================================================


I too think \dco is a long name. So,  I'd like to get suggestions to be more shortened. :)


  
>> +    initPQExpBuffer(&buf);
>> +    printfPQExpBuffer(&buf,
>> +                      "SELECT \n"
>> +                      "n.nspname AS \"%s\", \n"
>> +                      "cst.conname AS \"%s\", \n"
>> +                      "pg_catalog.pg_get_constraintdef(cst.oid) AS \"%s\", \n"
>> +                      "c.relname AS \"%s\" \n"
>> +                      "FROM pg_constraint cst \n"
>> +                      "JOIN pg_namespace n ON n.oid = cst.connamespace \n"
>> +                      "JOIN pg_class c ON c.oid = cst.conrelid \n",
> 
> You should write "pg_catalog." prefix for the tables (in addition to the
> function).


Oops, You are right. Fixed.

  
> Rather than join to pg_class, you can write conrelid::pg_catalog.regclass,
> since regclass is supported since at least v7.3 (but ::regnamespace was
> introduced in v9.5, so the join against pg_namespace is still necessary).
> https://www.postgresql.org/docs/9.5/datatype-oid.html
>> +    myopt.title = _("List of constsraints");
> 
> spelling: constraints


Thanks! Fixed.

  
> I'm not confident that if I would use this, so let's wait to see if someone
> else wants to give a +1.


Okay, but you agree that there are DBAs and users who want to see the
list of constraints, I think.

Currently, DBAs need the table name to see the constraint information.
However, with this feature, you can see its definition and table name
from the constraint name.
For example, it will be easier to understand how many foreign key
constraints are in the DB. The \d command also displays the constraints
but does not list them, so this feature is more beneficial for those who
want to check them.


Attached new patch includes:

   -  Add a filter by contype
   - Add pg_catalog prefix
   - Fix typo
   - Fix help message to add \dco

Not implemented yet:
   - NOT NULL constraint, and so on (based on pg_attribute)
   - Tab completion
   - Regression test
   - Document

Any comments welcome! :-D


Thanks,
Tatsuro Yamada

Attachment

Re: Add psql command to list constraints

From
Justin Pryzby
Date:
Thanks for updating the patch :)

> Currently, DBAs need the table name to see the constraint information.

Or, they can query pg_constraint or information_schema: check_constraints,
table_constraints.

> -                success = listConversions(pattern, show_verbose, show_system);
> +                if (strncmp(cmd, "dco", 3) == 0) /* Constraint */
> +                    switch (cmd[3])
> +                    {
> +                        case '\0':
> +                        case '+':

Does "+" do anything ?

> +++ b/src/bin/psql/help.c
> @@ -231,6 +231,7 @@ slashUsage(unsigned short int pager)

>      fprintf(output, _("  \\db[+]  [PATTERN]      list tablespaces\n"));
>      fprintf(output, _("  \\dc[S+] [PATTERN]      list conversions\n"));
> +    fprintf(output, _("  \\dco[S] [PATTERN]      list constraint\n"));

Should be plural "constraints".

I think "exclude" should be called "exclusion" ("exclude" sounded to me like
you're going to provide a way to "exclude" types of constraints, like "xc"
would show everything except check constraints).

-- 
Justin



Re: Add psql command to list constraints

From
"David G. Johnston"
Date:
On Mon, Nov 15, 2021 at 5:23 PM Tatsuro Yamada <tatsuro.yamada.tf@nttcom.co.jp> wrote:

> I'm not confident that if I would use this, so let's wait to see if someone
> else wants to give a +1.

Okay, but you agree that there are DBAs and users who want to see the
list of constraints, I think.

My opinion is this doesn't exist because there isn't any demand for it.  
 
For example, it will be easier to understand how many foreign key
constraints are in the DB.

That isn't a very compelling metric.  Metrics also don't seem to be the primary motivation for the psql \d commands.  I envision them mostly useful when writing a query and wanting a query refresher as to what is valid/available.  In that context looking at constraints in the context of a single table makes sense.  Looking at all constraints is considerably less so.  Especially since constraints mostly impact insert queries and those only affect a single table.

If the only motivation for this is "feature completion" - since we have so many other \d commands already implemented - I say we should pass.

David J.

Re: Add psql command to list constraints

From
"David G. Johnston"
Date:
On Monday, November 15, 2021, David G. Johnston <david.g.johnston@gmail.com> wrote:

If the only motivation for this is "feature completion" - since we have so many other \d commands already implemented - I say we should pass.

If anything, doing this for triggers would be a much better feature.

The other missing listing then would be columns.  I would rather add both columns and constraints, or neither.  And add triggers regardless as their combination of rarity and performance impact makes having them listable on a \d command would be beneficial.

I also noticed that the “\dd” command would need to be modified (and maybe deprecated if we actually do simply provide a listing for object type).

David J.
 

Re: Add psql command to list constraints

From
Tatsuro Yamada
Date:
Hi Justin,


Thanks for your comments!

  
>> Currently, DBAs need the table name to see the constraint information.
> 
> Or, they can query pg_constraint or information_schema: check_constraints,
> table_constraints.


Yeah, right.
If they can use the meta-command instead of a long query against pg_constraint
or information_schema and also pg_attribulte, it would be helpful, I believe. :-D



>> -                success = listConversions(pattern, show_verbose, show_system);
>> +                if (strncmp(cmd, "dco", 3) == 0) /* Constraint */
>> +                    switch (cmd[3])
>> +                    {
>> +                        case '\0':
>> +                        case '+':
> 
> Does "+" do anything ?


No, it doesn't. Removed.


  
>> +++ b/src/bin/psql/help.c
>> @@ -231,6 +231,7 @@ slashUsage(unsigned short int pager)
> 
>>       fprintf(output, _("  \\db[+]  [PATTERN]      list tablespaces\n"));
>>       fprintf(output, _("  \\dc[S+] [PATTERN]      list conversions\n"));
>> +    fprintf(output, _("  \\dco[S] [PATTERN]      list constraint\n"));
> 
> Should be plural "constraints".
> 
> I think "exclude" should be called "exclusion" ("exclude" sounded to me like
> you're going to provide a way to "exclude" types of constraints, like "xc"
> would show everything except check constraints).


Thanks! Fixed the both.


Attached file is new patch. It includes:

   - Fix help message s/constraint/constraints/
   - s/Exclude/Exclusion/
   - Remove unused modifier "+"
   - Add document for \dco



Thanks,
Tatsuro Yamada


Attachment

Re: Add psql command to list constraints

From
Tatsuro Yamada
Date:
Hi David,

Thanks for your comments.

>     Okay, but you agree that there are DBAs and users who want to see the
>     list of constraints, I think.
> 
> 
> My opinion is this doesn't exist because there isn't any demand for it.


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.
(The other day, I also wanted to see the list, and I arrived at this site)
Therefore, the only thing that hasn't been implemented so far is that no one
could communicate the request to -hackers, and I think there is demand.

https://stackoverflow.com/questions/62987794/how-to-list-all-constraints-of-a-table-in-postgresql


Regards,
Tatsuro Yamada









Add psql command to list constraints

From
"David G. Johnston"
Date:
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.

Re: Add psql command to list constraints

From
Robert Haas
Date:
On Fri, Mar 25, 2022 at 3:20 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
> \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.

I don't think that \dX is comparable, because I don't think we should
regard extended statistics as a table object. Indeed, generalizing
extended statistics so that they can be generated on a join seems to
me to be one of the most important things we could do in that area.

-- 
Robert Haas
EDB: http://www.enterprisedb.com



Re: Add psql command to list constraints

From
Greg Stark
Date:
In the interests of trying to clean up the CF and keep things moving
I'll mark the patch rejected.

That doesn't mean the decision can't change or that nobody's allowed
to keep discussing it. It's just that that seems to be the decision
right now and there are too many patches queued up to keep things in a
pending state waiting for a more decisive conclusion. We can always
change it if the winds shift...



Re: Add psql command to list constraints

From
Tatsuro Yamada
Date:
Hi All,

> In the interests of trying to clean up the CF and keep things moving
> I'll mark the patch rejected.

Thank you for managing the commitfest and the comments from many of
hackers. I apologize for not being able to email you more often due to
my busy day job.

First of all, I understand to a certain extent your opinion that we
can use \d and look at the constraints on a table-by-table basis as a
way to check the constraints.
However, suppose We want to reverse lookup a table from a constraint.
In that case, there are two ways: (1) use "\d" to lookup all tables,
(2) execute a select statement against pg_constraint. I think the
proposed function is more valuable than these.

 From another perspective, when looking at the comprehensiveness of
metacommands in psql, it seems that only functions that focus on
constraints do not exist. Therefore, It would be better to add it in
terms of comprehensiveness.

I think there is room for other discussions about this patch. Still,
at least there are people (including myself) who think it is useful.
I don't think there is anything wrong with this patch that would
complicate the code or cause performance degradation, so I would like to
continue developing it for those who want to use it.

However, I understand that it will not be ready in time for PG15, so
I would like to move forward with PG16. Therefore, the status of the
patch would be better by changing "Waiting for Author" rather than
"Rejected".

P.S.
I'll send a new patch addressing Dag's comments in the next email.

Thanks,
Tatsuro Yamada





Re: Add psql command to list constraints

From
Tatsuro Yamada
Date:
Hi Dag,

> The patch adds the command "\dco" to list constraints in psql. This
> seems useful to me.

Thank you!

  
> 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.

Fixed.


> * 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);


Oh, you are right! Fixed.

  
> 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".
  

I fixed the output columns as you proposed.

The current status of this patch is:

   - Addressed Dag's comments
   - Not implemented yet:
     - Tab completion
     - Regression test
     - NOT NULL constraint, and so on (based on pg_attribute)

Please find attached new patch.


Thanks,
Tatsuro Yamada





Attachment