Postgres Pro
Downloads
Home   >   mailing lists

Thread: Request to modify view_table_usage to include materialized views

Request to modify view_table_usage to include materialized views

From
Jonathan Lemig
Date:
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.


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


Re: Request to modify view_table_usage to include materialized views

From
Tom Lane
Date:
Jonathan Lemig <jtlemig@gmail.com> writes:
> Would it be possible to modify the information_schema.view_table_usage
> (VTU) to include materialized views?

Is it physically possible?  Sure, it'd just take adjustment of some
relkind checks.

However, it's against project policy.  We consider that because the
information_schema views are defined by the SQL standard, they should
only show standardized properties of standardized objects.  If the
standard ever gains materialized views, we'd adjust those views to
show them.  In the meantime, they aren't there.

It would make little sense in any case to adjust only this one view.
But if we were to revisit that policy, there are a lot of corner
cases that would have to be thought through --- things that almost
fit into the views, or that might appear in a very misleading way,
etc.

            regards, tom lane

Re: Request to modify view_table_usage to include materialized views

From
Jonathan Lemig
Date:
Hey Tom,

Thanks for the info.  I'll submit a document change request instead. 

Thanks!

Jon

On Mon, Dec 5, 2022 at 11:53 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Jonathan Lemig <jtlemig@gmail.com> writes:
> Would it be possible to modify the information_schema.view_table_usage
> (VTU) to include materialized views?

Is it physically possible?  Sure, it'd just take adjustment of some
relkind checks.

However, it's against project policy.  We consider that because the
information_schema views are defined by the SQL standard, they should
only show standardized properties of standardized objects.  If the
standard ever gains materialized views, we'd adjust those views to
show them.  In the meantime, they aren't there.

It would make little sense in any case to adjust only this one view.
But if we were to revisit that policy, there are a lot of corner
cases that would have to be thought through --- things that almost
fit into the views, or that might appear in a very misleading way,
etc.

                        regards, tom lane