Обсуждение: Views "missing" from information_schema.view_table_usage
Hi,
I am running Postgres 12.9 on FreeBSD.
I had a developer ask me the best way to determine a view's dependencies (i.e. which tables/views are used in a view's query). In Oracle, I would use the ALL_DEPENDENCIES dictionary view. I found a view called information_schema.view_table_usage (VTU) in the Postgres docs. This does the trick. However, the view the developer is interested in is not listed when I query the VTU view.
Looking at the documentation for the VTU view, it does state - "A table is only included if that table is owned by a currently enabled role."
I am able to look at the view's query by using "\d+ schema_name.view_name" and can see which objects are associated with the view. I have confirmed I am logged in as the role that owns all associated objects (i.e. the view, plus all objects that are part of the view's SELECT.).
If I run the following:
select viewname from pg_views where schemaname = 'event' order by 1;
I see there are 7 views in the event schema. However, when I run this:
select distinct view_name from information_schema.view_table_usage where view_schema = 'event' order by 1;
There are only 6 views listed. And the one that I'm interested in is, of course, not listed.
I've also tried playing around with the search_path (e.g. making sure it includes the schemas of all objects associated with the view), but still no luck. Every time I query VTU, it only shows 6 of the 7 views in the event schema.
Has anybody ever encountered this, and if so, did you find a resolution? Or perhaps there other limitations with the VTU that I'm unaware of?
Thanks!
Jon
On Fri, Dec 2, 2022 at 1:52 PM Jonathan Lemig <jtlemig@gmail.com> wrote:
Has anybody ever encountered this, and if so, did you find a resolution? Or perhaps there other limitations with the VTU that I'm unaware of?
Well, the query itself is available in the source code if you want to experiment on your own. Otherwise, I suggest you produce a self-contained example that fails to produce one or more rows in view_table_usage that you believe should be present. Whether it is a bug report or trying to point out what you are misunderstanding, diagnosing this situation with the information you've provided is not a productive activity.
David J.
> On 02/12/2022 21:51 CET Jonathan Lemig <jtlemig@gmail.com> wrote: > > Has anybody ever encountered this, and if so, did you find a resolution? > Or perhaps there other limitations with the VTU that I'm unaware of? Is the one view you cannot find in view_table_usage a materialized view? Because those are not covered by view_table_usage[1]. [1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/catalog/information_schema.sql;h=18725a02d1fb6ffda3d218033b972a0ff23aac3b;hb=HEAD#l2605 -- Erik
Hi David,
This is specifically what I'm seeing:
drps=> \c
psql (12.11, server 12.9)
You are now connected to database "drps" as user "drps".
psql (12.11, server 12.9)
You are now connected to database "drps" as user "drps".
drps=> show search_path;
search_path
------------------------
"$user", public, event
(1 row)
search_path
------------------------
"$user", public, event
(1 row)
drps=> select viewowner, schemaname, viewname from pg_views where viewname = 'platform_version_v';
viewowner | schemaname | viewname
-----------+------------+--------------------
drps | event | platform_version_v
(1 row)
drps=> select * from information_schema.view_table_usage where view_name = 'platform_version_v';
view_catalog | view_schema | view_name | table_catalog | table_schema | table_name
--------------+-------------+-----------+---------------+--------------+------------
(0 rows)
viewowner | schemaname | viewname
-----------+------------+--------------------
drps | event | platform_version_v
(1 row)
drps=> select * from information_schema.view_table_usage where view_name = 'platform_version_v';
view_catalog | view_schema | view_name | table_catalog | table_schema | table_name
--------------+-------------+-----------+---------------+--------------+------------
(0 rows)
The event.platform_version_v doesn't show up when querying the view_table_usage view. All other views in the event schema are present when querying that view. For example:
view_catalog | view_schema | view_name | table_catalog | table_schema | table_name
--------------+-------------+-------------+---------------+--------------+------------
drps | event | tv_lineup_v | drps | event | tv_lineup
I'll take a look at the VTU view's DDL and see what else I can find.
Thanks!
Jon
On Fri, Dec 2, 2022 at 3:15 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Fri, Dec 2, 2022 at 1:52 PM Jonathan Lemig <jtlemig@gmail.com> wrote:Has anybody ever encountered this, and if so, did you find a resolution? Or perhaps there other limitations with the VTU that I'm unaware of?Well, the query itself is available in the source code if you want to experiment on your own. Otherwise, I suggest you produce a self-contained example that fails to produce one or more rows in view_table_usage that you believe should be present. Whether it is a bug report or trying to point out what you are misunderstanding, diagnosing this situation with the information you've provided is not a productive activity.David J.
> On 02/12/2022 22:33 CET Erik Wienhold <ewie@ewie.name> wrote: > > > > On 02/12/2022 21:51 CET Jonathan Lemig <jtlemig@gmail.com> wrote: > > > > Has anybody ever encountered this, and if so, did you find a resolution? > > Or perhaps there other limitations with the VTU that I'm unaware of? > > Is the one view you cannot find in view_table_usage a materialized view? > Because those are not covered by view_table_usage[1]. > > [1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/catalog/information_schema.sql;h=18725a02d1fb6ffda3d218033b972a0ff23aac3b;hb=HEAD#l2605 Never mind. I forgot that you wrote that it appears in pg_views. So it's a plain view. -- Erik
> On 02/12/2022 22:55 CET Jonathan Lemig <jtlemig@gmail.com> wrote:
>
> drps=> select viewowner, schemaname, viewname from pg_views where viewname = 'platform_version_v';
> viewowner | schemaname | viewname
> -----------+------------+--------------------
> drps | event | platform_version_v
> (1 row)
>
> drps=> select * from information_schema.view_table_usage where view_name = 'platform_version_v';
> view_catalog | view_schema | view_name | table_catalog | table_schema | table_name
> --------------+-------------+-----------+---------------+--------------+------------
> (0 rows)
>
> The event.platform_version_v doesn't show up when querying the view_table_usage view.
Does platform_version_v reference any tables? That view name suggests that it
provides some version info, e.g. with a definition like:
create view event.platform_version_v as select '1.0.0' as version;
In that case it won't appear in view_table_usage.
--
Erik
Hi Erik - sorry I missed your reply when I replied to David's. That is indeed the issue. The object that the view is querying is a materialized view.
Thanks for the link.
Jon
On Fri, Dec 2, 2022 at 3:33 PM Erik Wienhold <ewie@ewie.name> wrote:
> On 02/12/2022 21:51 CET Jonathan Lemig <jtlemig@gmail.com> wrote:
>
> Has anybody ever encountered this, and if so, did you find a resolution?
> Or perhaps there other limitations with the VTU that I'm unaware of?
Is the one view you cannot find in view_table_usage a materialized view?
Because those are not covered by view_table_usage[1].
[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/catalog/information_schema.sql;h=18725a02d1fb6ffda3d218033b972a0ff23aac3b;hb=HEAD#l2605
--
Erik
It probably wouldn't hurt to have that added to the documentation. I'll post a message to pgsql-docs. Thanks again!
Jon
On Fri, Dec 2, 2022 at 4:14 PM Jonathan Lemig <jtlemig@gmail.com> wrote:
Hi Erik - sorry I missed your reply when I replied to David's. That is indeed the issue. The object that the view is querying is a materialized view.Thanks for the link.JonOn Fri, Dec 2, 2022 at 3:33 PM Erik Wienhold <ewie@ewie.name> wrote:> On 02/12/2022 21:51 CET Jonathan Lemig <jtlemig@gmail.com> wrote:
>
> Has anybody ever encountered this, and if so, did you find a resolution?
> Or perhaps there other limitations with the VTU that I'm unaware of?
Is the one view you cannot find in view_table_usage a materialized view?
Because those are not covered by view_table_usage[1].
[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/catalog/information_schema.sql;h=18725a02d1fb6ffda3d218033b972a0ff23aac3b;hb=HEAD#l2605
--
Erik
> On 02/12/2022 23:22 CET Jonathan Lemig <jtlemig@gmail.com> wrote: > > It probably wouldn't hurt to have that added to the documentation. I'll post > a message to pgsql-docs. Thanks again! > > Jon Good idea! Could it be a bug? Materialized views are a Postgres extension[1] (I always thought they are standard.) But I'd expect them to be included when talking about "views". Maybe they are not included because they are considered being closer to physical tables[2] than views. Yet their dependencies would justify inclusion in view_table_usage. [1] https://www.postgresql.org/docs/15/sql-creatematerializedview.html, see Compatibility [2] https://www.postgresql.org/docs/9.3/release-9-3.html#AEN119452 > On Fri, Dec 2, 2022 at 4:14 PM Jonathan Lemig <jtlemig@gmail.com> wrote: > > Hi Erik - sorry I missed your reply when I replied to David's. That is > > indeed the issue. The object that the view is querying is a materialized > > view. > > > > Thanks for the link. > > > > Jon > > > > > > On Fri, Dec 2, 2022 at 3:33 PM Erik Wienhold <ewie@ewie.name> wrote: > > > > On 02/12/2022 21:51 CET Jonathan Lemig <jtlemig@gmail.com> wrote: > > > > > > > > Has anybody ever encountered this, and if so, did you find a resolution? > > > > Or perhaps there other limitations with the VTU that I'm unaware of? > > > > > > Is the one view you cannot find in view_table_usage a materialized view? > > > Because those are not covered by view_table_usage[1]. > > > > > > [1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/catalog/information_schema.sql;h=18725a02d1fb6ffda3d218033b972a0ff23aac3b;hb=HEAD#l2605 > > > > > > -- > > > Erik -- Erik
Erik Wienhold <ewie@ewie.name> writes:
> Could it be a bug? Materialized views are a Postgres extension[1] (I always
> thought they are standard.) But I'd expect them to be included when talking
> about "views". Maybe they are not included because they are considered being
> closer to physical tables[2] than views. Yet their dependencies would justify
> inclusion in view_table_usage.
The reasoning is that the information_schema views are defined by the
SQL standard and therefore should only show content that matches the
standard. Thus, they ignore PG-invented objects like matviews and
sequences. Some other projects adopt more liberal views about
what should be shown in those views, but that one is our policy.
regards, tom lane
Oh ok. Not to cause confusion, but after I suggested I would request an update to the docs, I thought maybe it would be better to ask if the VTU's code could be modified to include MVs. So I sent a request to pgsql-hackers (I think that's the list to use for feature requests). But from what you're saying, Tom, that may be a fruitless endeavor. If they reply back "nope", then I'll submit a request to have the documentation updated.
Thanks for everyone's contributions!
Jon
On Fri, Dec 2, 2022 at 5:11 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Erik Wienhold <ewie@ewie.name> writes:
> Could it be a bug? Materialized views are a Postgres extension[1] (I always
> thought they are standard.) But I'd expect them to be included when talking
> about "views". Maybe they are not included because they are considered being
> closer to physical tables[2] than views. Yet their dependencies would justify
> inclusion in view_table_usage.
The reasoning is that the information_schema views are defined by the
SQL standard and therefore should only show content that matches the
standard. Thus, they ignore PG-invented objects like matviews and
sequences. Some other projects adopt more liberal views about
what should be shown in those views, but that one is our policy.
regards, tom lane