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

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

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

"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

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

Re: Sadeq Dousti
> Thanks Greg and others for the feedback!
> 
> Please find attached the patch for implementing \dN (including \dNt, \dNi,
> \dNit).

I don't care particularly about the choice of letter, but I think this
is the wrong way round. It should be \dtN instead of \dNt.

Is the form \dN actually useful? For partitioned tables, you'd only
get the empty root indexes. If you actually wanted that, there's still
\dtiN.

Christoph

Re: Sadeq Dousti
> > I think this is the wrong way round.
> > It should be \dtN instead of \dNt.
> 
> Hi Christoph,
> The order does not matter, the user can use \dNt or \dtN, as they do
> exactly the same thing. Letters coming after \d can be freely permuted. If
> you mean a change to the documentation or tests, I can apply whatever order
> makes more sense, as I don't have any particular preference.

Oh ok, that's perfect then.

        HELP0("  \\dn[Sx+] [PATTERN]     list schemas\n");
+       HELP0("  \\dN[Sx+] [PATTERN]     list tables and indexes (no partitions)\n");
        HELP0("  \\do[Sx+] [OPPTRN [TYPEPTRN [TYPEPTRN]]]\n"

Is this really a new "top-level" \d command and not a flag for the
existing \d commands? I would think that the help texts should place
the N next to S which is a similar "show different set of things"
modifier:

\d[NSx+]                list tables, views, and sequences
\di[NSx+] [PATTERN]     list indexes
\dt[NSx+] [PATTERN]     list tables

For documentation and tests, the N should be at the end like S is at
the end in \dt[S+].

Christoph