v9 attached; this one's final AFAICT.
On 2019-Mar-25, Peter Eisentraut wrote:
> relispartition was added in PG10, so the conditional in
> describeOneTableDetails() seems wrong.
Hmm, yeah, we weren't using it anyway (since we can only use the new
display with pg_partition_ancestors which is new in pg12), but I guess
you have a point that this could confuse somebody in the future.
> In the older branches of that same function, I'd prefer writing
>
> false AS relispartition
>
> for clarity.
Yeah, some previous commits in that area have added "false" flags here
and there without adding aliases. We should fix those sometime. And
also the new "amname" output column is conditional on the version number
and changes column numbering for any column that appears afterwards ...
that one definitely deserves a "NULL as amname" in the older branches.
I changed some code to use PQfnumber() the way pg_dump does it; that
code's support for back-branch compatibility is much more battle-tested
than psql's and I trust that to be more maintainable. In fact, my
motivation for doing it that way is that I found psql's way to be
confusing.
> Some of the other queries could also use some column aliases, like
>
> conrelid = '%s'::pg_catalog.regclass AS isroot (?)
>
> or
>
> pg_catalog.pg_get_constraintdef(oid, true) AS condef
>
> (as in the other branch).
Agreed, added.
> A test case for the incoming foreign key display would be nice, as that
> was the original argument for the patch.
Agreed, added.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From 58a8890daa0599be0dcb27c018ee31bafa2ee359 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvhe...@alvh.no-ip.org>
Date: Fri, 22 Mar 2019 19:13:16 -0300
Subject: [PATCH v9] fix psql display of FKs
---
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
