Request to modify view_table_usage to include materialized views

Started by Jonathan Lemigabout 3 years ago3 messages
#1Jonathan Lemig
jtlemig@gmail.com

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Jonathan Lemig (#1)
Re: Request to modify view_table_usage to include materialized views

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

#3Jonathan Lemig
jtlemig@gmail.com
In reply to: Tom Lane (#2)
Re: Request to modify view_table_usage to include materialized views

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:

Show quoted text

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