psql display of foreign keys

From: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
To: Pg Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: psql display of foreign keys
Date: 2018-12-04 14:38:34
Message-ID: 20181204143834.ym6euxxxi5aeqdpn@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

When \d a table referenced by a foreign key on a partitioned table, you
currently get this:

Table "public.referenced"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | not null |
Indexes:
"referenced_pkey" PRIMARY KEY, btree (a)
Referenced by:
TABLE "hashp96_39" CONSTRAINT "hashp_a_fkey" FOREIGN KEY (a) REFERENCES referenced(a)
TABLE "hashp96_38" CONSTRAINT "hashp_a_fkey" FOREIGN KEY (a) REFERENCES referenced(a)
TABLE "hashp96_37" CONSTRAINT "hashp_a_fkey" FOREIGN KEY (a) REFERENCES referenced(a)
TABLE "hashp96_36" CONSTRAINT "hashp_a_fkey" FOREIGN KEY (a) REFERENCES referenced(a)
(thousands more)

This is not very useful. I propose that we change it so that it only
displays the one on the partitioned table on which the constraint was
defined:
Table "public.referenced"
Column │ Type │ Collation │ Nullable │ Default
────────┼─────────┼───────────┼──────────┼─────────
a │ integer │ │ not null │
Indexes:
"referenced_pkey" PRIMARY KEY, btree (a)
Referenced by:
TABLE "hashp" CONSTRAINT "hashp_a_fkey" FOREIGN KEY (a) REFERENCES referenced(a)
TABLE "hashp" CONSTRAINT "hashp_b_fkey" FOREIGN KEY (b) REFERENCES referenced(a)
TABLE "parted" CONSTRAINT "parted_a_fkey" FOREIGN KEY (a) REFERENCES referenced(a)

Which results in the actually useful info.

Also, when describing one of the partitions, I propose we add a "TABLE
foo" prefix to the constraint line, so that it indicates on which
ancestor table the constraint was defined. So instead of this:

\d parted1
Table "public.parted1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | not null |
Partition of: parted FOR VALUES FROM (0) TO (1)
Foreign-key constraints:
"parted_a_fkey" FOREIGN KEY (a) REFERENCES referenced(a)

we get this:

\d parted1
Table "public.parted1"
Column │ Type │ Collation │ Nullable │ Default
────────┼─────────┼───────────┼──────────┼─────────
a │ integer │ │ not null │
Partition of: parted FOR VALUES FROM (0) TO (1)
Foreign-key constraints:
TABLE "parted" CONSTRAINT "parted_a_fkey" FOREIGN KEY (a) REFERENCES referenced(a)

In some cases (such as in the regression tests that change in this
commit) the constraint name is different in the parent than the
partition, and it is more useful to display the parent's constraint name
rather than the partition's.

My first instinct is to change this in psql for Postgres 11, unless
there's much opposition to that.

Patch attached.

PS -- it surprises me that we've got this far without an index on
pg_constraint.confrelid.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachment Content-Type Size
0001-Fix-psql-display-of-FKs-on-partitioned-tables.patch text/x-diff 9.9 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2018-12-04 15:00:00 Re: psql display of foreign keys
Previous Message Alvaro Herrera 2018-12-04 13:23:20 Re: additional foreign key test coverage