Subject: Re: Adding comments to help understand psql hidden queries

From: Maiquel Grassi <[email-masked]>
Date: Tue, 1 Apr 2025 00:02:25 +0000

Hi!
I have read the discussion and would like
to share my humble opinion. I believe that
a visually appealing way to display the
output on the screen is to ensure symmetry
in the length of asterisks and description lines.
I imagine someone looking at the screen and
focusing on symmetrical details. Therefore,
the string length should serve as the basis for
the calculation. If the description length is an
even number, then the formula would be:

((description length − 7) / 2)​

Placing this result of asterisks on both sides of
the string ' QUERY ' ensures balance.
If the description length is an odd number,
then place:

((description length − 7) / 2)​
asterisks on the right side and:
(((description length − 7) / 2) ​+ 1)

asterisks on the left side.

This method does not always result in a perfectly
symmetric number of asterisks, but it provides a
more visually aligned appearance. At the end of
the SQL code, we should also include a line
terminator of the same length of the
description. The 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;
/********************************************/

Regards,
Maiquel.

---

From: Greg Sabino Mullane <[email-masked]>
Date: Sun, 22 Mar 2026 11:15:38 -0400

Going back through all the feedback and comments, plus having some time to
think things through, I am including a new patch, v7, that greatly
simplifies things, and only makes changes inside of describe.c. In the
spirit of not letting the perfect be the enemy of the good, I'm not
worrying at all about the number of stars, or the width, and simply adding
a small consistent description at the top of each query. I also realized
that having these queries show up in someone's server log could be quite
confusing, so I had them output as part of the query itself. In other
words, they show up in both psql -E and the server logs. A few benefits to
doing this:

* Simplifies the code
* Makes searching the web for what generated this code a lot easier (a
comment versus a giant blob of SQL)
* Makes all the SQL a little bit self-documented everywhere it shows up
* Easier to maintain describe.c, as the comment is always
printfPQExpBuffer, and everything
else is appendPQExpBuffer, rather than trying to figure out which to use
for each section of SQL.
Also removes bugs like the append-first in objectDescription()

Here's what the new output looks like via psql -E:

/******** QUERY *********/
/* Get matching aggregates */
SELECT n.nspname as "Schema",
  p.proname AS "Name",
  pg_catalog.format_type(p.prorettype, NULL) AS "Result data type",
  CASE WHEN p.pronargs = 0
    THEN CAST('*' AS pg_catalog.text)
    ELSE pg_catalog.pg_get_function_arguments(p.oid)
  END AS "Argument data types",
  pg_catalog.obj_description(p.oid, 'pg_proc') as "Description"
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE p.prokind = 'a'
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_function_is_visible(p.oid)
ORDER BY 1, 2, 4;
/************************/

and more examples:

/******** QUERY *********/
/* Get publications that exclude this table */
SELECT pubname
FROM pg_catalog.pg_publication p
JOIN pg_catalog.pg_publication_rel pr ON p.oid = pr.prpubid
WHERE (pr.prrelid = '16403' OR pr.prrelid =
pg_catalog.pg_partition_root('16403'))
AND pr.prexcept
ORDER BY 1;
/************************/

/******** QUERY *********/
/* Get parent tables */
SELECT c.oid::pg_catalog.regclass
FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
WHERE c.oid = i.inhparent AND i.inhrelid = '16403'
  AND c.relkind != 'p' AND c.relkind != 'I'
ORDER BY inhseqno;
/************************/

Cheers,
Greg

---

From: Greg Sabino Mullane <[email-masked]>
Date: Mon, 23 Mar 2026 18:50:16 -0400

Thanks for looking this over. I'm pretty happy with the patch as is now. I
agree the INTERNAL QUERY is a nice touch. I once thought about adding
"psql" into the header somehow as a kind of application_name self
labelling, but I think INTERNAL QUERY will be distinct enough.

Notably, I didn't like that some of the headers said "table" and some said
> "relation".  I made them all say "table", although you could certainly
> argue for the opposite.

I originally had "table", but then it felt weird in my testing when I was
describing a sequence or view it said table. So I'm a weak +1 for relation.

--
Cheers,
Greg

---

From: Tom Lane <[email-masked]>
Date: Thu, 26 Mar 2026 11:41:53 -0400

I wrote:
> Greg Sabino Mullane <[email-masked]> writes:

> My preference for "table" is likewise weak.  Anyone else have an
> opinion?

[ crickets... ]

After sleeping on it and taking another look at the output, I agree
that we need to use a mix of "relation" and "table", because some of
these queries definitely apply to all kinds of pg_class entries, while
for others we must be dealing with a table (or something reasonably
table-like, such as a foreign table).  I made another pass over it
to fix that, and pushed the results.

Thanks for working on this!  I know it's been a long process,
but sometimes that's what it takes to get to a consensus.

			regards, tom lane