Request to modify view_table_usage to include materialized views
| От | Jonathan Lemig |
|---|---|
| Тема | Request to modify view_table_usage to include materialized views |
| Дата | |
| Msg-id | CABR8q__emvE2af2YYf1n9R-CrEy7EW1YyFWuJomUaOsqkE0k5w@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: Request to modify view_table_usage to include materialized views
|
| Список | pgsql-hackers |
Hello,
drps-> from pg_views
drps-> where viewname = 'platform_version_v';
schemaname | viewname | viewowner
------------+--------------------+-----------
event | platform_version_v | drps
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.
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, viewownerdrps-> 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)
--------------+-------------+-----------+---------------+--------------+------------
(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
--------------+-------------+--------------------+---------------+--------------+---------------------
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
В списке pgsql-hackers по дате отправления: