Request to modify view_table_usage to include materialized views
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
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 wrote: > Jonathan Lemig 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 >
Re: Name of main process differs between servers (postmaster vs postgres)
> > Best guess, one of the servers has upgrades from previous versions > applied to it while the other started with a newer release. The upgraded > server didn't mess with a pre-existing launch script that was using > postmaster while the new one made use of the likely newer script that calls > postgres. > I bet it does have something to do with that. I had to migrate one of our Postgres server from Fedora Core 24/Postgres 9.5 to CentOS 7/Postgres 9.6. I'm new to Postgres and was playing around with different methods and settled on using pglogical. I refined (that's being generous) the migration process and migrated DEV and then PROD. Perhaps I should have had our SA re-do the VMs after the refinements, but before doing the final migrations, to make sure everything was apples-to-apples. Thanks! Jon On Tue, Jun 26, 2018 at 5:04 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tue, Jun 26, 2018 at 2:51 PM, Jonathan Lemig wrote: > >> From what I can tell, things are running fine on both servers, I guess I >> just don't like there is a difference/inconsistency. Can anyone please >> explain this? >> >> > Best guess, one of the servers has upgrades from previous versions > applied to it while the other started with a newer release. The upgraded > server didn't mess with a pre-existing launch script that was using > postmaster while the new one made use of the likely newer script that calls > postgres. > > Dave > >
Name of main process differs between servers (postmaster vs postgres)
Hi, I noticed on two of my postgres servers, one has "postmaster" for the main process, and the other has "postgres". My question is - why is this? For example: Server1: postgres 909 1 0 May08 ?00:03:55 /usr/pgsql-9.6/bin/postmaster -D /var/lib/pgsql/9.6/data/ Server2: postgres 4804 1 0 May01 ?00:05:08 /usr/pgsql-9.6/bin/postgres -D /var/lib/pgsql/9.6/data Both servers are running CentOS Linux release 7.4.1708 for the OS and PostgreSQL 9.6.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit. The only differences I've been able to find so far are: 1) When doing a ps, server1 shows a trailing slash for the $PGDATA directory whereas server2 doesn't have the trailing slash. I echoed $PGDATA on both servers and they're both the same (no trailing slash). 2) Server2 has an additional package installed -- postgresql96-contrib-9.6.8-1PGDG.rhel7.x86_64. Aside from that, both servers have the same packages installed: postgresql96-9.6.8-1PGDG.rhel7.x86_64 postgresql96-server-9.6.8-1PGDG.rhel7.x86_64 postgresql96-libs-9.6.8-1PGDG.rhel7.x86_64 postgresql96-pglogical-2.2.0-1.el7.x86_64 Looking at the documentation, it seems that postmaster is a deprecated alias for the postgres process so while they differ in name, they're the same in spirit? >From what I can tell, things are running fine on both servers, I guess I just don't like there is a difference/inconsistency. Can anyone please explain this? Thanks! Jon
