Обсуждение: Add psql command to list constraints

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

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

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

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

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

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

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

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

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

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

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

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