Re: [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