public inbox for [email protected]
help / color / mirror / Atom feedFrom: Greg Sabino Mullane <[email protected]>
To: pgsql-hackers <[email protected]>
Subject: Adding comments to help understand psql hidden queries
Date: Mon, 11 Dec 2023 16:53:01 -0500
Message-ID: <CAKAnmmJz8Hh=8Ru8jgzySPWmLBhnv4=oc_0KRiz-UORJ0Dex+w@mail.gmail.com> (raw)
The use of the --echo-hidden flag in psql is used to show people the way
psql performs its magic for its backslash commands. None of them has more
magic than "\d relation", but it suffers from needing a lot of separate
queries to gather all of the information it needs. Unfortunately, those
queries can get overwhelming and hard to figure out which one does what,
especially for those not already very familiar with the system catalogs.
Attached is a patch to add a small SQL comment to the top of each SELECT
query inside describeOneTableDetail. All other functions use a single
query, and thus need no additional context. But "\d mytable" has the
potential to run over a dozen SQL queries! The new format looks like this:
/******** QUERY *********/
/* Get information about row-level policies */
SELECT pol.polname, pol.polpermissive,
CASE WHEN pol.polroles = '{0}' THEN NULL ELSE
pg_catalog.array_to_string(array(select rolname from pg_catalog.pg_roles
where oid = any (pol.polroles) order by 1),',') END,
pg_catalog.pg_get_expr(pol.polqual, pol.polrelid),
pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid),
CASE pol.polcmd
WHEN 'r' THEN 'SELECT'
WHEN 'a' THEN 'INSERT'
WHEN 'w' THEN 'UPDATE'
WHEN 'd' THEN 'DELETE'
END AS cmd
FROM pg_catalog.pg_policy pol
WHERE pol.polrelid = '134384' ORDER BY 1;
/************************/
Cheers,
Greg
Attachments:
[application/octet-stream] psql.echo.hidden.comments.v1.patch (12.9K, 3-psql.echo.hidden.comments.v1.patch)
download | inline diff:
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 5077e7b358..323f8d516c 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1582,9 +1582,10 @@ describeOneTableDetails(const char *schemaname,
initPQExpBuffer(&tmpbuf);
/* Get general table info */
+ printfPQExpBuffer(&buf, _("/* Get general table information */\n"));
if (pset.sversion >= 120000)
{
- printfPQExpBuffer(&buf,
+ appendPQExpBuffer(&buf,
"SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
"c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, "
"false AS relhasoids, c.relispartition, %s, c.reltablespace, "
@@ -1602,7 +1603,7 @@ describeOneTableDetails(const char *schemaname,
}
else if (pset.sversion >= 100000)
{
- printfPQExpBuffer(&buf,
+ appendPQExpBuffer(&buf,
"SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
"c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, "
"c.relhasoids, c.relispartition, %s, c.reltablespace, "
@@ -1619,7 +1620,7 @@ describeOneTableDetails(const char *schemaname,
}
else if (pset.sversion >= 90500)
{
- printfPQExpBuffer(&buf,
+ appendPQExpBuffer(&buf,
"SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
"c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, "
"c.relhasoids, false as relispartition, %s, c.reltablespace, "
@@ -1636,7 +1637,7 @@ describeOneTableDetails(const char *schemaname,
}
else if (pset.sversion >= 90400)
{
- printfPQExpBuffer(&buf,
+ appendPQExpBuffer(&buf,
"SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
"c.relhastriggers, false, false, c.relhasoids, "
"false as relispartition, %s, c.reltablespace, "
@@ -1653,7 +1654,7 @@ describeOneTableDetails(const char *schemaname,
}
else
{
- printfPQExpBuffer(&buf,
+ appendPQExpBuffer(&buf,
"SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
"c.relhastriggers, false, false, c.relhasoids, "
"false as relispartition, %s, c.reltablespace, "
@@ -1714,9 +1715,10 @@ describeOneTableDetails(const char *schemaname,
printQueryOpt myopt = pset.popt;
char *footers[2] = {NULL, NULL};
+ printfPQExpBuffer(&buf, _("/* Get general sequence information */\n"));
if (pset.sversion >= 100000)
{
- printfPQExpBuffer(&buf,
+ appendPQExpBuffer(&buf,
"SELECT pg_catalog.format_type(seqtypid, NULL) AS \"%s\",\n"
" seqstart AS \"%s\",\n"
" seqmin AS \"%s\",\n"
@@ -1740,7 +1742,7 @@ describeOneTableDetails(const char *schemaname,
}
else
{
- printfPQExpBuffer(&buf,
+ appendPQExpBuffer(&buf,
"SELECT 'bigint' AS \"%s\",\n"
" start_value AS \"%s\",\n"
" min_value AS \"%s\",\n"
@@ -1767,7 +1769,8 @@ describeOneTableDetails(const char *schemaname,
goto error_return;
/* Get the column that owns this sequence */
- printfPQExpBuffer(&buf, "SELECT pg_catalog.quote_ident(nspname) || '.' ||"
+ printfPQExpBuffer(&buf, _("/* Get the column that owns this sequence */\n"));
+ appendPQExpBuffer(&buf, "SELECT pg_catalog.quote_ident(nspname) || '.' ||"
"\n pg_catalog.quote_ident(relname) || '.' ||"
"\n pg_catalog.quote_ident(attname),"
"\n d.deptype"
@@ -1846,7 +1849,8 @@ describeOneTableDetails(const char *schemaname,
* duplicative test logic below.
*/
cols = 0;
- printfPQExpBuffer(&buf, "SELECT a.attname");
+ printfPQExpBuffer(&buf, _("/* Get information about each column */\n"));
+ appendPQExpBuffer(&buf, "SELECT a.attname");
attname_col = cols++;
appendPQExpBufferStr(&buf, ",\n pg_catalog.format_type(a.atttypid, a.atttypmod)");
atttype_col = cols++;
@@ -2146,7 +2150,8 @@ describeOneTableDetails(const char *schemaname,
/* Footer information for a partition child table */
PGresult *result;
- printfPQExpBuffer(&buf,
+ printfPQExpBuffer(&buf, _("/* Get partition information for this table */\n"));
+ appendPQExpBuffer(&buf,
"SELECT inhparent::pg_catalog.regclass,\n"
" pg_catalog.pg_get_expr(c.relpartbound, c.oid),\n ");
@@ -2201,7 +2206,8 @@ describeOneTableDetails(const char *schemaname,
/* Footer information for a partitioned table (partitioning parent) */
PGresult *result;
- printfPQExpBuffer(&buf,
+ printfPQExpBuffer(&buf, _("/* Get the partition key for this table */\n"));
+ appendPQExpBuffer(&buf,
"SELECT pg_catalog.pg_get_partkeydef('%s'::pg_catalog.oid);",
oid);
result = PSQLexec(buf.data);
@@ -2223,7 +2229,8 @@ describeOneTableDetails(const char *schemaname,
/* For a TOAST table, print name of owning table */
PGresult *result;
- printfPQExpBuffer(&buf,
+ printfPQExpBuffer(&buf, _("/* Find which table owns this TOAST table */\n"));
+ appendPQExpBuffer(&buf,
"SELECT n.nspname, c.relname\n"
"FROM pg_catalog.pg_class c"
" JOIN pg_catalog.pg_namespace n"
@@ -2251,7 +2258,8 @@ describeOneTableDetails(const char *schemaname,
/* Footer information about an index */
PGresult *result;
- printfPQExpBuffer(&buf,
+ printfPQExpBuffer(&buf, _("/* Get information about this index */\n"));
+ appendPQExpBuffer(&buf,
"SELECT i.indisunique, i.indisprimary, i.indisclustered, "
"i.indisvalid,\n"
" (NOT i.indimmediate) AND "
@@ -2368,7 +2376,8 @@ describeOneTableDetails(const char *schemaname,
/* print indexes */
if (tableinfo.hasindex)
{
- printfPQExpBuffer(&buf,
+ printfPQExpBuffer(&buf, _("/* Get information about each index */\n"));
+ appendPQExpBuffer(&buf,
"SELECT c2.relname, i.indisprimary, i.indisunique, "
"i.indisclustered, i.indisvalid, "
"pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),\n "
@@ -2498,6 +2507,8 @@ describeOneTableDetails(const char *schemaname,
if (tableinfo.hastriggers ||
tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
{
+ printfPQExpBuffer(&buf, _("/* Get information about foreign key constraints */\n"));
+
if (pset.sversion >= 120000 &&
(tableinfo.ispartition || tableinfo.relkind == RELKIND_PARTITIONED_TABLE))
{
@@ -2505,7 +2516,7 @@ describeOneTableDetails(const char *schemaname,
* Put the constraints defined in this table first, followed
* by the constraints defined in ancestor partitioned tables.
*/
- printfPQExpBuffer(&buf,
+ appendPQExpBuffer(&buf,
"SELECT conrelid = '%s'::pg_catalog.regclass AS sametable,\n"
" conname,\n"
" pg_catalog.pg_get_constraintdef(oid, true) AS condef,\n"
@@ -2518,7 +2529,7 @@ describeOneTableDetails(const char *schemaname,
}
else
{
- printfPQExpBuffer(&buf,
+ appendPQExpBuffer(&buf,
"SELECT true as sametable, conname,\n"
" pg_catalog.pg_get_constraintdef(r.oid, true) as condef,\n"
" conrelid::pg_catalog.regclass AS ontable\n"
@@ -2572,9 +2583,10 @@ describeOneTableDetails(const char *schemaname,
if (tableinfo.hastriggers ||
tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
{
+ printfPQExpBuffer(&buf, _("/* Get information about incoming foreign key references */\n"));
if (pset.sversion >= 120000)
{
- printfPQExpBuffer(&buf,
+ appendPQExpBuffer(&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"
@@ -2586,7 +2598,7 @@ describeOneTableDetails(const char *schemaname,
}
else
{
- printfPQExpBuffer(&buf,
+ appendPQExpBuffer(&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"
@@ -2624,7 +2636,8 @@ describeOneTableDetails(const char *schemaname,
/* print any row-level policies */
if (pset.sversion >= 90500)
{
- printfPQExpBuffer(&buf, "SELECT pol.polname,");
+ printfPQExpBuffer(&buf, _("/* Get information about row-level policies */\n"));
+ appendPQExpBuffer(&buf, "SELECT pol.polname,");
if (pset.sversion >= 100000)
appendPQExpBufferStr(&buf,
" pol.polpermissive,\n");
@@ -2704,9 +2717,10 @@ describeOneTableDetails(const char *schemaname,
}
/* print any extended statistics */
+ printfPQExpBuffer(&buf, _("/* Get information about extended statistics */\n"));
if (pset.sversion >= 140000)
{
- printfPQExpBuffer(&buf,
+ appendPQExpBuffer(&buf,
"SELECT oid, "
"stxrelid::pg_catalog.regclass, "
"stxnamespace::pg_catalog.regnamespace::pg_catalog.text AS nsp, "
@@ -2803,7 +2817,7 @@ describeOneTableDetails(const char *schemaname,
}
else if (pset.sversion >= 100000)
{
- printfPQExpBuffer(&buf,
+ appendPQExpBuffer(&buf,
"SELECT oid, "
"stxrelid::pg_catalog.regclass, "
"stxnamespace::pg_catalog.regnamespace AS nsp, "
@@ -2882,7 +2896,8 @@ describeOneTableDetails(const char *schemaname,
/* print rules */
if (tableinfo.hasrules && tableinfo.relkind != RELKIND_MATVIEW)
{
- printfPQExpBuffer(&buf,
+ printfPQExpBuffer(&buf, _("/* Get information about each rule for this table */\n"));
+ appendPQExpBuffer(&buf,
"SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true)), "
"ev_enabled\n"
"FROM pg_catalog.pg_rewrite r\n"
@@ -2965,9 +2980,10 @@ describeOneTableDetails(const char *schemaname,
/* print any publications */
if (pset.sversion >= 100000)
{
+ printfPQExpBuffer(&buf, _("/* Get information about each publication using this table */\n"));
if (pset.sversion >= 150000)
{
- printfPQExpBuffer(&buf,
+ appendPQExpBuffer(&buf,
"SELECT pubname\n"
" , NULL\n"
" , NULL\n"
@@ -2999,7 +3015,7 @@ describeOneTableDetails(const char *schemaname,
}
else
{
- printfPQExpBuffer(&buf,
+ appendPQExpBuffer(&buf,
"SELECT pubname\n"
" , NULL\n"
" , NULL\n"
@@ -3049,7 +3065,9 @@ describeOneTableDetails(const char *schemaname,
/* If verbose, print NOT NULL constraints */
if (verbose)
{
- printfPQExpBuffer(&buf,
+ printfPQExpBuffer(&buf, _("/* Get information about NOT NULL constraints */\n"));
+ appendPQExpBuffer(&buf,
+ "/* Find NOT NULL constraints */\n"
"SELECT co.conname, at.attname, co.connoinherit, co.conislocal,\n"
"co.coninhcount <> 0\n"
"FROM pg_catalog.pg_constraint co JOIN\n"
@@ -3121,7 +3139,8 @@ describeOneTableDetails(const char *schemaname,
/* print rules */
if (tableinfo.hasrules)
{
- printfPQExpBuffer(&buf,
+ printfPQExpBuffer(&buf, _("/* Get information about each rule for this view */\n"));
+ appendPQExpBuffer(&buf,
"SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))\n"
"FROM pg_catalog.pg_rewrite r\n"
"WHERE r.ev_class = '%s' AND r.rulename != '_RETURN' ORDER BY 1;",
@@ -3158,7 +3177,8 @@ describeOneTableDetails(const char *schemaname,
PGresult *result;
int tuples;
- printfPQExpBuffer(&buf,
+ printfPQExpBuffer(&buf, _("/* Get information about each trigger on this table */\n"));
+ appendPQExpBuffer(&buf,
"SELECT t.tgname, "
"pg_catalog.pg_get_triggerdef(t.oid, true), "
"t.tgenabled, t.tgisinternal,\n");
@@ -3377,6 +3397,7 @@ describeOneTableDetails(const char *schemaname,
/* print tables inherited from (exclude partitioned parents) */
printfPQExpBuffer(&buf,
+ "/* Find tables inherited from */\n"
"SELECT c.oid::pg_catalog.regclass\n"
"FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i\n"
"WHERE c.oid = i.inhparent AND i.inhrelid = '%s'\n"
@@ -3413,8 +3434,9 @@ describeOneTableDetails(const char *schemaname,
}
/* print child tables (with additional info if partitions) */
+ printfPQExpBuffer(&buf, _("/* Get information about child tables */\n"));
if (pset.sversion >= 140000)
- printfPQExpBuffer(&buf,
+ appendPQExpBuffer(&buf,
"SELECT c.oid::pg_catalog.regclass, c.relkind,"
" inhdetachpending,"
" pg_catalog.pg_get_expr(c.relpartbound, c.oid)\n"
@@ -3424,7 +3446,7 @@ describeOneTableDetails(const char *schemaname,
" c.oid::pg_catalog.regclass::pg_catalog.text;",
oid);
else if (pset.sversion >= 100000)
- printfPQExpBuffer(&buf,
+ appendPQExpBuffer(&buf,
"SELECT c.oid::pg_catalog.regclass, c.relkind,"
" false AS inhdetachpending,"
" pg_catalog.pg_get_expr(c.relpartbound, c.oid)\n"
@@ -3434,7 +3456,7 @@ describeOneTableDetails(const char *schemaname,
" c.oid::pg_catalog.regclass::pg_catalog.text;",
oid);
else
- printfPQExpBuffer(&buf,
+ appendPQExpBuffer(&buf,
"SELECT c.oid::pg_catalog.regclass, c.relkind,"
" false AS inhdetachpending, NULL\n"
"FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i\n"
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: Adding comments to help understand psql hidden queries
In-Reply-To: <CAKAnmmJz8Hh=8Ru8jgzySPWmLBhnv4=oc_0KRiz-UORJ0Dex+w@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox