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]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".
drps=> show search_path;
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)
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:
drps=> select * from information_schema.view_table_usage where view_name =
'tv_lineup_v';
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:
Show quoted text
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].
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:
Show quoted text
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].--
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:
Show quoted text
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].--
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]https://www.postgresql.org/docs/15/sql-creatematerializedview.html, see Compatibility (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]https://www.postgresql.org/docs/9.3/release-9-3.html#AEN119452 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].--
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:
Show quoted text
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
Hello,
I'd like to request the documentation be updated to add a note that the
information_schema.view_table_usage view won't include views that query
materialized views.
Thanks!
Jon
---------- Forwarded message ---------
From: Jonathan Lemig <jtlemig@gmail.com>
Date: Fri, Dec 2, 2022 at 4:14 PM
Subject: Re: Views "missing" from information_schema.view_table_usage
To: Erik Wienhold <ewie@ewie.name>
Cc: <pgsql-general@lists.postgresql.org>
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:
Show quoted text
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].--
Erik