public inbox for [email protected]help / color / mirror / Atom feed
[PATCH v3 2/2] fix psql display of FKs 8+ messages / 3 participants [nested] [flat]
* [PATCH v3 2/2] fix psql display of FKs @ 2019-02-26 21:57 Alvaro Herrera <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Alvaro Herrera @ 2019-02-26 21:57 UTC (permalink / raw) --- src/bin/psql/describe.c | 134 ++++++++++++++++------ src/test/regress/expected/foreign_key.out | 26 ++--- 2 files changed, 114 insertions(+), 46 deletions(-) diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 4da6719ce71..ecc597b94eb 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,33 @@ 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 >= 120000 && + (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, + "SELECT conrelid = '%s'::pg_catalog.regclass, conname,\n" + " pg_catalog.pg_get_constraintdef(oid, true),\n" + " conrelid::pg_catalog.regclass\n" + " FROM pg_constraint, pg_partition_ancestors('%s', 't')\n" + " WHERE conrelid = relid AND contype = 'f' AND conparentid = 0\n" + "ORDER BY 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 +2444,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 +2465,33 @@ 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 >= 120000 && + (tableinfo.ispartition || + tableinfo.relkind == RELKIND_PARTITIONED_TABLE)) + { + printfPQExpBuffer(&buf, + "SELECT conname,\n" + " conrelid::pg_catalog.regclass,\n" + " pg_catalog.pg_get_constraintdef(oid, true)\n" + " FROM pg_constraint, pg_partition_ancestors('%s', 't')\n" + " WHERE confrelid = relid AND contype = 'f' AND conparentid = 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 4a2f9af3287..29aca6c4d7b 100644 --- a/src/test/regress/expected/foreign_key.out +++ b/src/test/regress/expected/foreign_key.out @@ -1728,7 +1728,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); @@ -1748,7 +1748,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 @@ -1759,7 +1759,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 @@ -1771,7 +1771,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, @@ -1795,7 +1795,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 @@ -1811,9 +1811,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 @@ -1849,7 +1849,7 @@ alter table fkpart0.fk_part add foreign key (a) references fkpart0.pkey; a | integer | | | Partition of: fkpart0.fk_part FOR VALUES IN (1) Foreign-key constraints: - "fk_part_1_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) + TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) alter table fkpart0.fk_part_1 drop constraint fk_part_1_a_fkey; ERROR: cannot drop inherited constraint "fk_part_1_a_fkey" of relation "fk_part_1" @@ -1861,7 +1861,7 @@ ERROR: cannot drop inherited constraint "fk_part_1_a_fkey" of relation "fk_part Partition of: fkpart0.fk_part FOR VALUES IN (2, 3) Partition key: LIST (a) Foreign-key constraints: - "fk_part_23_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) + TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) Number of partitions: 1 (Use \d+ to list them.) \d fkpart0.fk_part_23_2 \\ -- should have only one FK @@ -1871,7 +1871,7 @@ Number of partitions: 1 (Use \d+ to list them.) a | integer | | | Partition of: fkpart0.fk_part_23 FOR VALUES IN (2) Foreign-key constraints: - "fk_part_23_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) + TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) alter table fkpart0.fk_part_23 drop constraint fk_part_23_a_fkey; ERROR: cannot drop inherited constraint "fk_part_23_a_fkey" of relation "fk_part_23" @@ -1885,7 +1885,7 @@ create table fkpart0.fk_part_4 partition of fkpart0.fk_part for values in (4); a | integer | | | Partition of: fkpart0.fk_part FOR VALUES IN (4) Foreign-key constraints: - "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) + TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) alter table fkpart0.fk_part_4 drop constraint fk_part_a_fkey; ERROR: cannot drop inherited constraint "fk_part_a_fkey" of relation "fk_part_4" @@ -1901,7 +1901,7 @@ create table fkpart0.fk_part_56_5 partition of fkpart0.fk_part_56 Partition of: fkpart0.fk_part FOR VALUES IN (5, 6) Partition key: LIST (a) Foreign-key constraints: - "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) + TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) Number of partitions: 1 (Use \d+ to list them.) alter table fkpart0.fk_part_56 drop constraint fk_part_a_fkey; -- 2.17.1 --cWoXeonUoKmBZSoM-- ^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH v5] fix psql display of FKs @ 2019-02-26 21:57 Alvaro Herrera <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Alvaro Herrera @ 2019-02-26 21:57 UTC (permalink / raw) --- src/bin/psql/describe.c | 134 ++++++++++++++++------ src/test/regress/expected/foreign_key.out | 26 ++--- 2 files changed, 114 insertions(+), 46 deletions(-) diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 4da6719ce71..6dac5fa3009 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,33 @@ 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 >= 120000 && + (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, + "SELECT conrelid = '%s'::pg_catalog.regclass, conname,\n" + " pg_catalog.pg_get_constraintdef(oid, true),\n" + " conrelid::pg_catalog.regclass\n" + " FROM pg_constraint, pg_partition_ancestors('%s')\n" + " WHERE conrelid = relid AND contype = 'f' AND conparentid = 0\n" + "ORDER BY 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 +2444,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 +2465,33 @@ 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 >= 120000 && + (tableinfo.ispartition || + tableinfo.relkind == RELKIND_PARTITIONED_TABLE)) + { + printfPQExpBuffer(&buf, + "SELECT conname,\n" + " conrelid::pg_catalog.regclass,\n" + " pg_catalog.pg_get_constraintdef(oid, true)\n" + " FROM pg_constraint, pg_partition_ancestors('%s')\n" + " WHERE confrelid = relid AND contype = 'f' AND conparentid = 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 bf2c91d9f0e..4a111f3290c 100644 --- a/src/test/regress/expected/foreign_key.out +++ b/src/test/regress/expected/foreign_key.out @@ -1741,7 +1741,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); @@ -1761,7 +1761,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 @@ -1772,7 +1772,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 @@ -1784,7 +1784,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, @@ -1808,7 +1808,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 @@ -1824,9 +1824,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 @@ -1862,7 +1862,7 @@ alter table fkpart0.fk_part add foreign key (a) references fkpart0.pkey; a | integer | | | Partition of: fkpart0.fk_part FOR VALUES IN (1) Foreign-key constraints: - "fk_part_1_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) + TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) alter table fkpart0.fk_part_1 drop constraint fk_part_1_a_fkey; ERROR: cannot drop inherited constraint "fk_part_1_a_fkey" of relation "fk_part_1" @@ -1874,7 +1874,7 @@ ERROR: cannot drop inherited constraint "fk_part_1_a_fkey" of relation "fk_part Partition of: fkpart0.fk_part FOR VALUES IN (2, 3) Partition key: LIST (a) Foreign-key constraints: - "fk_part_23_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) + TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) Number of partitions: 1 (Use \d+ to list them.) \d fkpart0.fk_part_23_2 \\ -- should have only one FK @@ -1884,7 +1884,7 @@ Number of partitions: 1 (Use \d+ to list them.) a | integer | | | Partition of: fkpart0.fk_part_23 FOR VALUES IN (2) Foreign-key constraints: - "fk_part_23_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) + TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) alter table fkpart0.fk_part_23 drop constraint fk_part_23_a_fkey; ERROR: cannot drop inherited constraint "fk_part_23_a_fkey" of relation "fk_part_23" @@ -1898,7 +1898,7 @@ create table fkpart0.fk_part_4 partition of fkpart0.fk_part for values in (4); a | integer | | | Partition of: fkpart0.fk_part FOR VALUES IN (4) Foreign-key constraints: - "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) + TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) alter table fkpart0.fk_part_4 drop constraint fk_part_a_fkey; ERROR: cannot drop inherited constraint "fk_part_a_fkey" of relation "fk_part_4" @@ -1914,7 +1914,7 @@ create table fkpart0.fk_part_56_5 partition of fkpart0.fk_part_56 Partition of: fkpart0.fk_part FOR VALUES IN (5, 6) Partition key: LIST (a) Foreign-key constraints: - "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) + TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) Number of partitions: 1 (Use \d+ to list them.) alter table fkpart0.fk_part_56 drop constraint fk_part_a_fkey; -- 2.17.1 --NzB8fVQJ5HfG6fxh-- ^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH v10] fix psql display of FKs @ 2019-03-22 22:13 Alvaro Herrera <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Alvaro Herrera @ 2019-03-22 22:13 UTC (permalink / raw) --- src/bin/psql/describe.c | 156 ++++++++++++++++------ src/test/regress/expected/foreign_key.out | 38 ++++-- src/test/regress/sql/foreign_key.sql | 2 + 3 files changed, 145 insertions(+), 51 deletions(-) diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index fd8ebee8cd3..91f2306734e 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; @@ -1502,7 +1503,7 @@ 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, am.amname\n" "FROM pg_catalog.pg_class c\n " @@ -1515,12 +1516,29 @@ describeOneTableDetails(const char *schemaname, : "''"), oid); } + else if (pset.sversion >= 100000) + { + 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 " + "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 >= 90500) { 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 as 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 " @@ -1537,7 +1555,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 as 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 " @@ -1554,7 +1572,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 as relispartition, %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 " @@ -1571,7 +1589,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 as relispartition, %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" @@ -1587,7 +1605,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 as relispartition, %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';", @@ -1602,7 +1620,7 @@ describeOneTableDetails(const char *schemaname, printfPQExpBuffer(&buf, "SELECT relchecks, relkind, relhasindex, relhasrules, " "reltriggers <> 0, false, false, relhasoids, " - "%s, reltablespace\n" + "false as relispartition, %s, reltablespace\n" "FROM pg_catalog.pg_class WHERE oid = '%s';", (verbose ? "pg_catalog.array_to_string(reloptions, E', ')" : "''"), @@ -1613,7 +1631,7 @@ describeOneTableDetails(const char *schemaname, printfPQExpBuffer(&buf, "SELECT relchecks, relkind, relhasindex, relhasrules, " "reltriggers <> 0, false, false, relhasoids, " - "'', reltablespace\n" + "false as relispartition, '', reltablespace\n" "FROM pg_catalog.pg_class WHERE oid = '%s';", oid); } @@ -1622,7 +1640,7 @@ describeOneTableDetails(const char *schemaname, printfPQExpBuffer(&buf, "SELECT relchecks, relkind, relhasindex, relhasrules, " "reltriggers <> 0, false, false, relhasoids, " - "'', ''\n" + "false as relispartition, '', ''\n" "FROM pg_catalog.pg_class WHERE oid = '%s';", oid); } @@ -1647,20 +1665,21 @@ 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'; if (pset.sversion >= 120000) - tableinfo.relam = PQgetisnull(res, 0, 13) ? - (char *) NULL : pg_strdup(PQgetvalue(res, 0, 13)); + tableinfo.relam = PQgetisnull(res, 0, 14) ? + (char *) NULL : pg_strdup(PQgetvalue(res, 0, 14)); else tableinfo.relam = NULL; PQclear(res); @@ -2394,12 +2413,36 @@ 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 >= 120000 && + (tableinfo.ispartition || tableinfo.relkind == RELKIND_PARTITIONED_TABLE)) + { + /* + * Put the constraints defined in this table first, followed + * by the constraints defined in ancestor partitioned tables. + */ + printfPQExpBuffer(&buf, + "SELECT conrelid = '%s'::pg_catalog.regclass AS sametable,\n" + " conname,\n" + " pg_catalog.pg_get_constraintdef(oid, true) AS condef,\n" + " conrelid::pg_catalog.regclass AS ontable\n" + " FROM pg_catalog.pg_constraint,\n" + " pg_catalog.pg_partition_ancestors('%s')\n" + " WHERE conrelid = relid AND contype = 'f' AND conparentid = 0\n" + "ORDER BY sametable DESC, conname;", + oid, oid); + } + else + { + printfPQExpBuffer(&buf, + "SELECT true as sametable, conname,\n" + " pg_catalog.pg_get_constraintdef(r.oid, true) as condef,\n" + " conrelid::regclass AS ontable\n" + "FROM pg_catalog.pg_constraint r\n" + "WHERE r.conrelid = '%s' AND r.contype = 'f'\n" + "ORDER BY conname;", + oid); + } + result = PSQLexec(buf.data); if (!result) goto error_return; @@ -2408,13 +2451,28 @@ describeOneTableDetails(const char *schemaname, if (tuples > 0) { + int i_sametable = PQfnumber(result, "sametable"), + i_conname = PQfnumber(result, "conname"), + i_condef = PQfnumber(result, "condef"), + i_ontable = PQfnumber(result, "ontable"); + 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, i_sametable), "f") == 0) + printfPQExpBuffer(&buf, " TABLE \"%s\" CONSTRAINT \"%s\" %s", + PQgetvalue(result, i, i_ontable), + PQgetvalue(result, i, i_conname), + PQgetvalue(result, i, i_condef)); + else + printfPQExpBuffer(&buf, " \"%s\" %s", + PQgetvalue(result, i, i_conname), + PQgetvalue(result, i, i_condef)); printTableAddFooter(&cont, buf.data); } @@ -2422,15 +2480,33 @@ 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 >= 120000) + { + printfPQExpBuffer(&buf, + "SELECT conname, conrelid::pg_catalog.regclass AS ontable,\n" + " pg_catalog.pg_get_constraintdef(oid, true) AS condef\n" + " FROM pg_catalog.pg_constraint c\n" + " WHERE confrelid IN (SELECT pg_catalog.pg_partition_ancestors('%s')\n" + " UNION ALL VALUES (regclass '%s'))\n" + " AND contype = 'f' AND conparentid = 0\n" + "ORDER BY conname;", + oid, oid); + } + else + { + printfPQExpBuffer(&buf, + "SELECT conname, conrelid::pg_catalog.regclass AS ontable,\n" + " pg_catalog.pg_get_constraintdef(oid, true) AS condef\n" + " FROM pg_catalog.pg_constraint\n" + " WHERE confrelid = %s AND contype = 'f'\n" + "ORDER BY conname;", + oid); + } + result = PSQLexec(buf.data); if (!result) goto error_return; @@ -2439,13 +2515,17 @@ describeOneTableDetails(const char *schemaname, if (tuples > 0) { + int i_conname = PQfnumber(result, "conname"), + i_ontable = PQfnumber(result, "ontable"), + i_condef = PQfnumber(result, "condef"); + printTableAddFooter(&cont, _("Referenced by:")); for (i = 0; i < tuples; i++) { printfPQExpBuffer(&buf, " TABLE \"%s\" CONSTRAINT \"%s\" %s", - PQgetvalue(result, i, 1), - PQgetvalue(result, i, 0), - PQgetvalue(result, i, 2)); + PQgetvalue(result, i, i_ontable), + PQgetvalue(result, i, i_conname), + PQgetvalue(result, i, i_condef)); printTableAddFooter(&cont, buf.data); } diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out index 15cb47976ba..4f7acb9b1ef 100644 --- a/src/test/regress/expected/foreign_key.out +++ b/src/test/regress/expected/foreign_key.out @@ -1627,6 +1627,18 @@ DETAIL: Key (a, b)=(1500, 1501) is still referenced from table "fk_partitioned_ UPDATE fk_notpartitioned_pk SET b = 2504 WHERE a = 2500; ERROR: update or delete on table "fk_notpartitioned_pk" violates foreign key constraint "fk_partitioned_fk_a_b_fkey" on table "fk_partitioned_fk" DETAIL: Key (a, b)=(2500, 2502) is still referenced from table "fk_partitioned_fk". +-- check psql behavior +\d fk_notpartitioned_pk + Table "public.fk_notpartitioned_pk" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | integer | | not null | + b | integer | | not null | +Indexes: + "fk_notpartitioned_pk_pkey" PRIMARY KEY, btree (a, b) +Referenced by: + TABLE "fk_partitioned_fk" CONSTRAINT "fk_partitioned_fk_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) + ALTER TABLE fk_partitioned_fk DROP CONSTRAINT fk_partitioned_fk_a_b_fkey; -- done. DROP TABLE fk_notpartitioned_pk, fk_partitioned_fk; @@ -1795,7 +1807,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_b_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_b_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); @@ -1815,7 +1827,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_b_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_b_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 @@ -1826,7 +1838,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_b_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_b_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 @@ -1838,7 +1850,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_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE SET NULL - "fk_partitioned_fk_4_a_b_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_b_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, @@ -1862,7 +1874,7 @@ Partition key: RANGE (a) Foreign-key constraints: "fk_partitioned_fk_5_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE "fk_partitioned_fk_5_a_b_fkey1" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE - "fk_partitioned_fk_a_b_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_b_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 @@ -1878,9 +1890,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_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) - "fk_partitioned_fk_5_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE - "fk_partitioned_fk_5_a_b_fkey1" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE - "fk_partitioned_fk_a_b_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_b_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_b_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_b_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 @@ -1916,7 +1928,7 @@ alter table fkpart0.fk_part add foreign key (a) references fkpart0.pkey; a | integer | | | Partition of: fkpart0.fk_part FOR VALUES IN (1) Foreign-key constraints: - "fk_part_1_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) + TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) alter table fkpart0.fk_part_1 drop constraint fk_part_1_a_fkey; ERROR: cannot drop inherited constraint "fk_part_1_a_fkey" of relation "fk_part_1" @@ -1928,7 +1940,7 @@ ERROR: cannot drop inherited constraint "fk_part_1_a_fkey" of relation "fk_part Partition of: fkpart0.fk_part FOR VALUES IN (2, 3) Partition key: LIST (a) Foreign-key constraints: - "fk_part_23_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) + TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) Number of partitions: 1 (Use \d+ to list them.) \d fkpart0.fk_part_23_2 \\ -- should have only one FK @@ -1938,7 +1950,7 @@ Number of partitions: 1 (Use \d+ to list them.) a | integer | | | Partition of: fkpart0.fk_part_23 FOR VALUES IN (2) Foreign-key constraints: - "fk_part_23_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) + TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) alter table fkpart0.fk_part_23 drop constraint fk_part_23_a_fkey; ERROR: cannot drop inherited constraint "fk_part_23_a_fkey" of relation "fk_part_23" @@ -1952,7 +1964,7 @@ create table fkpart0.fk_part_4 partition of fkpart0.fk_part for values in (4); a | integer | | | Partition of: fkpart0.fk_part FOR VALUES IN (4) Foreign-key constraints: - "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) + TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) alter table fkpart0.fk_part_4 drop constraint fk_part_a_fkey; ERROR: cannot drop inherited constraint "fk_part_a_fkey" of relation "fk_part_4" @@ -1968,7 +1980,7 @@ create table fkpart0.fk_part_56_5 partition of fkpart0.fk_part_56 Partition of: fkpart0.fk_part FOR VALUES IN (5, 6) Partition key: LIST (a) Foreign-key constraints: - "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) + TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) Number of partitions: 1 (Use \d+ to list them.) alter table fkpart0.fk_part_56 drop constraint fk_part_a_fkey; diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql index 4adcda582b6..a5c7e147a7f 100644 --- a/src/test/regress/sql/foreign_key.sql +++ b/src/test/regress/sql/foreign_key.sql @@ -1218,6 +1218,8 @@ UPDATE fk_partitioned_fk SET a = a + 1 WHERE a = 2501; UPDATE fk_notpartitioned_pk SET b = 502 WHERE a = 500; UPDATE fk_notpartitioned_pk SET b = 1502 WHERE a = 1500; UPDATE fk_notpartitioned_pk SET b = 2504 WHERE a = 2500; +-- check psql behavior +\d fk_notpartitioned_pk ALTER TABLE fk_partitioned_fk DROP CONSTRAINT fk_partitioned_fk_a_b_fkey; -- done. DROP TABLE fk_notpartitioned_pk, fk_partitioned_fk; -- 2.17.1 --n8g4imXOkfNTN/H1-- ^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH v8] fix psql display of FKs @ 2019-03-22 22:13 Alvaro Herrera <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Alvaro Herrera @ 2019-03-22 22:13 UTC (permalink / raw) --- src/bin/psql/describe.c | 140 ++++++++++++++++------ src/test/regress/expected/foreign_key.out | 26 ++-- 2 files changed, 118 insertions(+), 48 deletions(-) diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index fd8ebee8cd3..734a718af21 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; @@ -1502,7 +1503,25 @@ 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, am.amname\n" + "FROM pg_catalog.pg_class c\n " + "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n" + "LEFT JOIN pg_catalog.pg_am am ON (c.relam = am.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, am.amname\n" "FROM pg_catalog.pg_class c\n " @@ -1520,7 +1539,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 " @@ -1537,7 +1556,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 " @@ -1554,7 +1573,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 " @@ -1571,7 +1590,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" @@ -1587,7 +1606,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';", @@ -1602,7 +1621,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', ')" : "''"), @@ -1613,7 +1632,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); } @@ -1622,7 +1641,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); } @@ -1647,20 +1666,21 @@ 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'; if (pset.sversion >= 120000) - tableinfo.relam = PQgetisnull(res, 0, 13) ? - (char *) NULL : pg_strdup(PQgetvalue(res, 0, 13)); + tableinfo.relam = PQgetisnull(res, 0, 14) ? + (char *) NULL : pg_strdup(PQgetvalue(res, 0, 14)); else tableinfo.relam = NULL; PQclear(res); @@ -2394,12 +2414,33 @@ 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 >= 120000 && + (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, + "SELECT conrelid = '%s'::pg_catalog.regclass, conname,\n" + " pg_catalog.pg_get_constraintdef(oid, true),\n" + " conrelid::pg_catalog.regclass\n" + " FROM pg_constraint, pg_partition_ancestors('%s')\n" + " WHERE conrelid = relid AND contype = 'f' AND conparentid = 0\n" + "ORDER BY 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; @@ -2411,10 +2452,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); } @@ -2422,15 +2473,34 @@ 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 >= 120000 && + (tableinfo.ispartition || + tableinfo.relkind == RELKIND_PARTITIONED_TABLE)) + { + printfPQExpBuffer(&buf, + "SELECT conname,\n" + " conrelid::pg_catalog.regclass,\n" + " pg_catalog.pg_get_constraintdef(oid, true)\n" + " FROM pg_constraint, pg_partition_ancestors('%s')\n" + " WHERE confrelid = relid AND contype = 'f' AND conparentid = 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' AND conparentid = 0\n" + "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 401514a3e00..2c99f033dfc 100644 --- a/src/test/regress/expected/foreign_key.out +++ b/src/test/regress/expected/foreign_key.out @@ -1795,7 +1795,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_b_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_b_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); @@ -1815,7 +1815,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_b_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_b_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 @@ -1826,7 +1826,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_b_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_b_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 @@ -1838,7 +1838,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_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE SET NULL - "fk_partitioned_fk_4_a_b_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_b_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, @@ -1862,7 +1862,7 @@ Partition key: RANGE (a) Foreign-key constraints: "fk_partitioned_fk_5_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE "fk_partitioned_fk_5_a_b_fkey1" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE - "fk_partitioned_fk_a_b_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_b_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 @@ -1878,9 +1878,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_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) - "fk_partitioned_fk_5_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE - "fk_partitioned_fk_5_a_b_fkey1" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE - "fk_partitioned_fk_a_b_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_b_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_b_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_b_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 @@ -1916,7 +1916,7 @@ alter table fkpart0.fk_part add foreign key (a) references fkpart0.pkey; a | integer | | | Partition of: fkpart0.fk_part FOR VALUES IN (1) Foreign-key constraints: - "fk_part_1_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) + TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) alter table fkpart0.fk_part_1 drop constraint fk_part_1_a_fkey; ERROR: cannot drop inherited constraint "fk_part_1_a_fkey" of relation "fk_part_1" @@ -1928,7 +1928,7 @@ ERROR: cannot drop inherited constraint "fk_part_1_a_fkey" of relation "fk_part Partition of: fkpart0.fk_part FOR VALUES IN (2, 3) Partition key: LIST (a) Foreign-key constraints: - "fk_part_23_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) + TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) Number of partitions: 1 (Use \d+ to list them.) \d fkpart0.fk_part_23_2 \\ -- should have only one FK @@ -1938,7 +1938,7 @@ Number of partitions: 1 (Use \d+ to list them.) a | integer | | | Partition of: fkpart0.fk_part_23 FOR VALUES IN (2) Foreign-key constraints: - "fk_part_23_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) + TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) alter table fkpart0.fk_part_23 drop constraint fk_part_23_a_fkey; ERROR: cannot drop inherited constraint "fk_part_23_a_fkey" of relation "fk_part_23" @@ -1952,7 +1952,7 @@ create table fkpart0.fk_part_4 partition of fkpart0.fk_part for values in (4); a | integer | | | Partition of: fkpart0.fk_part FOR VALUES IN (4) Foreign-key constraints: - "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) + TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) alter table fkpart0.fk_part_4 drop constraint fk_part_a_fkey; ERROR: cannot drop inherited constraint "fk_part_a_fkey" of relation "fk_part_4" @@ -1968,7 +1968,7 @@ create table fkpart0.fk_part_56_5 partition of fkpart0.fk_part_56 Partition of: fkpart0.fk_part FOR VALUES IN (5, 6) Partition key: LIST (a) Foreign-key constraints: - "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) + TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) Number of partitions: 1 (Use \d+ to list them.) alter table fkpart0.fk_part_56 drop constraint fk_part_a_fkey; -- 2.17.1 --BOKacYhQ+x31HxR3-- ^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH v9] fix psql display of FKs @ 2019-03-22 22:13 Alvaro Herrera <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Alvaro Herrera @ 2019-03-22 22:13 UTC (permalink / raw) --- src/bin/psql/describe.c | 155 ++++++++++++++++------ src/test/regress/expected/foreign_key.out | 38 ++++-- src/test/regress/sql/foreign_key.sql | 2 + 3 files changed, 144 insertions(+), 51 deletions(-) diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index fd8ebee8cd3..fc404bbaab8 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; @@ -1502,7 +1503,7 @@ 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, am.amname\n" "FROM pg_catalog.pg_class c\n " @@ -1515,12 +1516,29 @@ describeOneTableDetails(const char *schemaname, : "''"), oid); } + else if (pset.sversion >= 100000) + { + 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 " + "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 >= 90500) { 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 as 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 " @@ -1537,7 +1555,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 as 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 " @@ -1554,7 +1572,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 as relispartition, %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 " @@ -1571,7 +1589,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 as relispartition, %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" @@ -1587,7 +1605,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 as relispartition, %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';", @@ -1602,7 +1620,7 @@ describeOneTableDetails(const char *schemaname, printfPQExpBuffer(&buf, "SELECT relchecks, relkind, relhasindex, relhasrules, " "reltriggers <> 0, false, false, relhasoids, " - "%s, reltablespace\n" + "false as relispartition, %s, reltablespace\n" "FROM pg_catalog.pg_class WHERE oid = '%s';", (verbose ? "pg_catalog.array_to_string(reloptions, E', ')" : "''"), @@ -1613,7 +1631,7 @@ describeOneTableDetails(const char *schemaname, printfPQExpBuffer(&buf, "SELECT relchecks, relkind, relhasindex, relhasrules, " "reltriggers <> 0, false, false, relhasoids, " - "'', reltablespace\n" + "false as relispartition, '', reltablespace\n" "FROM pg_catalog.pg_class WHERE oid = '%s';", oid); } @@ -1622,7 +1640,7 @@ describeOneTableDetails(const char *schemaname, printfPQExpBuffer(&buf, "SELECT relchecks, relkind, relhasindex, relhasrules, " "reltriggers <> 0, false, false, relhasoids, " - "'', ''\n" + "false as relispartition, '', ''\n" "FROM pg_catalog.pg_class WHERE oid = '%s';", oid); } @@ -1647,20 +1665,21 @@ 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'; if (pset.sversion >= 120000) - tableinfo.relam = PQgetisnull(res, 0, 13) ? - (char *) NULL : pg_strdup(PQgetvalue(res, 0, 13)); + tableinfo.relam = PQgetisnull(res, 0, 14) ? + (char *) NULL : pg_strdup(PQgetvalue(res, 0, 14)); else tableinfo.relam = NULL; PQclear(res); @@ -2394,12 +2413,36 @@ 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 >= 120000 && + (tableinfo.ispartition || tableinfo.relkind == RELKIND_PARTITIONED_TABLE)) + { + /* + * Put the constraints defined in this table first, followed + * by the constraints defined in ancestor partitioned tables. + */ + printfPQExpBuffer(&buf, + "SELECT conrelid = '%s'::pg_catalog.regclass AS sametable,\n" + " conname,\n" + " pg_catalog.pg_get_constraintdef(oid, true) AS condef,\n" + " conrelid::pg_catalog.regclass AS ontable\n" + " FROM pg_catalog.pg_constraint,\n" + " pg_catalog.pg_partition_ancestors('%s')\n" + " WHERE conrelid = relid AND contype = 'f' AND conparentid = 0\n" + "ORDER BY sametable DESC, conname;", + oid, oid); + } + else + { + printfPQExpBuffer(&buf, + "SELECT true as sametable, conname,\n" + " pg_catalog.pg_get_constraintdef(r.oid, true) as condef,\n" + " conrelid::regclass AS ontable\n" + "FROM pg_catalog.pg_constraint r\n" + "WHERE r.conrelid = '%s' AND r.contype = 'f'\n" + "ORDER BY conname;", + oid); + } + result = PSQLexec(buf.data); if (!result) goto error_return; @@ -2408,13 +2451,28 @@ describeOneTableDetails(const char *schemaname, if (tuples > 0) { + int i_sametable = PQfnumber(result, "sametable"), + i_conname = PQfnumber(result, "conname"), + i_condef = PQfnumber(result, "condef"), + i_ontable = PQfnumber(result, "ontable"); + 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, i_sametable), "f") == 0) + printfPQExpBuffer(&buf, " TABLE \"%s\" CONSTRAINT \"%s\" %s", + PQgetvalue(result, i, i_ontable), + PQgetvalue(result, i, i_conname), + PQgetvalue(result, i, i_condef)); + else + printfPQExpBuffer(&buf, " \"%s\" %s", + PQgetvalue(result, i, i_conname), + PQgetvalue(result, i, i_condef)); printTableAddFooter(&cont, buf.data); } @@ -2422,15 +2480,32 @@ 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 >= 120000) + { + printfPQExpBuffer(&buf, + "SELECT conname, conrelid::pg_catalog.regclass AS ontable,\n" + " pg_catalog.pg_get_constraintdef(oid, true) AS condef\n" + " FROM pg_catalog.pg_constraint c LEFT JOIN \n" + " pg_catalog.pg_partition_ancestors(c.confrelid) ON (true)\n" + " WHERE confrelid = '%s' AND contype = 'f' AND conparentid = 0\n" + "ORDER BY conname;", + oid); + } + else + { + printfPQExpBuffer(&buf, + "SELECT conname, conrelid::pg_catalog.regclass AS ontable,\n" + " pg_catalog.pg_get_constraintdef(oid, true) AS condef\n" + " FROM pg_catalog.pg_constraint\n" + " WHERE confrelid = %s AND contype = 'f'\n" + "ORDER BY conname;", + oid); + } + result = PSQLexec(buf.data); if (!result) goto error_return; @@ -2439,13 +2514,17 @@ describeOneTableDetails(const char *schemaname, if (tuples > 0) { + int i_conname = PQfnumber(result, "conname"), + i_ontable = PQfnumber(result, "ontable"), + i_condef = PQfnumber(result, "condef"); + printTableAddFooter(&cont, _("Referenced by:")); for (i = 0; i < tuples; i++) { printfPQExpBuffer(&buf, " TABLE \"%s\" CONSTRAINT \"%s\" %s", - PQgetvalue(result, i, 1), - PQgetvalue(result, i, 0), - PQgetvalue(result, i, 2)); + PQgetvalue(result, i, i_ontable), + PQgetvalue(result, i, i_conname), + PQgetvalue(result, i, i_condef)); printTableAddFooter(&cont, buf.data); } diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out index 15cb47976ba..4f7acb9b1ef 100644 --- a/src/test/regress/expected/foreign_key.out +++ b/src/test/regress/expected/foreign_key.out @@ -1627,6 +1627,18 @@ DETAIL: Key (a, b)=(1500, 1501) is still referenced from table "fk_partitioned_ UPDATE fk_notpartitioned_pk SET b = 2504 WHERE a = 2500; ERROR: update or delete on table "fk_notpartitioned_pk" violates foreign key constraint "fk_partitioned_fk_a_b_fkey" on table "fk_partitioned_fk" DETAIL: Key (a, b)=(2500, 2502) is still referenced from table "fk_partitioned_fk". +-- check psql behavior +\d fk_notpartitioned_pk + Table "public.fk_notpartitioned_pk" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | integer | | not null | + b | integer | | not null | +Indexes: + "fk_notpartitioned_pk_pkey" PRIMARY KEY, btree (a, b) +Referenced by: + TABLE "fk_partitioned_fk" CONSTRAINT "fk_partitioned_fk_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) + ALTER TABLE fk_partitioned_fk DROP CONSTRAINT fk_partitioned_fk_a_b_fkey; -- done. DROP TABLE fk_notpartitioned_pk, fk_partitioned_fk; @@ -1795,7 +1807,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_b_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_b_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); @@ -1815,7 +1827,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_b_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_b_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 @@ -1826,7 +1838,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_b_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_b_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 @@ -1838,7 +1850,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_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE SET NULL - "fk_partitioned_fk_4_a_b_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_b_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, @@ -1862,7 +1874,7 @@ Partition key: RANGE (a) Foreign-key constraints: "fk_partitioned_fk_5_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE "fk_partitioned_fk_5_a_b_fkey1" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE - "fk_partitioned_fk_a_b_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_b_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 @@ -1878,9 +1890,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_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) - "fk_partitioned_fk_5_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE - "fk_partitioned_fk_5_a_b_fkey1" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE - "fk_partitioned_fk_a_b_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_b_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_b_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_b_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 @@ -1916,7 +1928,7 @@ alter table fkpart0.fk_part add foreign key (a) references fkpart0.pkey; a | integer | | | Partition of: fkpart0.fk_part FOR VALUES IN (1) Foreign-key constraints: - "fk_part_1_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) + TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) alter table fkpart0.fk_part_1 drop constraint fk_part_1_a_fkey; ERROR: cannot drop inherited constraint "fk_part_1_a_fkey" of relation "fk_part_1" @@ -1928,7 +1940,7 @@ ERROR: cannot drop inherited constraint "fk_part_1_a_fkey" of relation "fk_part Partition of: fkpart0.fk_part FOR VALUES IN (2, 3) Partition key: LIST (a) Foreign-key constraints: - "fk_part_23_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) + TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) Number of partitions: 1 (Use \d+ to list them.) \d fkpart0.fk_part_23_2 \\ -- should have only one FK @@ -1938,7 +1950,7 @@ Number of partitions: 1 (Use \d+ to list them.) a | integer | | | Partition of: fkpart0.fk_part_23 FOR VALUES IN (2) Foreign-key constraints: - "fk_part_23_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) + TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) alter table fkpart0.fk_part_23 drop constraint fk_part_23_a_fkey; ERROR: cannot drop inherited constraint "fk_part_23_a_fkey" of relation "fk_part_23" @@ -1952,7 +1964,7 @@ create table fkpart0.fk_part_4 partition of fkpart0.fk_part for values in (4); a | integer | | | Partition of: fkpart0.fk_part FOR VALUES IN (4) Foreign-key constraints: - "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) + TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) alter table fkpart0.fk_part_4 drop constraint fk_part_a_fkey; ERROR: cannot drop inherited constraint "fk_part_a_fkey" of relation "fk_part_4" @@ -1968,7 +1980,7 @@ create table fkpart0.fk_part_56_5 partition of fkpart0.fk_part_56 Partition of: fkpart0.fk_part FOR VALUES IN (5, 6) Partition key: LIST (a) Foreign-key constraints: - "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) + TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) Number of partitions: 1 (Use \d+ to list them.) alter table fkpart0.fk_part_56 drop constraint fk_part_a_fkey; diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql index 4adcda582b6..a5c7e147a7f 100644 --- a/src/test/regress/sql/foreign_key.sql +++ b/src/test/regress/sql/foreign_key.sql @@ -1218,6 +1218,8 @@ UPDATE fk_partitioned_fk SET a = a + 1 WHERE a = 2501; UPDATE fk_notpartitioned_pk SET b = 502 WHERE a = 500; UPDATE fk_notpartitioned_pk SET b = 1502 WHERE a = 1500; UPDATE fk_notpartitioned_pk SET b = 2504 WHERE a = 2500; +-- check psql behavior +\d fk_notpartitioned_pk ALTER TABLE fk_partitioned_fk DROP CONSTRAINT fk_partitioned_fk_a_b_fkey; -- done. DROP TABLE fk_notpartitioned_pk, fk_partitioned_fk; -- 2.17.1 --82I3+IH0IqGh5yIs-- ^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH v6] fix psql display of FKs @ 2019-03-22 22:13 Alvaro Herrera <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Alvaro Herrera @ 2019-03-22 22:13 UTC (permalink / raw) --- src/bin/psql/describe.c | 140 ++++++++++++++++------ src/test/regress/expected/foreign_key.out | 10 +- 2 files changed, 110 insertions(+), 40 deletions(-) diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index fd8ebee8cd3..734a718af21 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; @@ -1502,7 +1503,25 @@ 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, am.amname\n" + "FROM pg_catalog.pg_class c\n " + "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n" + "LEFT JOIN pg_catalog.pg_am am ON (c.relam = am.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, am.amname\n" "FROM pg_catalog.pg_class c\n " @@ -1520,7 +1539,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 " @@ -1537,7 +1556,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 " @@ -1554,7 +1573,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 " @@ -1571,7 +1590,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" @@ -1587,7 +1606,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';", @@ -1602,7 +1621,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', ')" : "''"), @@ -1613,7 +1632,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); } @@ -1622,7 +1641,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); } @@ -1647,20 +1666,21 @@ 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'; if (pset.sversion >= 120000) - tableinfo.relam = PQgetisnull(res, 0, 13) ? - (char *) NULL : pg_strdup(PQgetvalue(res, 0, 13)); + tableinfo.relam = PQgetisnull(res, 0, 14) ? + (char *) NULL : pg_strdup(PQgetvalue(res, 0, 14)); else tableinfo.relam = NULL; PQclear(res); @@ -2394,12 +2414,33 @@ 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 >= 120000 && + (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, + "SELECT conrelid = '%s'::pg_catalog.regclass, conname,\n" + " pg_catalog.pg_get_constraintdef(oid, true),\n" + " conrelid::pg_catalog.regclass\n" + " FROM pg_constraint, pg_partition_ancestors('%s')\n" + " WHERE conrelid = relid AND contype = 'f' AND conparentid = 0\n" + "ORDER BY 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; @@ -2411,10 +2452,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); } @@ -2422,15 +2473,34 @@ 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 >= 120000 && + (tableinfo.ispartition || + tableinfo.relkind == RELKIND_PARTITIONED_TABLE)) + { + printfPQExpBuffer(&buf, + "SELECT conname,\n" + " conrelid::pg_catalog.regclass,\n" + " pg_catalog.pg_get_constraintdef(oid, true)\n" + " FROM pg_constraint, pg_partition_ancestors('%s')\n" + " WHERE confrelid = relid AND contype = 'f' AND conparentid = 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' AND conparentid = 0\n" + "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 401514a3e00..eac5c0ab145 100644 --- a/src/test/regress/expected/foreign_key.out +++ b/src/test/regress/expected/foreign_key.out @@ -1916,7 +1916,7 @@ alter table fkpart0.fk_part add foreign key (a) references fkpart0.pkey; a | integer | | | Partition of: fkpart0.fk_part FOR VALUES IN (1) Foreign-key constraints: - "fk_part_1_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) + TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) alter table fkpart0.fk_part_1 drop constraint fk_part_1_a_fkey; ERROR: cannot drop inherited constraint "fk_part_1_a_fkey" of relation "fk_part_1" @@ -1928,7 +1928,7 @@ ERROR: cannot drop inherited constraint "fk_part_1_a_fkey" of relation "fk_part Partition of: fkpart0.fk_part FOR VALUES IN (2, 3) Partition key: LIST (a) Foreign-key constraints: - "fk_part_23_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) + TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) Number of partitions: 1 (Use \d+ to list them.) \d fkpart0.fk_part_23_2 \\ -- should have only one FK @@ -1938,7 +1938,7 @@ Number of partitions: 1 (Use \d+ to list them.) a | integer | | | Partition of: fkpart0.fk_part_23 FOR VALUES IN (2) Foreign-key constraints: - "fk_part_23_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) + TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) alter table fkpart0.fk_part_23 drop constraint fk_part_23_a_fkey; ERROR: cannot drop inherited constraint "fk_part_23_a_fkey" of relation "fk_part_23" @@ -1952,7 +1952,7 @@ create table fkpart0.fk_part_4 partition of fkpart0.fk_part for values in (4); a | integer | | | Partition of: fkpart0.fk_part FOR VALUES IN (4) Foreign-key constraints: - "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) + TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) alter table fkpart0.fk_part_4 drop constraint fk_part_a_fkey; ERROR: cannot drop inherited constraint "fk_part_a_fkey" of relation "fk_part_4" @@ -1968,7 +1968,7 @@ create table fkpart0.fk_part_56_5 partition of fkpart0.fk_part_56 Partition of: fkpart0.fk_part FOR VALUES IN (5, 6) Partition key: LIST (a) Foreign-key constraints: - "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) + TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a) Number of partitions: 1 (Use \d+ to list them.) alter table fkpart0.fk_part_56 drop constraint fk_part_a_fkey; -- 2.17.1 --YZ5djTAD1cGYuMQK-- ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: API stability @ 2022-04-08 01:34 Kyotaro Horiguchi <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Kyotaro Horiguchi @ 2022-04-08 01:34 UTC (permalink / raw) To: [email protected]; +Cc: [email protected]; [email protected]; [email protected]; pgsql-hackers (Mmm. My mailer automatically teared off the [was: ..] part from the subject..) -- Kyotaro Horiguchi NTT Open Source Software Center ^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH v12 1/7] Row pattern recognition patch for raw parser. @ 2023-12-04 11:23 Tatsuo Ishii <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Tatsuo Ishii @ 2023-12-04 11:23 UTC (permalink / raw) --- src/backend/parser/gram.y | 222 ++++++++++++++++++++++++++++++-- src/include/nodes/parsenodes.h | 56 ++++++++ src/include/parser/kwlist.h | 8 ++ src/include/parser/parse_node.h | 1 + 4 files changed, 273 insertions(+), 14 deletions(-) diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index d631ac89a9..5a77fca17f 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -251,6 +251,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); DefElem *defelt; SortBy *sortby; WindowDef *windef; + RPCommonSyntax *rpcom; + RPSubsetItem *rpsubset; JoinExpr *jexpr; IndexElem *ielem; StatsElem *selem; @@ -278,6 +280,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); MergeWhenClause *mergewhen; struct KeyActions *keyactions; struct KeyAction *keyaction; + RPSkipTo skipto; } %type <node> stmt toplevel_stmt schema_stmt routine_body_stmt @@ -453,8 +456,12 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); TriggerTransitions TriggerReferencing vacuum_relation_list opt_vacuum_relation_list drop_option_list pub_obj_list - -%type <node> opt_routine_body + row_pattern_measure_list row_pattern_definition_list + opt_row_pattern_subset_clause + row_pattern_subset_list row_pattern_subset_rhs + row_pattern +%type <rpsubset> row_pattern_subset_item +%type <node> opt_routine_body row_pattern_term %type <groupclause> group_clause %type <list> group_by_list %type <node> group_by_item empty_grouping_set rollup_clause cube_clause @@ -551,6 +558,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type <range> relation_expr_opt_alias %type <node> tablesample_clause opt_repeatable_clause %type <target> target_el set_target insert_column_item + row_pattern_measure_item row_pattern_definition +%type <skipto> first_or_last %type <str> generic_option_name %type <node> generic_option_arg @@ -633,6 +642,9 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type <list> window_clause window_definition_list opt_partition_clause %type <windef> window_definition over_clause window_specification opt_frame_clause frame_extent frame_bound +%type <rpcom> opt_row_pattern_common_syntax opt_row_pattern_skip_to +%type <boolean> opt_row_pattern_initial_or_seek +%type <list> opt_row_pattern_measures %type <ival> opt_window_exclusion_clause %type <str> opt_existing_window_name %type <boolean> opt_if_not_exists @@ -659,7 +671,6 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); json_object_constructor_null_clause_opt json_array_constructor_null_clause_opt - /* * Non-keyword token types. These are hard-wired into the "flex" lexer. * They must be listed first so that their numeric codes do not depend on @@ -702,7 +713,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE DATA_P DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS - DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DEPENDS DEPTH DESC + DEFERRABLE DEFERRED DEFINE DEFINER DELETE_P DELIMITER DELIMITERS DEPENDS DEPTH DESC DETACH DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P DOUBLE_P DROP @@ -718,7 +729,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); HANDLER HAVING HEADER_P HOLD HOUR_P IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P INCLUDE - INCLUDING INCREMENT INDENT INDEX INDEXES INHERIT INHERITS INITIALLY INLINE_P + INCLUDING INCREMENT INDENT INDEX INDEXES INHERIT INHERITS INITIAL INITIALLY INLINE_P INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION @@ -731,7 +742,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); LEADING LEAKPROOF LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P LOCKED LOGGED - MAPPING MATCH MATCHED MATERIALIZED MAXVALUE MERGE METHOD + MAPPING MATCH MATCHED MATERIALIZED MAXVALUE MEASURES MERGE METHOD MINUTE_P MINVALUE MODE MONTH_P MOVE NAME_P NAMES NATIONAL NATURAL NCHAR NEW NEXT NFC NFD NFKC NFKD NO NONE @@ -743,8 +754,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); ORDER ORDINALITY OTHERS OUT_P OUTER_P OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER - PARALLEL PARAMETER PARSER PARTIAL PARTITION PASSING PASSWORD - PLACING PLANS POLICY + PARALLEL PARAMETER PARSER PARTIAL PARTITION PASSING PASSWORD PAST + PATTERN_P PERMUTE PLACING PLANS POLICY POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION @@ -755,12 +766,13 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); RESET RESTART RESTRICT RETURN RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP ROUTINE ROUTINES ROW ROWS RULE - SAVEPOINT SCALAR SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT + SAVEPOINT SCALAR SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SEEK SELECT SEQUENCE SEQUENCES + SERIALIZABLE SERVER SESSION SESSION_USER SET SETS SETOF SHARE SHOW SIMILAR SIMPLE SKIP SMALLINT SNAPSHOT SOME SQL_P STABLE STANDALONE_P START STATEMENT STATISTICS STDIN STDOUT STORAGE STORED STRICT_P STRIP_P - SUBSCRIPTION SUBSTRING SUPPORT SYMMETRIC SYSID SYSTEM_P SYSTEM_USER + SUBSCRIPTION SUBSET SUBSTRING SUPPORT SYMMETRIC SYSID SYSTEM_P SYSTEM_USER TABLE TABLES TABLESAMPLE TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN TIES TIME TIMESTAMP TO TRAILING TRANSACTION TRANSFORM @@ -866,6 +878,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %nonassoc UNBOUNDED /* ideally would have same precedence as IDENT */ %nonassoc IDENT PARTITION RANGE ROWS GROUPS PRECEDING FOLLOWING CUBE ROLLUP SET KEYS OBJECT_P SCALAR VALUE_P WITH WITHOUT +%nonassoc MEASURES AFTER INITIAL SEEK PATTERN_P %left Op OPERATOR /* multi-character ops and user-defined operators */ %left '+' '-' %left '*' '/' '%' @@ -15914,7 +15927,8 @@ over_clause: OVER window_specification ; window_specification: '(' opt_existing_window_name opt_partition_clause - opt_sort_clause opt_frame_clause ')' + opt_sort_clause opt_row_pattern_measures opt_frame_clause + opt_row_pattern_common_syntax ')' { WindowDef *n = makeNode(WindowDef); @@ -15922,10 +15936,12 @@ window_specification: '(' opt_existing_window_name opt_partition_clause n->refname = $2; n->partitionClause = $3; n->orderClause = $4; + n->rowPatternMeasures = $5; /* copy relevant fields of opt_frame_clause */ - n->frameOptions = $5->frameOptions; - n->startOffset = $5->startOffset; - n->endOffset = $5->endOffset; + n->frameOptions = $6->frameOptions; + n->startOffset = $6->startOffset; + n->endOffset = $6->endOffset; + n->rpCommonSyntax = $7; n->location = @1; $$ = n; } @@ -15949,6 +15965,31 @@ opt_partition_clause: PARTITION BY expr_list { $$ = $3; } | /*EMPTY*/ { $$ = NIL; } ; +/* + * ROW PATTERN_P MEASURES + */ +opt_row_pattern_measures: MEASURES row_pattern_measure_list { $$ = $2; } + | /*EMPTY*/ { $$ = NIL; } + ; + +row_pattern_measure_list: + row_pattern_measure_item + { $$ = list_make1($1); } + | row_pattern_measure_list ',' row_pattern_measure_item + { $$ = lappend($1, $3); } + ; + +row_pattern_measure_item: + a_expr AS ColLabel + { + $$ = makeNode(ResTarget); + $$->name = $3; + $$->indirection = NIL; + $$->val = (Node *) $1; + $$->location = @1; + } + ; + /* * For frame clauses, we return a WindowDef, but only some fields are used: * frameOptions, startOffset, and endOffset. @@ -16108,6 +16149,143 @@ opt_window_exclusion_clause: | /*EMPTY*/ { $$ = 0; } ; +opt_row_pattern_common_syntax: +opt_row_pattern_skip_to opt_row_pattern_initial_or_seek + PATTERN_P '(' row_pattern ')' + opt_row_pattern_subset_clause + DEFINE row_pattern_definition_list + { + RPCommonSyntax *n = makeNode(RPCommonSyntax); + n->rpSkipTo = $1->rpSkipTo; + n->rpSkipVariable = $1->rpSkipVariable; + n->initial = $2; + n->rpPatterns = $5; + n->rpSubsetClause = $7; + n->rpDefs = $9; + $$ = n; + } + | /*EMPTY*/ { $$ = NULL; } + ; + +opt_row_pattern_skip_to: + AFTER MATCH SKIP TO NEXT ROW + { + RPCommonSyntax *n = makeNode(RPCommonSyntax); + n->rpSkipTo = ST_NEXT_ROW; + n->rpSkipVariable = NULL; + $$ = n; + } + | AFTER MATCH SKIP PAST LAST_P ROW + { + RPCommonSyntax *n = makeNode(RPCommonSyntax); + n->rpSkipTo = ST_PAST_LAST_ROW; + n->rpSkipVariable = NULL; + $$ = n; + } + | AFTER MATCH SKIP TO first_or_last ColId + { + RPCommonSyntax *n = makeNode(RPCommonSyntax); + n->rpSkipTo = $5; + n->rpSkipVariable = $6; + $$ = n; + } +/* + | AFTER MATCH SKIP TO LAST_P ColId %prec LAST_P + { + RPCommonSyntax *n = makeNode(RPCommonSyntax); + n->rpSkipTo = ST_LAST_VARIABLE; + n->rpSkipVariable = $6; + $$ = n; + } + | AFTER MATCH SKIP TO ColId + { + RPCommonSyntax *n = makeNode(RPCommonSyntax); + n->rpSkipTo = ST_VARIABLE; + n->rpSkipVariable = $5; + $$ = n; + } +*/ + | /*EMPTY*/ + { + RPCommonSyntax *n = makeNode(RPCommonSyntax); + /* temporary set default to ST_NEXT_ROW */ + n->rpSkipTo = ST_PAST_LAST_ROW; + n->rpSkipVariable = NULL; + $$ = n; + } + ; + +first_or_last: + FIRST_P { $$ = ST_FIRST_VARIABLE; } + | LAST_P { $$ = ST_LAST_VARIABLE; } + ; + +opt_row_pattern_initial_or_seek: + INITIAL { $$ = true; } + | SEEK + { + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("SEEK is not supported"), + errhint("Use INITIAL."), + parser_errposition(@1))); + } + | /*EMPTY*/ { $$ = true; } + ; + +row_pattern: + row_pattern_term { $$ = list_make1($1); } + | row_pattern row_pattern_term { $$ = lappend($1, $2); } + ; + +row_pattern_term: + ColId { $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "", (Node *)makeString($1), NULL, @1); } + | ColId '*' { $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "*", (Node *)makeString($1), NULL, @1); } + | ColId '+' { $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "+", (Node *)makeString($1), NULL, @1); } + | ColId '?' { $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "?", (Node *)makeString($1), NULL, @1); } + ; + +opt_row_pattern_subset_clause: + SUBSET row_pattern_subset_list { $$ = $2; } + | /*EMPTY*/ { $$ = NIL; } + ; + +row_pattern_subset_list: + row_pattern_subset_item { $$ = list_make1($1); } + | row_pattern_subset_list ',' row_pattern_subset_item { $$ = lappend($1, $3); } + | /*EMPTY*/ { $$ = NIL; } + ; + +row_pattern_subset_item: ColId '=' '(' row_pattern_subset_rhs ')' + { + RPSubsetItem *n = makeNode(RPSubsetItem); + n->name = $1; + n->rhsVariable = $4; + $$ = n; + } + ; + +row_pattern_subset_rhs: + ColId { $$ = list_make1(makeStringConst($1, @1)); } + | row_pattern_subset_rhs ',' ColId { $$ = lappend($1, makeStringConst($3, @1)); } + | /*EMPTY*/ { $$ = NIL; } + ; + +row_pattern_definition_list: + row_pattern_definition { $$ = list_make1($1); } + | row_pattern_definition_list ',' row_pattern_definition { $$ = lappend($1, $3); } + ; + +row_pattern_definition: + ColId AS a_expr + { + $$ = makeNode(ResTarget); + $$->name = $1; + $$->indirection = NIL; + $$->val = (Node *) $3; + $$->location = @1; + } + ; /* * Supporting nonterminals for expressions. @@ -17217,6 +17395,7 @@ unreserved_keyword: | INDEXES | INHERIT | INHERITS + | INITIAL | INLINE_P | INPUT_P | INSENSITIVE @@ -17244,6 +17423,7 @@ unreserved_keyword: | MATCHED | MATERIALIZED | MAXVALUE + | MEASURES | MERGE | METHOD | MINUTE_P @@ -17286,6 +17466,9 @@ unreserved_keyword: | PARTITION | PASSING | PASSWORD + | PAST + | PATTERN_P + | PERMUTE | PLANS | POLICY | PRECEDING @@ -17336,6 +17519,7 @@ unreserved_keyword: | SEARCH | SECOND_P | SECURITY + | SEEK | SEQUENCE | SEQUENCES | SERIALIZABLE @@ -17361,6 +17545,7 @@ unreserved_keyword: | STRICT_P | STRIP_P | SUBSCRIPTION + | SUBSET | SUPPORT | SYSID | SYSTEM_P @@ -17548,6 +17733,7 @@ reserved_keyword: | CURRENT_USER | DEFAULT | DEFERRABLE + | DEFINE | DESC | DISTINCT | DO @@ -17710,6 +17896,7 @@ bare_label_keyword: | DEFAULTS | DEFERRABLE | DEFERRED + | DEFINE | DEFINER | DELETE_P | DELIMITER @@ -17785,6 +17972,7 @@ bare_label_keyword: | INDEXES | INHERIT | INHERITS + | INITIAL | INITIALLY | INLINE_P | INNER_P @@ -17834,6 +18022,7 @@ bare_label_keyword: | MATCHED | MATERIALIZED | MAXVALUE + | MEASURES | MERGE | METHOD | MINVALUE @@ -17887,6 +18076,9 @@ bare_label_keyword: | PARTITION | PASSING | PASSWORD + | PAST + | PATTERN_P + | PERMUTE | PLACING | PLANS | POLICY @@ -17943,6 +18135,7 @@ bare_label_keyword: | SCROLL | SEARCH | SECURITY + | SEEK | SELECT | SEQUENCE | SEQUENCES @@ -17974,6 +18167,7 @@ bare_label_keyword: | STRICT_P | STRIP_P | SUBSCRIPTION + | SUBSET | SUBSTRING | SUPPORT | SYMMETRIC diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index e494309da8..094c603887 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -540,6 +540,44 @@ typedef struct SortBy int location; /* operator location, or -1 if none/unknown */ } SortBy; +/* + * AFTER MATCH row pattern skip to types in row pattern common syntax + */ +typedef enum RPSkipTo +{ + ST_NONE, /* AFTER MATCH omitted */ + ST_NEXT_ROW, /* SKIP TO NEXT ROW */ + ST_PAST_LAST_ROW, /* SKIP TO PAST LAST ROW */ + ST_FIRST_VARIABLE, /* SKIP TO FIRST variable name */ + ST_LAST_VARIABLE, /* SKIP TO LAST variable name */ + ST_VARIABLE /* SKIP TO variable name */ +} RPSkipTo; + +/* + * Row Pattern SUBSET clause item + */ +typedef struct RPSubsetItem +{ + NodeTag type; + char *name; /* Row Pattern SUBSET clause variable name */ + List *rhsVariable; /* Row Pattern SUBSET rhs variables (list of char *string) */ +} RPSubsetItem; + +/* + * RowPatternCommonSyntax - raw representation of row pattern common syntax + * + */ +typedef struct RPCommonSyntax +{ + NodeTag type; + RPSkipTo rpSkipTo; /* Row Pattern AFTER MATCH SKIP type */ + char *rpSkipVariable; /* Row Pattern Skip To variable name, if any */ + bool initial; /* true if <row pattern initial or seek> is initial */ + List *rpPatterns; /* PATTERN variables (list of A_Expr) */ + List *rpSubsetClause; /* row pattern subset clause (list of RPSubsetItem), if any */ + List *rpDefs; /* row pattern definitions clause (list of ResTarget) */ +} RPCommonSyntax; + /* * WindowDef - raw representation of WINDOW and OVER clauses * @@ -555,6 +593,8 @@ typedef struct WindowDef char *refname; /* referenced window name, if any */ List *partitionClause; /* PARTITION BY expression list */ List *orderClause; /* ORDER BY (list of SortBy) */ + List *rowPatternMeasures; /* row pattern measures (list of ResTarget) */ + RPCommonSyntax *rpCommonSyntax; /* row pattern common syntax */ int frameOptions; /* frame_clause options, see below */ Node *startOffset; /* expression for starting bound, if any */ Node *endOffset; /* expression for ending bound, if any */ @@ -1476,6 +1516,11 @@ typedef struct GroupingSet * the orderClause might or might not be copied (see copiedOrder); the framing * options are never copied, per spec. * + * "defineClause" is Row Pattern Recognition DEFINE clause (list of + * TargetEntry). TargetEntry.resname represents row pattern definition + * variable name. "patternVariable" and "patternRegexp" represents PATTERN + * clause. + * * The information relevant for the query jumbling is the partition clause * type and its bounds. */ @@ -1507,6 +1552,17 @@ typedef struct WindowClause Index winref; /* ID referenced by window functions */ /* did we copy orderClause from refname? */ bool copiedOrder pg_node_attr(query_jumble_ignore); + /* Row Pattern AFTER MACH SKIP clause */ + RPSkipTo rpSkipTo; /* Row Pattern Skip To type */ + bool initial; /* true if <row pattern initial or seek> is initial */ + /* Row Pattern DEFINE clause (list of TargetEntry) */ + List *defineClause; + /* Row Pattern DEFINE variable initial names (list of String) */ + List *defineInitial; + /* Row Pattern PATTERN variable name (list of String) */ + List *patternVariable; + /* Row Pattern PATTERN regular expression quantifier ('+' or ''. list of String) */ + List *patternRegexp; } WindowClause; /* diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 5984dcfa4b..2804333b53 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -128,6 +128,7 @@ PG_KEYWORD("default", DEFAULT, RESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("defaults", DEFAULTS, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("deferrable", DEFERRABLE, RESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("deferred", DEFERRED, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("define", DEFINE, RESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("definer", DEFINER, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("delete", DELETE_P, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("delimiter", DELIMITER, UNRESERVED_KEYWORD, BARE_LABEL) @@ -212,6 +213,7 @@ PG_KEYWORD("index", INDEX, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("indexes", INDEXES, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("inherit", INHERIT, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("inherits", INHERITS, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("initial", INITIAL, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("initially", INITIALLY, RESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("inline", INLINE_P, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("inner", INNER_P, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL) @@ -265,6 +267,7 @@ PG_KEYWORD("match", MATCH, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("matched", MATCHED, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("materialized", MATERIALIZED, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("maxvalue", MAXVALUE, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("measures", MEASURES, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("merge", MERGE, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("method", METHOD, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("minute", MINUTE_P, UNRESERVED_KEYWORD, AS_LABEL) @@ -326,6 +329,9 @@ PG_KEYWORD("partial", PARTIAL, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("partition", PARTITION, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("passing", PASSING, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("password", PASSWORD, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("past", PAST, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("pattern", PATTERN_P, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("permute", PERMUTE, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("placing", PLACING, RESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("plans", PLANS, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("policy", POLICY, UNRESERVED_KEYWORD, BARE_LABEL) @@ -385,6 +391,7 @@ PG_KEYWORD("scroll", SCROLL, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("search", SEARCH, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("second", SECOND_P, UNRESERVED_KEYWORD, AS_LABEL) PG_KEYWORD("security", SECURITY, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("seek", SEEK, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("select", SELECT, RESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("sequence", SEQUENCE, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("sequences", SEQUENCES, UNRESERVED_KEYWORD, BARE_LABEL) @@ -416,6 +423,7 @@ PG_KEYWORD("stored", STORED, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("strict", STRICT_P, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("strip", STRIP_P, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("subscription", SUBSCRIPTION, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("subset", SUBSET, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("substring", SUBSTRING, COL_NAME_KEYWORD, BARE_LABEL) PG_KEYWORD("support", SUPPORT, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("symmetric", SYMMETRIC, RESERVED_KEYWORD, BARE_LABEL) diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h index f589112d5e..6640090910 100644 --- a/src/include/parser/parse_node.h +++ b/src/include/parser/parse_node.h @@ -51,6 +51,7 @@ typedef enum ParseExprKind EXPR_KIND_WINDOW_FRAME_RANGE, /* window frame clause with RANGE */ EXPR_KIND_WINDOW_FRAME_ROWS, /* window frame clause with ROWS */ EXPR_KIND_WINDOW_FRAME_GROUPS, /* window frame clause with GROUPS */ + EXPR_KIND_RPR_DEFINE, /* DEFINE */ EXPR_KIND_SELECT_TARGET, /* SELECT target list item */ EXPR_KIND_INSERT_TARGET, /* INSERT target list item */ EXPR_KIND_UPDATE_SOURCE, /* UPDATE assignment source item */ -- 2.25.1 ----Next_Part(Fri_Dec__8_10_16_13_2023_489)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v12-0002-Row-pattern-recognition-patch-parse-analysis.patch" ^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-12-04 11:23 UTC | newest] Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2019-02-26 21:57 [PATCH v3 2/2] fix psql display of FKs Alvaro Herrera <[email protected]> 2019-02-26 21:57 [PATCH v5] fix psql display of FKs Alvaro Herrera <[email protected]> 2019-03-22 22:13 [PATCH v8] fix psql display of FKs Alvaro Herrera <[email protected]> 2019-03-22 22:13 [PATCH v9] fix psql display of FKs Alvaro Herrera <[email protected]> 2019-03-22 22:13 [PATCH v10] fix psql display of FKs Alvaro Herrera <[email protected]> 2019-03-22 22:13 [PATCH v6] fix psql display of FKs Alvaro Herrera <[email protected]> 2022-04-08 01:34 Re: API stability Kyotaro Horiguchi <[email protected]> 2023-12-04 11:23 [PATCH v12 1/7] Row pattern recognition patch for raw parser. Tatsuo Ishii <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox