From: | Jonathan Lemig <jtlemig(at)gmail(dot)com> |
---|---|
To: | Pg Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Request to modify view_table_usage to include materialized views |
Date: | 2022-12-05 17:39:01 |
Message-ID: | CABR8q__emvE2af2YYf1n9R-CrEy7EW1YyFWuJomUaOsqkE0k5w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2022-12-05 17:41:19 | Re: Failed Assert while pgstat_unlink_relation |
Previous Message | Joe Conway | 2022-12-05 17:38:45 | Re: Error-safe user functions |