At Fri, 26 May 2023 16:49:35 +0900 (JST), Kyotaro Horiguchi <horikyota.ntt@gmail.com> wrote in
> present in the list. Anyway, I don't find a simple way to do that for
> now.
For 12 or later, pg_partition_ancestors() is available. Thus something
like the following query would work.
+WITH inh_children AS
+ (SELECT tc.relid::name, t.nspname FROM
+ (SELECT c.relname, ns.nspname FROM pg_catalog.pg_class c
+ JOIN pg_catalog.pg_namespace ns ON c.relnamespace OPERATOR(pg_catalog.=) ns.oid
+ LEFT JOIN pg_catalog.pg_class t ON c.reltoastrelid OPERATOR(pg_catalog.=) t.oid
+ WHERE c.relkind OPERATOR(pg_catalog.=) 'p') t,
+ LATERAL
+ (SELECT tt.relid, t.nspname
+ FROM pg_catalog.pg_partition_tree(t.relname::text) tt WHERE isleaf) tc)
SELECT c.relname, ns.nspname FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace ns ON c.relnamespace OPERATOR(pg_catalog.=) ns.oid
LEFT JOIN pg_catalog.pg_class t ON c.reltoastrelid OPERATOR(pg_catalog.=) t.oid
WHERE c.relkind OPERATOR(pg_catalog.=) ANY (array['r', 'm', 'p'])
+ AND NOT EXISTS (SELECT * FROM inh_children i WHERE i.relid = c.relname AND i.nspname = ns.nspname)
ORDER BY c.relpages DESC;
The lines prefixed by a '+' removes other than the topmost-parents of trees.
For 11, we need to do the same withouth using the function.
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center