On 2018-Dec-04, Alvaro Herrera wrote: > On 2018-Dec-04, Alvaro Herrera wrote: > > > v2 attached. > > Oops.
One more oops: The version I posted was for pg11, and does not apply to master. This version applies to master. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 0a181b01d9..9d24fb77bc 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -1479,6 +1479,7 @@ describeOneTableDetails(const char *schemaname, bool rowsecurity; bool forcerowsecurity; bool hasoids; + bool ispartition; Oid tablespace; char *reloptions; char *reloftype; @@ -1501,7 +1502,24 @@ describeOneTableDetails(const char *schemaname, printfPQExpBuffer(&buf, "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, " "c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, " - "false AS relhasoids, %s, c.reltablespace, " + "false AS relhasoids, c.relispartition, %s, c.reltablespace, " + "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, " + "c.relpersistence, c.relreplident\n" + "FROM pg_catalog.pg_class c\n " + "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n" + "WHERE c.oid = '%s';", + (verbose ? + "pg_catalog.array_to_string(c.reloptions || " + "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n" + : "''"), + oid); + } + else if (pset.sversion >= 110000) + { + printfPQExpBuffer(&buf, + "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, " + "c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, " + "c.relhasoids, c.relispartition, %s, c.reltablespace, " "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, " "c.relpersistence, c.relreplident\n" "FROM pg_catalog.pg_class c\n " @@ -1518,7 +1536,7 @@ describeOneTableDetails(const char *schemaname, printfPQExpBuffer(&buf, "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, " "c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, " - "c.relhasoids, %s, c.reltablespace, " + "c.relhasoids, false, %s, c.reltablespace, " "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, " "c.relpersistence, c.relreplident\n" "FROM pg_catalog.pg_class c\n " @@ -1535,7 +1553,7 @@ describeOneTableDetails(const char *schemaname, printfPQExpBuffer(&buf, "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, " "c.relhastriggers, false, false, c.relhasoids, " - "%s, c.reltablespace, " + "false, %s, c.reltablespace, " "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, " "c.relpersistence, c.relreplident\n" "FROM pg_catalog.pg_class c\n " @@ -1552,7 +1570,7 @@ describeOneTableDetails(const char *schemaname, printfPQExpBuffer(&buf, "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, " "c.relhastriggers, false, false, c.relhasoids, " - "%s, c.reltablespace, " + "false, %s, c.reltablespace, " "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, " "c.relpersistence\n" "FROM pg_catalog.pg_class c\n " @@ -1569,7 +1587,7 @@ describeOneTableDetails(const char *schemaname, printfPQExpBuffer(&buf, "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, " "c.relhastriggers, false, false, c.relhasoids, " - "%s, c.reltablespace, " + "false, %s, c.reltablespace, " "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END\n" "FROM pg_catalog.pg_class c\n " "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n" @@ -1585,7 +1603,7 @@ describeOneTableDetails(const char *schemaname, printfPQExpBuffer(&buf, "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, " "c.relhastriggers, false, false, c.relhasoids, " - "%s, c.reltablespace\n" + "false, %s, c.reltablespace\n" "FROM pg_catalog.pg_class c\n " "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n" "WHERE c.oid = '%s';", @@ -1600,7 +1618,7 @@ describeOneTableDetails(const char *schemaname, printfPQExpBuffer(&buf, "SELECT relchecks, relkind, relhasindex, relhasrules, " "reltriggers <> 0, false, false, relhasoids, " - "%s, reltablespace\n" + "false, %s, reltablespace\n" "FROM pg_catalog.pg_class WHERE oid = '%s';", (verbose ? "pg_catalog.array_to_string(reloptions, E', ')" : "''"), @@ -1611,7 +1629,7 @@ describeOneTableDetails(const char *schemaname, printfPQExpBuffer(&buf, "SELECT relchecks, relkind, relhasindex, relhasrules, " "reltriggers <> 0, false, false, relhasoids, " - "'', reltablespace\n" + "false, '', reltablespace\n" "FROM pg_catalog.pg_class WHERE oid = '%s';", oid); } @@ -1620,7 +1638,7 @@ describeOneTableDetails(const char *schemaname, printfPQExpBuffer(&buf, "SELECT relchecks, relkind, relhasindex, relhasrules, " "reltriggers <> 0, false, false, relhasoids, " - "'', ''\n" + "false, '', ''\n" "FROM pg_catalog.pg_class WHERE oid = '%s';", oid); } @@ -1645,17 +1663,18 @@ describeOneTableDetails(const char *schemaname, tableinfo.rowsecurity = strcmp(PQgetvalue(res, 0, 5), "t") == 0; tableinfo.forcerowsecurity = strcmp(PQgetvalue(res, 0, 6), "t") == 0; tableinfo.hasoids = strcmp(PQgetvalue(res, 0, 7), "t") == 0; + tableinfo.ispartition = strcmp(PQgetvalue(res, 0, 8), "t") == 0; tableinfo.reloptions = (pset.sversion >= 80200) ? - pg_strdup(PQgetvalue(res, 0, 8)) : NULL; + pg_strdup(PQgetvalue(res, 0, 9)) : NULL; tableinfo.tablespace = (pset.sversion >= 80000) ? - atooid(PQgetvalue(res, 0, 9)) : 0; + atooid(PQgetvalue(res, 0, 10)) : 0; tableinfo.reloftype = (pset.sversion >= 90000 && - strcmp(PQgetvalue(res, 0, 10), "") != 0) ? - pg_strdup(PQgetvalue(res, 0, 10)) : NULL; + strcmp(PQgetvalue(res, 0, 11), "") != 0) ? + pg_strdup(PQgetvalue(res, 0, 11)) : NULL; tableinfo.relpersistence = (pset.sversion >= 90100) ? - *(PQgetvalue(res, 0, 11)) : 0; + *(PQgetvalue(res, 0, 12)) : 0; tableinfo.relreplident = (pset.sversion >= 90400) ? - *(PQgetvalue(res, 0, 12)) : 'd'; + *(PQgetvalue(res, 0, 13)) : 'd'; PQclear(res); res = NULL; @@ -2387,12 +2406,40 @@ describeOneTableDetails(const char *schemaname, 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); + if (pset.sversion >= 110000 && + (tableinfo.ispartition || tableinfo.relkind == RELKIND_PARTITIONED_TABLE)) + { + /* + * Note we put the constraints defined in this table first, + * followed by the constraints defined in ancestor partitioned + * tables. + */ + printfPQExpBuffer(&buf, + "WITH RECURSIVE constraints (oid, parent, conname, conrelid, level) as (\n" + " SELECT oid, conparentid, conname, conrelid, 0\n" + " FROM pg_catalog.pg_constraint WHERE contype = 'f' AND conrelid = '%s'\n" + " UNION\n" + " SELECT pc.oid, pc.conparentid, pc.conname, pc.conrelid, level+1\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, true),\n" + " conrelid::pg_catalog.regclass\n" + " FROM constraints\n" + " WHERE parent = 0\n" + "ORDER BY level DESC, conname;", + oid, oid); + } + else + { + printfPQExpBuffer(&buf, + "SELECT true, 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); + } + result = PSQLexec(buf.data); if (!result) goto error_return; @@ -2404,10 +2451,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); } @@ -2415,15 +2472,36 @@ describeOneTableDetails(const char *schemaname, PQclear(result); } - /* print incoming foreign-key references (none if no triggers) */ - if (tableinfo.hastriggers) + /* print incoming foreign-key references */ + 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;", - oid); + if (pset.sversion >= 110000) + { + printfPQExpBuffer(&buf, + "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 conname, conrelid::pg_catalog.regclass, pg_catalog.pg_get_constraintdef(oid)\n" + " FROM constraints\n" + " WHERE parent = 0\n" + "ORDER BY conname;", + oid); + } + else + { + 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;", + oid); + } + result = PSQLexec(buf.data); if (!result) goto error_return; diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out index 085c9aba11..6fdc648a9b 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 @@ -1722,8 +1722,8 @@ Foreign-key constraints: b | integer | | | Partition of: fk_partitioned_fk_4 FOR VALUES FROM (100, 100) TO (1000, 1000) Foreign-key constraints: + 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 "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 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, @@ -1745,9 +1745,9 @@ ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_5 FOR VALUES IN Partition of: fk_partitioned_fk FOR VALUES IN (4500) Partition key: RANGE (a) Foreign-key constraints: + 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 "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 Number of partitions: 1 (Use \d+ to list them.) -- verify that it works to reattaching a child with multiple candidate @@ -1762,10 +1762,10 @@ ALTER TABLE fk_partitioned_fk_5 ATTACH PARTITION fk_partitioned_fk_5_1 FOR VALUE b | integer | | | Partition of: fk_partitioned_fk_5 FOR VALUES FROM (0) TO (10) Foreign-key constraints: + 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 + 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 "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 -- verify that attaching a table checks that the existing data satisfies the -- constraint