public inbox for [email protected]
help / color / mirror / Atom feedRe: Adding comments to help understand psql hidden queries
6+ messages / 4 participants
[nested] [flat]
* Re: Adding comments to help understand psql hidden queries
@ 2024-02-01 22:39 David Christensen <[email protected]>
2024-03-15 13:21 ` Re: Adding comments to help understand psql hidden queries Jim Jones <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: David Christensen @ 2024-02-01 22:39 UTC (permalink / raw)
To: Greg Sabino Mullane <[email protected]>; +Cc: pgsql-hackers
On Thu, Feb 1, 2024 at 4:34 PM Greg Sabino Mullane <[email protected]> wrote:
>
> 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
Thanks, this looks like some helpful information. In the same vein,
I'm including a patch which adds information about the command that
generates the given query as well (atop your commit). This will
modify the query line to include the command itself:
/******** QUERY (\dRs) *********/
Best,
David
Attachments:
[application/octet-stream] 0001-Add-output-of-the-command-that-got-us-here-to-the-QU.patch (2.6K, 2-0001-Add-output-of-the-command-that-got-us-here-to-the-QU.patch)
download | inline diff:
From 07f75c6220297bb2fba7c80b666445f9207cffbe Mon Sep 17 00:00:00 2001
From: David Christensen <[email protected]>
Date: Thu, 1 Feb 2024 14:58:34 -0600
Subject: [PATCH] Add output of the command that got us here to the QUERY
output
---
src/bin/psql/command.c | 5 +++++
src/bin/psql/common.c | 16 ++++++++++++----
2 files changed, 17 insertions(+), 4 deletions(-)
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 5c906e4806..6e55f81b0f 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -56,6 +56,8 @@ typedef enum EditableObjectType
EditableView,
} EditableObjectType;
+char *curcmd = NULL;
+
/* local function declarations */
static backslashResult exec_command(const char *cmd,
PsqlScanState scan_state,
@@ -307,6 +309,7 @@ exec_command(const char *cmd,
cmd);
}
+ curcmd = cmd;
if (strcmp(cmd, "a") == 0)
status = exec_command_a(scan_state, active_branch);
else if (strcmp(cmd, "bind") == 0)
@@ -423,6 +426,8 @@ exec_command(const char *cmd,
else
status = PSQL_CMD_UNKNOWN;
+ curcmd = NULL;
+
/*
* All the commands that return PSQL_CMD_SEND want to execute previous_buf
* if query_buf is empty. For convenience we implement that here, not in
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 76e01b02a3..7749656f5f 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -42,6 +42,7 @@ static int ExecQueryAndProcessResults(const char *query,
static bool command_no_begin(const char *query);
static bool is_select_command(const char *query);
+extern char *curcmd;
/*
* openQueryOutputFile --- attempt to open a query output file
@@ -581,6 +582,7 @@ PGresult *
PSQLexec(const char *query)
{
PGresult *res;
+ char *label = "";
if (!pset.db)
{
@@ -588,21 +590,27 @@ PSQLexec(const char *query)
return NULL;
}
+ if (curcmd)
+ label = psprintf(" (\\%s)", curcmd);
+
if (pset.echo_hidden != PSQL_ECHO_HIDDEN_OFF)
{
- printf(_("/******** QUERY *********/\n"
+ printf(_("/******** QUERY%s *********/\n"
"%s\n"
- "/************************/\n\n"), query);
+ "/************************/\n\n"), label, query);
fflush(stdout);
if (pset.logfile)
{
fprintf(pset.logfile,
- _("/******** QUERY *********/\n"
+ _("/******** QUERY%s *********/\n"
"%s\n"
- "/************************/\n\n"), query);
+ "/************************/\n\n"), label, query);
fflush(pset.logfile);
}
+ if (curcmd)
+ pfree(label);
+
if (pset.echo_hidden == PSQL_ECHO_HIDDEN_NOEXEC)
return NULL;
}
--
2.39.3 (Apple Git-145)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Adding comments to help understand psql hidden queries
2024-02-01 22:39 Re: Adding comments to help understand psql hidden queries David Christensen <[email protected]>
@ 2024-03-15 13:21 ` Jim Jones <[email protected]>
2024-03-21 17:31 ` Re: Adding comments to help understand psql hidden queries David Christensen <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Jim Jones @ 2024-03-15 13:21 UTC (permalink / raw)
To: David Christensen <[email protected]>; Greg Sabino Mullane <[email protected]>; +Cc: pgsql-hackers
Hi Greg, hi David
On 01.02.24 23:39, David Christensen wrote:
> On Thu, Feb 1, 2024 at 4:34 PM Greg Sabino Mullane <[email protected]> wrote:
>> 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
> Thanks, this looks like some helpful information. In the same vein,
> I'm including a patch which adds information about the command that
> generates the given query as well (atop your commit). This will
> modify the query line to include the command itself:
>
> /******** QUERY (\dRs) *********/
>
> Best,
>
> David
Having this kind of information in each query would have saved me a lot
of time in the past :) +1
There is a tiny little issue in the last patch (qualifiers):
command.c:312:16: warning: assignment discards ‘const’ qualifier from
pointer target type [-Wdiscarded-qualifiers]
312 | curcmd = cmd;
Thanks
--
Jim
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Adding comments to help understand psql hidden queries
2024-02-01 22:39 Re: Adding comments to help understand psql hidden queries David Christensen <[email protected]>
2024-03-15 13:21 ` Re: Adding comments to help understand psql hidden queries Jim Jones <[email protected]>
@ 2024-03-21 17:31 ` David Christensen <[email protected]>
2024-03-21 18:14 ` Re: Adding comments to help understand psql hidden queries David Christensen <[email protected]>
2024-03-21 22:20 ` Re: Adding comments to help understand psql hidden queries Peter Eisentraut <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: David Christensen @ 2024-03-21 17:31 UTC (permalink / raw)
To: Jim Jones <[email protected]>; +Cc: Greg Sabino Mullane <[email protected]>; pgsql-hackers
Hi Jim,
Thanks for the feedback. Enclosed is a v2 of this series(?) rebased
and with that warning fixed; @Greg Sabino Mullane I just created a
commit on your behalf with the message to hackers. I'm also creating
a commit-fest entry for this thread.
Best,
David
Attachments:
[application/octet-stream] v2-0002-Add-output-of-the-command-that-got-us-here-to-the.patch (2.6K, 2-v2-0002-Add-output-of-the-command-that-got-us-here-to-the.patch)
download | inline diff:
From a54f4e7bd56ab32565d15e9b022f1481cd0f7fdc Mon Sep 17 00:00:00 2001
From: David Christensen <[email protected]>
Date: Thu, 21 Mar 2024 12:27:32 -0500
Subject: [PATCH v2 2/2] Add output of the command that got us here to the
QUERY output
---
src/bin/psql/command.c | 5 +++++
src/bin/psql/common.c | 16 ++++++++++++----
2 files changed, 17 insertions(+), 4 deletions(-)
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 9b0fa041f7..4cfb91e134 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -56,6 +56,8 @@ typedef enum EditableObjectType
EditableView,
} EditableObjectType;
+char *curcmd = NULL;
+
/* local function declarations */
static backslashResult exec_command(const char *cmd,
PsqlScanState scan_state,
@@ -307,6 +309,7 @@ exec_command(const char *cmd,
cmd);
}
+ curcmd = (char *)cmd;
if (strcmp(cmd, "a") == 0)
status = exec_command_a(scan_state, active_branch);
else if (strcmp(cmd, "bind") == 0)
@@ -423,6 +426,8 @@ exec_command(const char *cmd,
else
status = PSQL_CMD_UNKNOWN;
+ curcmd = NULL;
+
/*
* All the commands that return PSQL_CMD_SEND want to execute previous_buf
* if query_buf is empty. For convenience we implement that here, not in
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 76e01b02a3..7749656f5f 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -42,6 +42,7 @@ static int ExecQueryAndProcessResults(const char *query,
static bool command_no_begin(const char *query);
static bool is_select_command(const char *query);
+extern char *curcmd;
/*
* openQueryOutputFile --- attempt to open a query output file
@@ -581,6 +582,7 @@ PGresult *
PSQLexec(const char *query)
{
PGresult *res;
+ char *label = "";
if (!pset.db)
{
@@ -588,21 +590,27 @@ PSQLexec(const char *query)
return NULL;
}
+ if (curcmd)
+ label = psprintf(" (\\%s)", curcmd);
+
if (pset.echo_hidden != PSQL_ECHO_HIDDEN_OFF)
{
- printf(_("/******** QUERY *********/\n"
+ printf(_("/******** QUERY%s *********/\n"
"%s\n"
- "/************************/\n\n"), query);
+ "/************************/\n\n"), label, query);
fflush(stdout);
if (pset.logfile)
{
fprintf(pset.logfile,
- _("/******** QUERY *********/\n"
+ _("/******** QUERY%s *********/\n"
"%s\n"
- "/************************/\n\n"), query);
+ "/************************/\n\n"), label, query);
fflush(pset.logfile);
}
+ if (curcmd)
+ pfree(label);
+
if (pset.echo_hidden == PSQL_ECHO_HIDDEN_NOEXEC)
return NULL;
}
--
2.39.3 (Apple Git-146)
[application/octet-stream] v2-0001-Include-SQL-comments-on-echo-hidden-output.patch (14.6K, 3-v2-0001-Include-SQL-comments-on-echo-hidden-output.patch)
download | inline diff:
From 3f511da8c5fdc4a89ecc09a4d5b9b8eb76fee80f Mon Sep 17 00:00:00 2001
From: Greg Sabino Mullane <[email protected]>
Date: Thu, 21 Mar 2024 12:12:54 -0500
Subject: [PATCH v2 1/2] Include SQL comments on --echo-hidden output
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;
/************************/
---
src/bin/psql/describe.c | 82 ++++++++++++++++++++++++++---------------
1 file changed, 52 insertions(+), 30 deletions(-)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 68b2ea8872..7f272b2ac1 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1586,9 +1586,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, "
@@ -1606,7 +1607,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, "
@@ -1623,7 +1624,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, "
@@ -1640,7 +1641,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, "
@@ -1657,7 +1658,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, "
@@ -1718,9 +1719,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"
@@ -1744,7 +1746,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"
@@ -1771,7 +1773,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"
@@ -1850,7 +1853,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++;
@@ -2150,7 +2154,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 ");
@@ -2205,7 +2210,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);
@@ -2227,7 +2233,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"
@@ -2255,7 +2262,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 "
@@ -2372,7 +2380,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 "
@@ -2510,6 +2519,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))
{
@@ -2517,7 +2528,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"
@@ -2530,7 +2541,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"
@@ -2584,9 +2595,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"
@@ -2598,7 +2610,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"
@@ -2636,7 +2648,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");
@@ -2716,9 +2729,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, "
@@ -2815,7 +2829,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, "
@@ -2894,7 +2908,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"
@@ -2977,9 +2992,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"
@@ -3011,7 +3027,7 @@ describeOneTableDetails(const char *schemaname,
}
else
{
- printfPQExpBuffer(&buf,
+ appendPQExpBuffer(&buf,
"SELECT pubname\n"
" , NULL\n"
" , NULL\n"
@@ -3061,7 +3077,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"
@@ -3133,7 +3151,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;",
@@ -3170,7 +3189,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");
@@ -3389,6 +3409,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"
@@ -3425,8 +3446,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"
@@ -3436,7 +3458,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"
@@ -3446,7 +3468,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"
--
2.39.3 (Apple Git-146)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Adding comments to help understand psql hidden queries
2024-02-01 22:39 Re: Adding comments to help understand psql hidden queries David Christensen <[email protected]>
2024-03-15 13:21 ` Re: Adding comments to help understand psql hidden queries Jim Jones <[email protected]>
2024-03-21 17:31 ` Re: Adding comments to help understand psql hidden queries David Christensen <[email protected]>
@ 2024-03-21 18:14 ` David Christensen <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: David Christensen @ 2024-03-21 18:14 UTC (permalink / raw)
To: [email protected]; +Cc: David Christensen <[email protected]>; Greg Sabino Mullane <[email protected]>
Created the CF entry in commitfest 48 but didn't see it was already in 47; closing the CFEntry in 48. (Doesn't appear to be a different status than "Withdrawn"...)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Adding comments to help understand psql hidden queries
2024-02-01 22:39 Re: Adding comments to help understand psql hidden queries David Christensen <[email protected]>
2024-03-15 13:21 ` Re: Adding comments to help understand psql hidden queries Jim Jones <[email protected]>
2024-03-21 17:31 ` Re: Adding comments to help understand psql hidden queries David Christensen <[email protected]>
@ 2024-03-21 22:20 ` Peter Eisentraut <[email protected]>
2024-03-22 14:46 ` Re: Adding comments to help understand psql hidden queries Greg Sabino Mullane <[email protected]>
1 sibling, 1 reply; 6+ messages in thread
From: Peter Eisentraut @ 2024-03-21 22:20 UTC (permalink / raw)
To: David Christensen <[email protected]>; Jim Jones <[email protected]>; +Cc: Greg Sabino Mullane <[email protected]>; pgsql-hackers
On 21.03.24 18:31, David Christensen wrote:
> Thanks for the feedback. Enclosed is a v2 of this series(?) rebased
> and with that warning fixed; @Greg Sabino Mullane I just created a
> commit on your behalf with the message to hackers. I'm also creating
> a commit-fest entry for this thread.
I don't think your patch takes into account that the
/**... QUERY ...**/
...
/**... ...**/
lines are supposed to align vertically. With your patch, the first line
would have variable length depending on the command.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Adding comments to help understand psql hidden queries
2024-02-01 22:39 Re: Adding comments to help understand psql hidden queries David Christensen <[email protected]>
2024-03-15 13:21 ` Re: Adding comments to help understand psql hidden queries Jim Jones <[email protected]>
2024-03-21 17:31 ` Re: Adding comments to help understand psql hidden queries David Christensen <[email protected]>
2024-03-21 22:20 ` Re: Adding comments to help understand psql hidden queries Peter Eisentraut <[email protected]>
@ 2024-03-22 14:46 ` Greg Sabino Mullane <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Greg Sabino Mullane @ 2024-03-22 14:46 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: David Christensen <[email protected]>; Jim Jones <[email protected]>; pgsql-hackers
On Thu, Mar 21, 2024 at 6:20 PM Peter Eisentraut <[email protected]>
wrote:
> lines are supposed to align vertically. With your patch, the first line
> would have variable length depending on the command.
>
Yes, that is a good point. Aligning those would be quite tricky, what if we
just kept a standard width for the closing query? Probably the 24 stars we
currently have to match "QUERY", which it appears nobody has changed for
translation purposes yet anyway. (If I am reading the code correctly, it
would be up to the translators to maintain the vertical alignment).
Cheers,
Greg
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-03-22 14:46 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-01 22:39 Re: Adding comments to help understand psql hidden queries David Christensen <[email protected]>
2024-03-15 13:21 ` Jim Jones <[email protected]>
2024-03-21 17:31 ` David Christensen <[email protected]>
2024-03-21 18:14 ` David Christensen <[email protected]>
2024-03-21 22:20 ` Peter Eisentraut <[email protected]>
2024-03-22 14:46 ` Greg Sabino Mullane <[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