Thread: [Proposal] Add \dAt [AMPTRN [TBLPTRN]] to list tables by Table Access Method in psql

On Tue, 15 Apr 2025 at 14:27, Srinath Reddy <srinath2133@gmail.com> wrote:
>
> Hi all,
>
> I'd like to propose adding a new `psql` meta-command:
>
>     \dAt [AMPTRN [TBLPTRN]]
>
> This would list all user tables that are using a specific Table Access Method (TAM), optionally filtered by table
namepattern. 
>
> Why?
> - Table Access Methods are increasingly used.
> - There's currently no native `\d`-style way to explore which tables are using a given TAM.
> - Users often need to check or debug TAM-based extensions and it'd be useful to have a shortcut.
>
> Example usage:
>
>     \dAt sometam        -- list all tables using sometam TAM
>     \dAt sometam auto%  -- filter to tables starting with 'auto'
>
> Would love to hear if this sounds like a useful addition — if so, I’d be happy to work on a patch. Also, please let
meknow if something similar has been discussed before. 

I think we can get both the details by SELECT command. Additionally,
this info should be visible by the admin only because there is no use
case for the user to know all the tables from one TAM.

postgres=# select * from pg_am;
 oid  | amname |      amhandler       | amtype
------+--------+----------------------+--------
    2 | heap   | heap_tableam_handler | t
  403 | btree  | bthandler            | i
  405 | hash   | hashhandler          | i
  783 | gist   | gisthandler          | i
 2742 | gin    | ginhandler           | i
 4000 | spgist | spghandler           | i
 3580 | brin   | brinhandler          | i
(7 rows)

postgres=#
postgres=# select relname from pg_class where relam = (select relam
from pg_am where amname = 'heap');
                    relname
------------------------------------------------
 test
 pg_statistic
 pg_type
 pg_toast_1255
 pg_toast_1255_index
 pg_toast_1247
 pg_toast_1247_index
 pg_toast_2604
 pg_toast_2604_index
 pg_toast_2606
 pg_toast_2606_index
 pg_toast_2610
 pg_toast_2610_index
 pg_toast_2612
-------------------------

In the above command, we can add FILTER also to SELECT only pattern
matching table only.

--
Thanks and Regards
Mahendra Singh Thalor
EnterpriseDB: http://www.enterprisedb.com

Greg Sabino Mullane <htamfids@gmail.com> writes:
> On Tue, Apr 15, 2025 at 4:57 AM Srinath Reddy <srinath2133@gmail.com> wrote:
>> - There's currently no native `\d`-style way to explore which tables are
>> using a given TAM.

> Perhaps there could be a more generic table-filtering mechanism for \d, but
> carving something out for such a niche case seems unwarranted, IMO.

I don't have a strong opinion on whether this functionality is worth
having in a psql meta-command.  But I don't like the proposed syntax
one bit.  In my mind the \dA group of meta-commands are supposed to
provide information on the *properties* of access methods.  Not on
what uses them.  It could be reasonable to have a \dAt command that
shows information about a table access method (although not much is
exposed at SQL level today, so there's not a lot for it to do).
But, for example, \dAf does not run around and find all indexes
using that operator family.

I like your thought that maybe this functionality could be cast
as some sort of filter in the \dt command group (with a syntax that
would allow for other sorts of filters too).  I don't have concrete
ideas about how to write that though.

            regards, tom lane