psql \dh: List High-Level (Root) Tables and Indexes

Sorry an id was missing in psql documentation, thus CI was failing.
Please find attached a patch that fixes this issue.

Best Regards,
Sadeq Dousti
Tech Lead and Staff Software Engineer at Trade Republic Bank GmbH

Sadeq Dousti <msdousti@gmail.com> writes:

Please find attached the patch to add a new metacommand (\dh) to psql.

I don't have a strong feeling one way or the other about whether this
is useful functionality or not. But if we do want it, I'd like to
bikeshed on the name a bit. "High-level" is not terminology we use
anywhere else, and it's ambiguous too (just how high in a partition
tree does a table need to be to be listed?). We do use the terms
"root partition" or "partition root". So I'd prefer something
based on those terms. Possibilities:

1. Use "\dr" or maybe better "\dR" (letting \dr remain available for
some future command about roles). The trouble with either of these
is that the name is already partially overloaded, since we already
have \drds, \drg, \dRp, \dRs.

2. Extend the existing \dP (display partitions) command with some
flag that restricts the listing to root partitions, probably
"r" for "root".

\dP[itnx+] [PATTERN] list [only index/table] partitioned relations [n=nested
]

I kind of like #2 better, but am not set on it.

While we're at it maybe we could bring some sanity to the "n"
flag, which seems to have several bizarre properties like
working differently depending on whether you give a pattern.
It looks from this documentation like it's the inverse of what
"r" would do, but testing says it's not.

regards, tom lane

Sorry an id was missing in psql documentation, thus CI was failing.

Combining patches into one, as otherwise CI does not combine them.

Best Regards
Sadeq Dousti

Hi Tom,

Thanks a lot for looking into this!

"High-level" is not terminology we use anywhere else
We do use the terms "root partition" or "partition root".

I agree "high-level" is not standard, but "root partition" also entails
the table is actually partitioned. This is not necessarily the case.
The suggested command will show all non-partitioned tables, plus the
root partitions.

So, in the example I provided, t is a non-partitioned table, and z is
the root partition. The command shows them both:

postgres=# \dht
List of root tables
Schema | Name | Owner
--------+------+-------
public | t | sadeq
public | z | sadeq

The reason I called them high-level is that they provide a
high-level overview into the database table/index structure.

Extend the existing \dP (display partitions)

I also thought about it at first, but that command is for displaying
partitions, while I want the opposite: Don't display partitions at all.
I tried to detail why I didn't use \dP or any existing \d commands
in the first email of the thread (section "Design Decision").

Best Regards,
Sadeq Dousti

That definition seems bizarre, and even less derivable
from the term "high-level" than what I thought you meant.
You really need some other terminology, I think.

Totally agree about the naming. There's no other terminology known to
me, but I'll definitely think about it. Of course, I'm very open to
suggestions from you or anyone else in the community.

The way we can think about the concept: these are "top" tables,
regardless of whether they have children (partitions) or not.

(BTW, you forgot to cc the list, so nobody else saw this defense
of your idea.)

Oops! Thanks for mentioning this. I sent the previous email to the list
as well.

Best Regards,
Sadeq Dousti

Sadeq Dousti <msdousti@gmail.com> writes:

"High-level" is not terminology we use anywhere else
We do use the terms "root partition" or "partition root".

I agree "high-level" is not standard, but "root partition" also entails
the table is actually partitioned. This is not necessarily the case.
The suggested command will show all non-partitioned tables, plus the
root partitions.

Hmm, that seems even less derivable from the term "high-level"
than what I thought you meant. However, if that's the behavior
you want, maybe we could extend "\d[ti...]" with an option that
suppresses partition-tree members? My first thought about that
is "n" for "not a partition member", but maybe somebody else
can invent a better name. (One problem with "n" is that "\dn"
without either "t" or "i" would mean something else. Using "N"
would fix that, but the capitalization seems a bit random.)

Independently of that, extending \dP with an option for "roots
only" seems like a good idea. But that's a different patch.

regards, tom lane

On Sun, Feb 23, 2025 at 4:34 PM Sadeq Dousti <msdousti@gmail.com> wrote:

Totally agree about the naming. There's no other terminology known to me,
but I'll definitely think about it. Of course, I'm very open to
suggestions from you or anyone else in the community.

The problem is that we are really tight on available letters for a bare \d.
I immediately thought of "adult" tables (those that are not children). But
both \da and \dA are used! The only other mnemonic option is "\de" where e
stands for elder tables (get it? elder because they are not children).

Would it be useful to you to have them as separate items? In other words,
not indexes and tables together, but some flag on both \dt and \di to limit
to non-child relations. I think as far as "core structure" the list of
tables is far more important than what indexes happen to be on them. Plus,
\d also shows sequences, which is even less useful information than
indexes, IMHO.

So maybe \dta and \dia? (adults only)

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

On Sun, Feb 23, 2025 at 4:31 PM Greg Sabino Mullane <htamfids@gmail.com>
wrote:

On Sun, Feb 23, 2025 at 4:34 PM Sadeq Dousti <msdousti@gmail.com> wrote:

Totally agree about the naming. There's no other terminology known to me,
but I'll definitely think about it. Of course, I'm very open to
suggestions from you or anyone else in the community.

The problem is that we are really tight on available letters for a
bare \d. I immediately thought of "adult" tables (those that are not
children). But both \da and \dA are used! The only other mnemonic option is
"\de" where e stands for elder tables (get it? elder because they are not
children).

How about:
\pset partition_roots_only 'true'

Then update all relevant queries to respect it.

That way no learning new ways to get the information you want but instead
can set a global filter to suppress those child nodes that really don't
need to be seen normally.

It would be nice if "N partition children suppressed due to
partition_roots_only" would appear in the output somewhere if going this
direction.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

How about:
\pset partition_roots_only 'true'

FWIW, I don't especially love this idea. None of our existing
\d-type commands use pset parameters for filtering, and I don't
see a reason to believe that people would typically want this
to be a persistent setting. I'd guess that sometimes you want
the tables filtered one way and sometimes the other.

regards, tom lane

po 24. 2. 2025 v 6:16 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

How about:
\pset partition_roots_only 'true'

FWIW, I don't especially love this idea. None of our existing
\d-type commands use pset parameters for filtering, and I don't
see a reason to believe that people would typically want this
to be a persistent setting. I'd guess that sometimes you want
the tables filtered one way and sometimes the other.

There can be numeric parameter that can specify the deep - some like

\dP 0

Regards

Pavel

Dear all,

I'm grateful for taking your time and providing various solutions!

Personally, I like Tom's idea of using N better:

* It can stand for "not a partition member" or "no partitions", and does
not provide any new terminology.
* It can be combined with various commands \dN, \dtN (or \dNt), \dNi
(or \diN), without causing confusion with existing command (e.g., \dn)
* While capitalization of N might seem arbitrary, it can be acceptable
as it's "negating" some concept

About other suggestions:

* While "a" (adult) and "e" (elders) are adorable, they introduce new
terminologies that are not widely known to the Postgres community. Being
super pedantic, an adult is always a child of some parent, and an elder
must always have one or more children. In addition, \da and \det already
exists.

* Using \pset: As Tom said, plus I think explicit is better than
implicit. Consider some DBA (or their colleague) adding this \pset into
.psqlrc, and then forget about it.

* Using \dP 0: The command \dP is already used to show partitioned
tables, and the default behavior (i.e., not \dPn) is to only show the
roots of the partition. What I'd like is to show roots as well as
non-partitioned tables.

If there are no hard objections, I'll proceed with adding the N letter
to \d commands.

Best Regards,
Sadeq Dousti

On Mon, Feb 24, 2025 at 3:07 AM Sadeq Dousti <msdousti@gmail.com> wrote:

* While capitalization of N might seem arbitrary, it can be acceptable as
it's "negating" some concept
...

If there are no hard objections, I'll proceed with adding the N letter to
\d commands.

Not strongly opposed, but "N" is a slightly odd as we already have "n" and
pg_dump uses both "n" and "N", where the latter is the inverse of the
former. But since I have no better idea at the moment, a weak +1 until more
bike shedders arrive. :)

Cheers,
Greg

P.S. On reflection, I do like having a capital letter more than a lowercase
for an exclusion case like this.

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

Thanks Greg and others for the feedback!

Please find attached the patch for implementing \dN (including \dNt, \dNi,
\dNit).

Best Regards,
Sadeq Dousti

The patch applies cleanly, and works as advertised. Nice work!

Quick notes:

* doc/src/sgml/ref/psql-ref.sgml

In the varlistentry section, the order should be the same as the other
places (N after m)

Line 1644 has an extra comma

Line 1651, maybe the example is simpler as \dNt to keep the wording better,
because "indexes that are not partitions" looks odd.

These bits:

pg_log_error("Did not find any%s relations named \"%s\".",
no_partition_description, pattern);

are not good for translation. We want things simple with replaceable
args/constants, but not replaceable words.

I think the myopt.title ones are fine.

* bin/psql/help.c:

\\dN[Sx+] [PATTERN] list relation, table, index (no partitions)

better as:

\\dN[Sx+] [PATTERN] list tables and indexes (no partitions)

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

Dear Greg,

Thank you so much for the kind and prompt review!

Please find the patches attached. The second patch (0002) is where I
applied the requested changes.

Best regards,
Sadeq Dousti

On Wed, Feb 26, 2025 at 1:01 AM Greg Sabino Mullane <htamfids@gmail.com>
wrote:

Changes look good to me, thanks. Can you make a new patch that applies a
single set of changes to HEAD?

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

Thanks a lot Greg!

Changes look good to me, thanks. Can you make a new patch that applies a

single set of changes to HEAD?

Please find attached the diff to Head in a single file.

Best Regards,
Sadeq Dousti

On Wed, 26 Feb 2025 at 23:21, Sadeq Dousti <msdousti@gmail.com> wrote:

Thanks a lot Greg!

Changes look good to me, thanks. Can you make a new patch that applies a single set of changes to HEAD?

Please find attached the diff to Head in a single file.

Currently we are supporting only PG13 and higher versions. I'm not
sure if we should support lesser than PG15 version:
+       /*
+        * Note: Declarative table partitioning is only supported as of Pg 10.0.
+        */
+       if (showNoPartitions && pset.sversion < 100000)
+       {
+               char            sverbuf[32];
+
+               pg_log_error("The server (version %s) does not support
declarative table partitioning.",
+
formatPGVersionNumber(pset.sversion, false,
+
            sverbuf, sizeof(sverbuf)));
+               return true;
+       }

Regards,
Vignesh

Thanks Vignesh for the review!

Currently we are supporting only PG13 and higher versions.

I understand that servers older than PG13 are no longer supported. But on
the client side, we still have this notice at the top of describe.c file,
which indicates that the client should support 9.2+.

* Support for the various \d ("describe") commands. Note that the current
* expectation is that all functions in this file will succeed when working
* with servers of versions 9.2 and up. It's okay to omit irrelevant
* information for an old server, but not to fail outright. (But failing
* against a pre-9.2 server is allowed.)

I'm just following the instructions here so as not to break anything
unwanted, and you can see for instance \dP is doing the same.

That said, I'm totally fine with removing the "if" from my patch, but first
I think a committer should update the above comment to the least supported
version for client code.

Best Regards,
Sadeq Dousti