Hello, I think this is the correct mail list for feature/modification requests. If not please let me know which mail list I should use.
Would it be possible to modify the information_schema.view_table_usage (VTU) to include materialized views? ( https://www.postgresql.org/docs/current/infoschema-view-table-usage.html) Currently when querying VTU, if the view you're interested in queries a materialized view, then it doesn't show up in VTU. For example, I was trying to determine which tables/views made up a particular view: --View is present in pg_views drps=> select schemaname, viewname, viewowner drps-> from pg_views drps-> where viewname = 'platform_version_v'; schemaname | viewname | viewowner ------------+--------------------+----------- event | platform_version_v | drps -- Check view_table_usage for objects that are queried by the platform_version_v view, but it doesn't find any: drps=> select * drps=> from information_schema.view_table_usage drps=> where view_name = 'platform_version_v'; view_catalog | view_schema | view_name | table_catalog | table_schema | table_name --------------+-------------+-----------+---------------+--------------+------------ (0 rows) I looked at the pg_views.definition column for platform_version_v, and it is querying a materialized view. The source code for information_schema.view_table_usage view is at https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/catalog/information_schema.sql;h=18725a02d1fb6ffda3d218033b972a0ff23aac3b;hb=HEAD#l2605 If I change lines 2605 and 2616 to: 2605: AND v.relkind in ('v','m') 2616: AND t.relkind IN ('r', 'v', 'f', 'p','m') and compile the modified version of VTU in my test schema, then I see the MV that is used in the query of platform_version_v view: drps=> select * drps=> from test.view_table_usage drps=> where view_name = 'platform_version_v'; view_catalog | view_schema | view_name | table_catalog | table_schema | table_name --------------+-------------+--------------------+---------------+--------------+--------------------- drps | event | platform_version_v | drps | event | platform_version_mv My method of changing those 2 lines of code may not be the best or correct solution, it's just to illustrate what I'm looking for. Thanks! Jon
