Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit1af25ca

Browse files
committed
Improve psql's \d display of foreign key constraints
When used on a partition containing foreign keys coming from one of itsancestors, \d would (rather unhelpfully) print the details about thepg_constraint row in the partition. This becomes a bit frustrating whenthe 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 partitionedtable, we would show multiple "Referenced by" lines, one for eachpartition, which gets unwieldy pretty fast. Modify that so that itshows only one line for the ancestor partitioned table where the FK isdefined.Discussion:https://postgr.es/m/20181204143834.ym6euxxxi5aeqdpn@alvherre.pgsqlReviewed-by: Tom Lane, Amit Langote, Peter Eisentraut
1 parent05295e3 commit1af25ca

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
boolrowsecurity;
14801480
boolforcerowsecurity;
14811481
boolhasoids;
1482+
boolispartition;
14821483
Oidtablespace;
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+
elseif (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
elseif (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
gotoerror_return;
@@ -2408,29 +2451,62 @@ describeOneTableDetails(const char *schemaname,
24082451

24092452
if (tuples>0)
24102453
{
2454+
inti_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
gotoerror_return;
@@ -2439,13 +2515,17 @@ describeOneTableDetails(const char *schemaname,
24392515

24402516
if (tuples>0)
24412517
{
2518+
inti_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)
[8]ページ先頭
©2009-2025 Movatter.jp