Thread: 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
Attachment
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
Attachment
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
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.
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.
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
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
On Monday, November 15, 2021, Tatsuro Yamada <tatsuro.yamada.tf@nttcom.co.
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-pos tgresql
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.
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