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
>From fcc42e59fde284024dd5b3793ee503b0b515f51e Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvhe...@alvh.no-ip.org>
Date: Tue, 4 Dec 2018 11:04:28 -0300
Subject: [PATCH] Fix psql display of FKs on partitioned tables
... as well as the display of partitioned tables as referencing, when
the referenced table is described.
With this change, instead of listing every single partition of the other
side, we only list the parent partitioned table on which the constraint
was explicitly declared.
We also add a "TABLE foo" prefix when describing the foreign key in a
partition, indicating which ancestor table the constraint was declared
in.
---
src/bin/psql/describe.c | 61 +++++++++++++++++++++++--------
src/test/regress/expected/foreign_key.out | 16 ++++----
2 files changed, 53 insertions(+), 24 deletions(-)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 4ca0db1d0c..a84fa8cb7b 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2358,17 +2358,28 @@ describeOneTableDetails(const char *schemaname,
/*
* Print foreign-key constraints (there are none if no triggers,
* except if the table is partitioned, in which case the triggers
- * appear in the partitions)
+ * appear in the partitions). Note we put the constraints defined
+ * in this table first, followed by the constraints defined in
+ * ancestor partitioned tables. XXX maybe should display in hierarchy
+ * order.
*/
if (tableinfo.hastriggers ||
tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
{
printfPQExpBuffer(&buf,
- "SELECT conname,\n"
- " pg_catalog.pg_get_constraintdef(r.oid, true) as condef\n"
- "FROM pg_catalog.pg_constraint r\n"
- "WHERE r.conrelid = '%s' AND r.contype = 'f' ORDER BY 1;",
- oid);
+ "WITH RECURSIVE constraints (oid, parent, conname, conrelid) as (\n"
+ " SELECT oid, conparentid, conname, conrelid\n"
+ " FROM pg_catalog.pg_constraint WHERE contype = 'f' AND conrelid = '%s'\n"
+ " UNION\n"
+ " SELECT pc.oid, pc.conparentid, pc.conname, pc.conrelid\n"
+ " FROM constraints, pg_constraint pc\n"
+ " WHERE pc.oid = constraints.parent\n"
+ ") SELECT conrelid = '%s' as conislocal, conname,\n"
+ " pg_catalog.pg_get_constraintdef(oid), conrelid::pg_catalog.regclass\n"
+ " FROM constraints\n"
+ " WHERE parent = 0\n"
+ "ORDER BY conislocal DESC, conname;",
+ oid, oid);
result = PSQLexec(buf.data);
if (!result)
goto error_return;
@@ -2380,10 +2391,20 @@ describeOneTableDetails(const char *schemaname,
printTableAddFooter(&cont, _("Foreign-key constraints:"));
for (i = 0; i < tuples; i++)
{
- /* untranslated constraint name and def */
- printfPQExpBuffer(&buf, " \"%s\" %s",
- PQgetvalue(result, i, 0),
- PQgetvalue(result, i, 1));
+ /*
+ * Print untranslated constraint name and definition.
+ * Use a "TABLE tab" prefix when the constraint is
+ * defined in a parent partitioned table.
+ */
+ if (strcmp(PQgetvalue(result, i, 0), "f") == 0)
+ printfPQExpBuffer(&buf, " TABLE \"%s\" CONSTRAINT \"%s\" %s",
+ PQgetvalue(result, i, 3),
+ PQgetvalue(result, i, 1),
+ PQgetvalue(result, i, 2));
+ else
+ printfPQExpBuffer(&buf, " \"%s\" %s",
+ PQgetvalue(result, i, 1),
+ PQgetvalue(result, i, 2));
printTableAddFooter(&cont, buf.data);
}
@@ -2392,13 +2413,21 @@ describeOneTableDetails(const char *schemaname,
}
/* print incoming foreign-key references (none if no triggers) */
- if (tableinfo.hastriggers)
+ if (tableinfo.hastriggers ||
+ tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
{
printfPQExpBuffer(&buf,
- "SELECT conname, conrelid::pg_catalog.regclass,\n"
- " pg_catalog.pg_get_constraintdef(c.oid, true) as condef\n"
- "FROM pg_catalog.pg_constraint c\n"
- "WHERE c.confrelid = '%s' AND c.contype = 'f' ORDER BY 1;",
+ "WITH RECURSIVE constraints (oid, parent, conname, conrelid, confrelid) as (\n"
+ " SELECT oid, conparentid, conname, conrelid, confrelid\n"
+ " FROM pg_catalog.pg_constraint WHERE contype = 'f' AND confrelid = '%s'\n"
+ " UNION\n"
+ " SELECT pc.oid, pc.conparentid, pc.conname, pc.conrelid, pc.confrelid\n"
+ " FROM constraints, pg_constraint pc\n"
+ " WHERE pc.oid = constraints.parent\n"
+ ") SELECT conrelid::pg_catalog.regclass, conname, pg_catalog.pg_get_constraintdef(oid)\n"
+ " FROM constraints\n"
+ " WHERE parent = 0\n"
+ "ORDER BY conname;",
oid);
result = PSQLexec(buf.data);
if (!result)
@@ -2412,8 +2441,8 @@ describeOneTableDetails(const char *schemaname,
for (i = 0; i < tuples; i++)
{
printfPQExpBuffer(&buf, " TABLE \"%s\" CONSTRAINT \"%s\" %s",
- PQgetvalue(result, i, 1),
PQgetvalue(result, i, 0),
+ PQgetvalue(result, i, 1),
PQgetvalue(result, i, 2));
printTableAddFooter(&cont, buf.data);
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 52164e89d2..c1feb76f61 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -1680,7 +1680,7 @@ ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN
a | integer | | |
Partition of: fk_partitioned_fk FOR VALUES IN (1500, 1502)
Foreign-key constraints:
- "fk_partitioned_fk_2_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
+ TABLE "fk_partitioned_fk" CONSTRAINT "fk_partitioned_fk_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
DROP TABLE fk_partitioned_fk_2;
CREATE TABLE fk_partitioned_fk_4 (a int, b int, FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE) PARTITION BY RANGE (b, a);
@@ -1700,7 +1700,7 @@ ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_4 FOR VALUES IN
Partition of: fk_partitioned_fk FOR VALUES IN (3500, 3502)
Partition key: RANGE (b, a)
Foreign-key constraints:
- "fk_partitioned_fk_4_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
+ TABLE "fk_partitioned_fk" CONSTRAINT "fk_partitioned_fk_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
Number of partitions: 2 (Use \d+ to list them.)
\d fk_partitioned_fk_4_1
@@ -1711,7 +1711,7 @@ Number of partitions: 2 (Use \d+ to list them.)
b | integer | | |
Partition of: fk_partitioned_fk_4 FOR VALUES FROM (1, 1) TO (100, 100)
Foreign-key constraints:
- "fk_partitioned_fk_4_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
+ TABLE "fk_partitioned_fk" CONSTRAINT "fk_partitioned_fk_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
-- this one has an FK with mismatched properties
\d fk_partitioned_fk_4_2
@@ -1723,7 +1723,7 @@ Foreign-key constraints:
Partition of: fk_partitioned_fk_4 FOR VALUES FROM (100, 100) TO (1000, 1000)
Foreign-key constraints:
"fk_partitioned_fk_4_2_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE SET NULL
- "fk_partitioned_fk_4_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
+ TABLE "fk_partitioned_fk" CONSTRAINT "fk_partitioned_fk_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
CREATE TABLE fk_partitioned_fk_5 (a int, b int,
FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE,
@@ -1747,7 +1747,7 @@ Partition key: RANGE (a)
Foreign-key constraints:
"fk_partitioned_fk_5_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE
"fk_partitioned_fk_5_a_fkey1" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE
- "fk_partitioned_fk_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
+ TABLE "fk_partitioned_fk" CONSTRAINT "fk_partitioned_fk_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
Number of partitions: 1 (Use \d+ to list them.)
-- verify that it works to reattaching a child with multiple candidate
@@ -1763,9 +1763,9 @@ ALTER TABLE fk_partitioned_fk_5 ATTACH PARTITION fk_partitioned_fk_5_1 FOR VALUE
Partition of: fk_partitioned_fk_5 FOR VALUES FROM (0) TO (10)
Foreign-key constraints:
"fk_partitioned_fk_5_1_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b)
- "fk_partitioned_fk_5_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE
- "fk_partitioned_fk_5_a_fkey1" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE
- "fk_partitioned_fk_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
+ TABLE "fk_partitioned_fk_5" CONSTRAINT "fk_partitioned_fk_5_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE
+ TABLE "fk_partitioned_fk_5" CONSTRAINT "fk_partitioned_fk_5_a_fkey1" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE
+ TABLE "fk_partitioned_fk" CONSTRAINT "fk_partitioned_fk_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
-- verify that attaching a table checks that the existing data satisfies the
-- constraint
--
2.11.0
