Views "missing" from information_schema.view_table_usage

From: Jonathan Lemig <jtlemig(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Views "missing" from information_schema.view_table_usage
Date: 2022-12-02 20:51:50
Message-ID: CABR8q__QVKFzaUdQQsyU2wd2TZX1tdv7Q8M5myQ8L=BDDAtjLQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs pgsql-general

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

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message David G. Johnston 2022-12-02 21:14:47 Re: Views "missing" from information_schema.view_table_usage
Previous Message Daniel Gustafsson 2022-12-02 12:56:35 Re: First Person being used (only occurs in 2 other places in all of documentation)

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2022-12-02 21:14:47 Re: Views "missing" from information_schema.view_table_usage
Previous Message Christophe Pettus 2022-12-02 16:05:43 Re: Stored procedure code no longer stored in v14 and v15, changed behaviour