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