public inbox for [email protected]
help / color / mirror / Atom feedFrom: David Christensen <[email protected]>
To: Greg Sabino Mullane <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: Adding comments to help understand psql hidden queries
Date: Thu, 1 Feb 2024 16:39:08 -0600
Message-ID: <CAHM0NXiD15-23=r1B9rHzKhS8e0+zwp_a6PncdLNetyF0xpNpA@mail.gmail.com> (raw)
In-Reply-To: <CAKAnmmJz8Hh=8Ru8jgzySPWmLBhnv4=oc_0KRiz-UORJ0Dex+w@mail.gmail.com>
References: <CAKAnmmJz8Hh=8Ru8jgzySPWmLBhnv4=oc_0KRiz-UORJ0Dex+w@mail.gmail.com>
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)
view thread (6+ messages) latest in thread
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], [email protected]
Subject: Re: Adding comments to help understand psql hidden queries
In-Reply-To: <CAHM0NXiD15-23=r1B9rHzKhS8e0+zwp_a6PncdLNetyF0xpNpA@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