Request to modify view_table_usage to include materialized views

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

Responses

Browse pgsql-hackers by date

  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