Show properly partitioned tables

[apkc-dvsts]

Currently all partitions are shown as separate tables. Would be great if we could see them as a tree under the parent table. The tree of a table could be:

• Table
  • Fields
    • Field1
    • Field2
    • Field3
  • Partitions
    • Partition1
    • Partition2
    • Partition3

[Borvik]

I'm not certain about actually showing the partitions, but I get the weirdness/frustration of having the table show up multiple times.

While I never looked at the partitions, I did at one point look at including more data about the table, but rejected it because I didn't want yet another tree node in the way of viewing the fields of the table.

I'm potentially optimizing the query that gets the tables, that maybe will help with the deduplication, but I don't actually have multi-partition tables to test against.

Would you be willing to run a couple of queries to confirm (may have to change public to your namespace)? Don't need the actual results, just whether it helped.

Query 1 (old query) - should show separate tables per partition:

SELECT
  tablename as name,
  true as is_table,
  schemaname AS schema
FROM pg_tables
WHERE 
  schemaname = 'public'
  AND has_table_privilege(quote_ident(schemaname) || '.' || quote_ident(tablename), 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER') = true

Query 2 (new query):

SELECT
  c.relname as "name",
  true as is_table,
  n.nspname as "schema"
FROM
  pg_catalog.pg_class c
  JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE
  c.relkind = 'r'
  AND n.nspname = 'public'
  AND has_table_privilege(quote_ident(n.nspname) || '.' || quote_ident(c.relname), 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER') = true
ORDER BY
  c.relname;

[robertpba]

Hi, I'm late to the party (user of your great extension since this morning) but am happy to help. We have several large tables (>1bn rows) that are partitioned.

I tried both queries. Summary of results: Query 1: shows all partitions (in my case, 109 partitions for a specific table) Query 2: shows most partitions (73 of the above 109 partitions for the same table)

Reason: the table in question is partitioned using both range and list partitioning (in a sense, a 2-level partitioning). The structure is a tree, with the whole table as root, the range partitions as child nodes, and the list partitions as grandchild nodes. Only the partitions at the first partitioning level, the child nodes, are filtered out by Query 2, but partitions at the second partitioning level, the grandchild nodes, remain. Worst of all, the root of the tree is also filtered out as its relkind is also 'p'. However, it has relispartition set to FALSE, thus this can be used to retrieve the root of the partitioned table.

Solution: include also tables with relkind = 'p' and restrict to relispartition IS FALSE.

This modified query should work as desired:

SELECT
  c.relname as "name",
  true as is_table,
  n.nspname as "schema"
FROM
  pg_catalog.pg_class c
  JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE
  c.relkind IN ('p', 'r')
  AND c.relispartition IS FALSE
  AND n.nspname = 'public'
  AND has_table_privilege(quote_ident(n.nspname) || '.' || quote_ident(c.relname), 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER') = true
ORDER BY
  c.relname;

Having said that, being able to drill down into a partitioned table by unfolding the tree would be really helpful when dealing with lots of partitions, as in our case.

[Borvik]

Thanks for that - I'll see about getting that query into the current version to get the tree to better behave itself.

As for being able to drill down to see partitions, I'll consider that for the next major version of the extension.