From 87053b456eae36811d643e5ce8c1b03eae5b7091 Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Thu, 22 Nov 2018 19:30:22 -0300 Subject: [PATCH 5/5] Have psql not display redundant FKs --- 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 0a181b01d9..1ed73afaa8 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -2382,17 +2382,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; @@ -2404,10 +2415,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); } @@ -2416,13 +2437,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) @@ -2436,8 +2465,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 66c083a8e1..5de2449119 100644 --- a/src/test/regress/expected/foreign_key.out +++ b/src/test/regress/expected/foreign_key.out @@ -1667,7 +1667,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); @@ -1687,7 +1687,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 @@ -1698,7 +1698,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 @@ -1710,7 +1710,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, @@ -1734,7 +1734,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 @@ -1750,9 +1750,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