MIME-Version: 1.0
References: <CAKAnmmJz8Hh=8Ru8jgzySPWmLBhnv4=oc_0KRiz-UORJ0Dex+w@mail.gmail.com>
In-Reply-To: <CAKAnmmJz8Hh=8Ru8jgzySPWmLBhnv4=oc_0KRiz-UORJ0Dex+w@mail.gmail.com>
From: David Christensen <david+pg@pgguru.net>
Date: Thu, 1 Feb 2024 16:39:08 -0600
Message-ID: <CAHM0NXiD15-23=r1B9rHzKhS8e0+zwp_a6PncdLNetyF0xpNpA@mail.gmail.com>
Subject: Re: Adding comments to help understand psql hidden queries
To: Greg Sabino Mullane <htamfids@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: multipart/mixed; boundary="000000000000877d74061059aa0a"
Archived-At: <https://www.postgresql.org/message-id/CAHM0NXiD15-23%3Dr1B9rHzKhS8e0%2Bzwp_a6PncdLNetyF0xpNpA%40mail.gmail.com>
Precedence: bulk

--000000000000877d74061059aa0a
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Thu, Feb 1, 2024 at 4:34=E2=80=AFPM Greg Sabino Mullane <htamfids@gmail.=
com> 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 m=
agic than "\d relation", but it suffers from needing a lot of separate quer=
ies to gather all of the information it needs. Unfortunately, those queries=
 can get overwhelming and hard to figure out which one does what, especiall=
y 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 ne=
ed 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 =3D '{0}' THEN NULL ELSE pg_catalog.array_to_str=
ing(array(select rolname from pg_catalog.pg_roles where oid =3D any (pol.po=
lroles) 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 =3D '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

--000000000000877d74061059aa0a
Content-Type: application/octet-stream; 
	name="0001-Add-output-of-the-command-that-got-us-here-to-the-QU.patch"
Content-Disposition: attachment; 
	filename="0001-Add-output-of-the-command-that-got-us-here-to-the-QU.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_ls3srwir0>
X-Attachment-Id: f_ls3srwir0

RnJvbSAwN2Y3NWM2MjIwMjk3YmIyZmJhN2M4MGI2NjY0NDVmOTIwN2NmZmJlIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBEYXZpZCBDaHJpc3RlbnNlbiA8ZGF2aWQuY2hyaXN0ZW5zZW5A
Y3J1bmNoeWRhdGEuY29tPgpEYXRlOiBUaHUsIDEgRmViIDIwMjQgMTQ6NTg6MzQgLTA2MDAKU3Vi
amVjdDogW1BBVENIXSBBZGQgb3V0cHV0IG9mIHRoZSBjb21tYW5kIHRoYXQgZ290IHVzIGhlcmUg
dG8gdGhlIFFVRVJZCiBvdXRwdXQKCi0tLQogc3JjL2Jpbi9wc3FsL2NvbW1hbmQuYyB8ICA1ICsr
KysrCiBzcmMvYmluL3BzcWwvY29tbW9uLmMgIHwgMTYgKysrKysrKysrKysrLS0tLQogMiBmaWxl
cyBjaGFuZ2VkLCAxNyBpbnNlcnRpb25zKCspLCA0IGRlbGV0aW9ucygtKQoKZGlmZiAtLWdpdCBh
L3NyYy9iaW4vcHNxbC9jb21tYW5kLmMgYi9zcmMvYmluL3BzcWwvY29tbWFuZC5jCmluZGV4IDVj
OTA2ZTQ4MDYuLjZlNTVmODFiMGYgMTAwNjQ0Ci0tLSBhL3NyYy9iaW4vcHNxbC9jb21tYW5kLmMK
KysrIGIvc3JjL2Jpbi9wc3FsL2NvbW1hbmQuYwpAQCAtNTYsNiArNTYsOCBAQCB0eXBlZGVmIGVu
dW0gRWRpdGFibGVPYmplY3RUeXBlCiAJRWRpdGFibGVWaWV3LAogfSBFZGl0YWJsZU9iamVjdFR5
cGU7CiAKK2NoYXIgKmN1cmNtZCA9IE5VTEw7CisKIC8qIGxvY2FsIGZ1bmN0aW9uIGRlY2xhcmF0
aW9ucyAqLwogc3RhdGljIGJhY2tzbGFzaFJlc3VsdCBleGVjX2NvbW1hbmQoY29uc3QgY2hhciAq
Y21kLAogCQkJCQkJCQkJUHNxbFNjYW5TdGF0ZSBzY2FuX3N0YXRlLApAQCAtMzA3LDYgKzMwOSw3
IEBAIGV4ZWNfY29tbWFuZChjb25zdCBjaGFyICpjbWQsCiAJCQkJCSAgIGNtZCk7CiAJfQogCisJ
Y3VyY21kID0gY21kOwogCWlmIChzdHJjbXAoY21kLCAiYSIpID09IDApCiAJCXN0YXR1cyA9IGV4
ZWNfY29tbWFuZF9hKHNjYW5fc3RhdGUsIGFjdGl2ZV9icmFuY2gpOwogCWVsc2UgaWYgKHN0cmNt
cChjbWQsICJiaW5kIikgPT0gMCkKQEAgLTQyMyw2ICs0MjYsOCBAQCBleGVjX2NvbW1hbmQoY29u
c3QgY2hhciAqY21kLAogCWVsc2UKIAkJc3RhdHVzID0gUFNRTF9DTURfVU5LTk9XTjsKIAorCWN1
cmNtZCA9IE5VTEw7CisKIAkvKgogCSAqIEFsbCB0aGUgY29tbWFuZHMgdGhhdCByZXR1cm4gUFNR
TF9DTURfU0VORCB3YW50IHRvIGV4ZWN1dGUgcHJldmlvdXNfYnVmCiAJICogaWYgcXVlcnlfYnVm
IGlzIGVtcHR5LiAgRm9yIGNvbnZlbmllbmNlIHdlIGltcGxlbWVudCB0aGF0IGhlcmUsIG5vdCBp
bgpkaWZmIC0tZ2l0IGEvc3JjL2Jpbi9wc3FsL2NvbW1vbi5jIGIvc3JjL2Jpbi9wc3FsL2NvbW1v
bi5jCmluZGV4IDc2ZTAxYjAyYTMuLjc3NDk2NTZmNWYgMTAwNjQ0Ci0tLSBhL3NyYy9iaW4vcHNx
bC9jb21tb24uYworKysgYi9zcmMvYmluL3BzcWwvY29tbW9uLmMKQEAgLTQyLDYgKzQyLDcgQEAg
c3RhdGljIGludAlFeGVjUXVlcnlBbmRQcm9jZXNzUmVzdWx0cyhjb25zdCBjaGFyICpxdWVyeSwK
IHN0YXRpYyBib29sIGNvbW1hbmRfbm9fYmVnaW4oY29uc3QgY2hhciAqcXVlcnkpOwogc3RhdGlj
IGJvb2wgaXNfc2VsZWN0X2NvbW1hbmQoY29uc3QgY2hhciAqcXVlcnkpOwogCitleHRlcm4gY2hh
ciAqY3VyY21kOwogCiAvKgogICogb3BlblF1ZXJ5T3V0cHV0RmlsZSAtLS0gYXR0ZW1wdCB0byBv
cGVuIGEgcXVlcnkgb3V0cHV0IGZpbGUKQEAgLTU4MSw2ICs1ODIsNyBAQCBQR3Jlc3VsdCAqCiBQ
U1FMZXhlYyhjb25zdCBjaGFyICpxdWVyeSkKIHsKIAlQR3Jlc3VsdCAgICpyZXM7CisJY2hhciAq
bGFiZWwgPSAiIjsKIAogCWlmICghcHNldC5kYikKIAl7CkBAIC01ODgsMjEgKzU5MCwyNyBAQCBQ
U1FMZXhlYyhjb25zdCBjaGFyICpxdWVyeSkKIAkJcmV0dXJuIE5VTEw7CiAJfQogCisJaWYgKGN1
cmNtZCkKKwkJbGFiZWwgPSBwc3ByaW50ZigiIChcXCVzKSIsIGN1cmNtZCk7CisKIAlpZiAocHNl
dC5lY2hvX2hpZGRlbiAhPSBQU1FMX0VDSE9fSElEREVOX09GRikKIAl7Ci0JCXByaW50ZihfKCIv
KioqKioqKiogUVVFUlkgKioqKioqKioqL1xuIgorCQlwcmludGYoXygiLyoqKioqKioqIFFVRVJZ
JXMgKioqKioqKioqL1xuIgogCQkJCSAiJXNcbiIKLQkJCQkgIi8qKioqKioqKioqKioqKioqKioq
KioqKiovXG5cbiIpLCBxdWVyeSk7CisJCQkJICIvKioqKioqKioqKioqKioqKioqKioqKioqL1xu
XG4iKSwgbGFiZWwsIHF1ZXJ5KTsKIAkJZmZsdXNoKHN0ZG91dCk7CiAJCWlmIChwc2V0LmxvZ2Zp
bGUpCiAJCXsKIAkJCWZwcmludGYocHNldC5sb2dmaWxlLAotCQkJCQlfKCIvKioqKioqKiogUVVF
UlkgKioqKioqKioqL1xuIgorCQkJCQlfKCIvKioqKioqKiogUVVFUlklcyAqKioqKioqKiovXG4i
CiAJCQkJCSAgIiVzXG4iCi0JCQkJCSAgIi8qKioqKioqKioqKioqKioqKioqKioqKiovXG5cbiIp
LCBxdWVyeSk7CisJCQkJCSAgIi8qKioqKioqKioqKioqKioqKioqKioqKiovXG5cbiIpLCBsYWJl
bCwgcXVlcnkpOwogCQkJZmZsdXNoKHBzZXQubG9nZmlsZSk7CiAJCX0KIAorCQlpZiAoY3VyY21k
KQorCQkJcGZyZWUobGFiZWwpOworCiAJCWlmIChwc2V0LmVjaG9faGlkZGVuID09IFBTUUxfRUNI
T19ISURERU5fTk9FWEVDKQogCQkJcmV0dXJuIE5VTEw7CiAJfQotLSAKMi4zOS4zIChBcHBsZSBH
aXQtMTQ1KQoK
--000000000000877d74061059aa0a--