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
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) |
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 |