Skip to content

Commit 1af25ca

Browse files
alvherrealvherre
committed
Improve psql's \d display of foreign key constraints
When used on a partition containing foreign keys coming from one of its ancestors, \d would (rather unhelpfully) print the details about the pg_constraint row in the partition. This becomes a bit frustrating when the user tries things like dropping the FK in the partition; instead, show the details for the foreign key on the table where it is defined. Also, when a table is referenced by a foreign key on a partitioned table, we would show multiple "Referenced by" lines, one for each partition, which gets unwieldy pretty fast. Modify that so that it shows only one line for the ancestor partitioned table where the FK is defined. Discussion: https://postgr.es/m/20181204143834.ym6euxxxi5aeqdpn@alvherre.pgsql Reviewed-by: Tom Lane, Amit Langote, Peter Eisentraut
1 parent 05295e3 commit 1af25ca

File tree

3 files changed

+145
-51
lines changed

3 files changed

+145
-51
lines changed

‎src/bin/psql/describe.c

Lines changed: 118 additions & 38 deletions
Original file line numberDiff line numberDiff line change
@@ -1479,6 +1479,7 @@ describeOneTableDetails(const char *schemaname,
14791479
bool rowsecurity;
14801480
bool forcerowsecurity;
14811481
bool hasoids;
1482+
bool ispartition;
14821483
Oid tablespace;
14831484
char *reloptions;
14841485
char *reloftype;
@@ -1502,7 +1503,7 @@ describeOneTableDetails(const char *schemaname,
15021503
printfPQExpBuffer(&buf,
15031504
"SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
15041505
"c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, "
1505-
"false AS relhasoids, %s, c.reltablespace, "
1506+
"false AS relhasoids, c.relispartition, %s, c.reltablespace, "
15061507
"CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
15071508
"c.relpersistence, c.relreplident, am.amname\n"
15081509
"FROM pg_catalog.pg_class c\n "
@@ -1515,12 +1516,29 @@ describeOneTableDetails(const char *schemaname,
15151516
: "''"),
15161517
oid);
15171518
}
1519+
else if (pset.sversion >= 100000)
1520+
{
1521+
printfPQExpBuffer(&buf,
1522+
"SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1523+
"c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, "
1524+
"c.relhasoids, c.relispartition, %s, c.reltablespace, "
1525+
"CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
1526+
"c.relpersistence, c.relreplident\n"
1527+
"FROM pg_catalog.pg_class c\n "
1528+
"LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1529+
"WHERE c.oid = '%s';",
1530+
(verbose ?
1531+
"pg_catalog.array_to_string(c.reloptions || "
1532+
"array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1533+
: "''"),
1534+
oid);
1535+
}
15181536
else if (pset.sversion >= 90500)
15191537
{
15201538
printfPQExpBuffer(&buf,
15211539
"SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
15221540
"c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, "
1523-
"c.relhasoids, %s, c.reltablespace, "
1541+
"c.relhasoids, false as relispartition, %s, c.reltablespace, "
15241542
"CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
15251543
"c.relpersistence, c.relreplident\n"
15261544
"FROM pg_catalog.pg_class c\n "
@@ -1537,7 +1555,7 @@ describeOneTableDetails(const char *schemaname,
15371555
printfPQExpBuffer(&buf,
15381556
"SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
15391557
"c.relhastriggers, false, false, c.relhasoids, "
1540-
"%s, c.reltablespace, "
1558+
"false as relispartition, %s, c.reltablespace, "
15411559
"CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
15421560
"c.relpersistence, c.relreplident\n"
15431561
"FROM pg_catalog.pg_class c\n "
@@ -1554,7 +1572,7 @@ describeOneTableDetails(const char *schemaname,
15541572
printfPQExpBuffer(&buf,
15551573
"SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
15561574
"c.relhastriggers, false, false, c.relhasoids, "
1557-
"%s, c.reltablespace, "
1575+
"false as relispartition, %s, c.reltablespace, "
15581576
"CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
15591577
"c.relpersistence\n"
15601578
"FROM pg_catalog.pg_class c\n "
@@ -1571,7 +1589,7 @@ describeOneTableDetails(const char *schemaname,
15711589
printfPQExpBuffer(&buf,
15721590
"SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
15731591
"c.relhastriggers, false, false, c.relhasoids, "
1574-
"%s, c.reltablespace, "
1592+
"false as relispartition, %s, c.reltablespace, "
15751593
"CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END\n"
15761594
"FROM pg_catalog.pg_class c\n "
15771595
"LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
@@ -1587,7 +1605,7 @@ describeOneTableDetails(const char *schemaname,
15871605
printfPQExpBuffer(&buf,
15881606
"SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
15891607
"c.relhastriggers, false, false, c.relhasoids, "
1590-
"%s, c.reltablespace\n"
1608+
"false as relispartition, %s, c.reltablespace\n"
15911609
"FROM pg_catalog.pg_class c\n "
15921610
"LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
15931611
"WHERE c.oid = '%s';",
@@ -1602,7 +1620,7 @@ describeOneTableDetails(const char *schemaname,
16021620
printfPQExpBuffer(&buf,
16031621
"SELECT relchecks, relkind, relhasindex, relhasrules, "
16041622
"reltriggers <> 0, false, false, relhasoids, "
1605-
"%s, reltablespace\n"
1623+
"false as relispartition, %s, reltablespace\n"
16061624
"FROM pg_catalog.pg_class WHERE oid = '%s';",
16071625
(verbose ?
16081626
"pg_catalog.array_to_string(reloptions, E', ')" : "''"),
@@ -1613,7 +1631,7 @@ describeOneTableDetails(const char *schemaname,
16131631
printfPQExpBuffer(&buf,
16141632
"SELECT relchecks, relkind, relhasindex, relhasrules, "
16151633
"reltriggers <> 0, false, false, relhasoids, "
1616-
"'', reltablespace\n"
1634+
"false as relispartition, '', reltablespace\n"
16171635
"FROM pg_catalog.pg_class WHERE oid = '%s';",
16181636
oid);
16191637
}
@@ -1622,7 +1640,7 @@ describeOneTableDetails(const char *schemaname,
16221640
printfPQExpBuffer(&buf,
16231641
"SELECT relchecks, relkind, relhasindex, relhasrules, "
16241642
"reltriggers <> 0, false, false, relhasoids, "
1625-
"'', ''\n"
1643+
"false as relispartition, '', ''\n"
16261644
"FROM pg_catalog.pg_class WHERE oid = '%s';",
16271645
oid);
16281646
}
@@ -1647,20 +1665,21 @@ describeOneTableDetails(const char *schemaname,
16471665
tableinfo.rowsecurity = strcmp(PQgetvalue(res, 0, 5), "t") == 0;
16481666
tableinfo.forcerowsecurity = strcmp(PQgetvalue(res, 0, 6), "t") == 0;
16491667
tableinfo.hasoids = strcmp(PQgetvalue(res, 0, 7), "t") == 0;
1668+
tableinfo.ispartition = strcmp(PQgetvalue(res, 0, 8), "t") == 0;
16501669
tableinfo.reloptions = (pset.sversion >= 80200) ?
1651-
pg_strdup(PQgetvalue(res, 0, 8)) : NULL;
1670+
pg_strdup(PQgetvalue(res, 0, 9)) : NULL;
16521671
tableinfo.tablespace = (pset.sversion >= 80000) ?
1653-
atooid(PQgetvalue(res, 0, 9)) : 0;
1672+
atooid(PQgetvalue(res, 0, 10)) : 0;
16541673
tableinfo.reloftype = (pset.sversion >= 90000 &&
1655-
strcmp(PQgetvalue(res, 0, 10), "") != 0) ?
1656-
pg_strdup(PQgetvalue(res, 0, 10)) : NULL;
1674+
strcmp(PQgetvalue(res, 0, 11), "") != 0) ?
1675+
pg_strdup(PQgetvalue(res, 0, 11)) : NULL;
16571676
tableinfo.relpersistence = (pset.sversion >= 90100) ?
1658-
*(PQgetvalue(res, 0, 11)) : 0;
1677+
*(PQgetvalue(res, 0, 12)) : 0;
16591678
tableinfo.relreplident = (pset.sversion >= 90400) ?
1660-
*(PQgetvalue(res, 0, 12)) : 'd';
1679+
*(PQgetvalue(res, 0, 13)) : 'd';
16611680
if (pset.sversion >= 120000)
1662-
tableinfo.relam = PQgetisnull(res, 0, 13) ?
1663-
(char *) NULL : pg_strdup(PQgetvalue(res, 0, 13));
1681+
tableinfo.relam = PQgetisnull(res, 0, 14) ?
1682+
(char *) NULL : pg_strdup(PQgetvalue(res, 0, 14));
16641683
else
16651684
tableinfo.relam = NULL;
16661685
PQclear(res);
@@ -2394,12 +2413,36 @@ describeOneTableDetails(const char *schemaname,
23942413
if (tableinfo.hastriggers ||
23952414
tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
23962415
{
2397-
printfPQExpBuffer(&buf,
2398-
"SELECT conname,\n"
2399-
" pg_catalog.pg_get_constraintdef(r.oid, true) as condef\n"
2400-
"FROM pg_catalog.pg_constraint r\n"
2401-
"WHERE r.conrelid = '%s' AND r.contype = 'f' ORDER BY 1;",
2402-
oid);
2416+
if (pset.sversion >= 120000 &&
2417+
(tableinfo.ispartition || tableinfo.relkind == RELKIND_PARTITIONED_TABLE))
2418+
{
2419+
/*
2420+
* Put the constraints defined in this table first, followed
2421+
* by the constraints defined in ancestor partitioned tables.
2422+
*/
2423+
printfPQExpBuffer(&buf,
2424+
"SELECT conrelid = '%s'::pg_catalog.regclass AS sametable,\n"
2425+
" conname,\n"
2426+
" pg_catalog.pg_get_constraintdef(oid, true) AS condef,\n"
2427+
" conrelid::pg_catalog.regclass AS ontable\n"
2428+
" FROM pg_catalog.pg_constraint,\n"
2429+
" pg_catalog.pg_partition_ancestors('%s')\n"
2430+
" WHERE conrelid = relid AND contype = 'f' AND conparentid = 0\n"
2431+
"ORDER BY sametable DESC, conname;",
2432+
oid, oid);
2433+
}
2434+
else
2435+
{
2436+
printfPQExpBuffer(&buf,
2437+
"SELECT true as sametable, conname,\n"
2438+
" pg_catalog.pg_get_constraintdef(r.oid, true) as condef,\n"
2439+
" conrelid::pg_catalog.regclass AS ontable\n"
2440+
"FROM pg_catalog.pg_constraint r\n"
2441+
"WHERE r.conrelid = '%s' AND r.contype = 'f'\n"
2442+
"ORDER BY conname;",
2443+
oid);
2444+
}
2445+
24032446
result = PSQLexec(buf.data);
24042447
if (!result)
24052448
goto error_return;
@@ -2408,29 +2451,62 @@ describeOneTableDetails(const char *schemaname,
24082451

24092452
if (tuples > 0)
24102453
{
2454+
int i_sametable = PQfnumber(result, "sametable"),
2455+
i_conname = PQfnumber(result, "conname"),
2456+
i_condef = PQfnumber(result, "condef"),
2457+
i_ontable = PQfnumber(result, "ontable");
2458+
24112459
printTableAddFooter(&cont, _("Foreign-key constraints:"));
24122460
for (i = 0; i < tuples; i++)
24132461
{
2414-
/* untranslated constraint name and def */
2415-
printfPQExpBuffer(&buf, " \"%s\" %s",
2416-
PQgetvalue(result, i, 0),
2417-
PQgetvalue(result, i, 1));
2462+
/*
2463+
* Print untranslated constraint name and definition. Use
2464+
* a "TABLE tab" prefix when the constraint is defined in
2465+
* a parent partitioned table.
2466+
*/
2467+
if (strcmp(PQgetvalue(result, i, i_sametable), "f") == 0)
2468+
printfPQExpBuffer(&buf, " TABLE \"%s\" CONSTRAINT \"%s\" %s",
2469+
PQgetvalue(result, i, i_ontable),
2470+
PQgetvalue(result, i, i_conname),
2471+
PQgetvalue(result, i, i_condef));
2472+
else
2473+
printfPQExpBuffer(&buf, " \"%s\" %s",
2474+
PQgetvalue(result, i, i_conname),
2475+
PQgetvalue(result, i, i_condef));
24182476

24192477
printTableAddFooter(&cont, buf.data);
24202478
}
24212479
}
24222480
PQclear(result);
24232481
}
24242482

2425-
/* print incoming foreign-key references (none if no triggers) */
2426-
if (tableinfo.hastriggers)
2483+
/* print incoming foreign-key references */
2484+
if (tableinfo.hastriggers ||
2485+
tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
24272486
{
2428-
printfPQExpBuffer(&buf,
2429-
"SELECT conname, conrelid::pg_catalog.regclass,\n"
2430-
" pg_catalog.pg_get_constraintdef(c.oid, true) as condef\n"
2431-
"FROM pg_catalog.pg_constraint c\n"
2432-
"WHERE c.confrelid = '%s' AND c.contype = 'f' ORDER BY 1;",
2433-
oid);
2487+
if (pset.sversion >= 120000)
2488+
{
2489+
printfPQExpBuffer(&buf,
2490+
"SELECT conname, conrelid::pg_catalog.regclass AS ontable,\n"
2491+
" pg_catalog.pg_get_constraintdef(oid, true) AS condef\n"
2492+
" FROM pg_catalog.pg_constraint c\n"
2493+
" WHERE confrelid IN (SELECT pg_catalog.pg_partition_ancestors('%s')\n"
2494+
" UNION ALL VALUES ('%s'::pg_catalog.regclass))\n"
2495+
" AND contype = 'f' AND conparentid = 0\n"
2496+
"ORDER BY conname;",
2497+
oid, oid);
2498+
}
2499+
else
2500+
{
2501+
printfPQExpBuffer(&buf,
2502+
"SELECT conname, conrelid::pg_catalog.regclass AS ontable,\n"
2503+
" pg_catalog.pg_get_constraintdef(oid, true) AS condef\n"
2504+
" FROM pg_catalog.pg_constraint\n"
2505+
" WHERE confrelid = %s AND contype = 'f'\n"
2506+
"ORDER BY conname;",
2507+
oid);
2508+
}
2509+
24342510
result = PSQLexec(buf.data);
24352511
if (!result)
24362512
goto error_return;
@@ -2439,13 +2515,17 @@ describeOneTableDetails(const char *schemaname,
24392515

24402516
if (tuples > 0)
24412517
{
2518+
int i_conname = PQfnumber(result, "conname"),
2519+
i_ontable = PQfnumber(result, "ontable"),
2520+
i_condef = PQfnumber(result, "condef");
2521+
24422522
printTableAddFooter(&cont, _("Referenced by:"));
24432523
for (i = 0; i < tuples; i++)
24442524
{
24452525
printfPQExpBuffer(&buf, " TABLE \"%s\" CONSTRAINT \"%s\" %s",
2446-
PQgetvalue(result, i, 1),
2447-
PQgetvalue(result, i, 0),
2448-
PQgetvalue(result, i, 2));
2526+
PQgetvalue(result, i, i_ontable),
2527+
PQgetvalue(result, i, i_conname),
2528+
PQgetvalue(result, i, i_condef));
24492529

24502530
printTableAddFooter(&cont, buf.data);
24512531
}

0 commit comments

Comments
 (0)