Josh Berkus <j...@agliodbs.com> wrote:
>> That I could probably do. Do you think they should have a separate
>> pg_stat_user_matviews table, etc., or do you think it would be
>> better to include them in with tables there?
>
> Well, ideally pg_matviews would have matview definitions, and
> pg_stat_matviews would have stats on matview usage and rows. But see
> what you can get done; I imagine we'll overhaul it for 9.4 anyway once
> we've had a chance to use the feature.
I agree on pg_matviews, but after looking over the existing views
and thinking about what I would use them for as a DBA, I'm inclined
to fold the backing tables for MVs into the _stat_ and _statio_
views -- especially since we already include the backing tables and
indexes for TOAST. There is a precident for including
implementation details at that level. The only difference from
TOAST, is that I include the heap and indexes for MVs in the _user_
views. I'm attaching the patch for just the system_views.sql file
for discussion before I go write docs for this part.
--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index c479c23..711a2ba 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -94,6 +94,18 @@ CREATE VIEW pg_tables AS
LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace)
WHERE C.relkind = 'r';
+CREATE VIEW pg_matviews AS
+ SELECT
+ N.nspname AS schemaname,
+ C.relname AS matviewname,
+ pg_get_userbyid(C.relowner) AS matviewowner,
+ T.spcname AS tablespace,
+ C.relhasindex AS hasindexes,
+ pg_get_viewdef(C.oid) AS definition
+ FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
+ LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace)
+ WHERE C.relkind = 'm';
+
CREATE VIEW pg_indexes AS
SELECT
N.nspname AS schemaname,
@@ -402,7 +414,7 @@ CREATE VIEW pg_stat_all_tables AS
FROM pg_class C LEFT JOIN
pg_index I ON C.oid = I.indrelid
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
- WHERE C.relkind IN ('r', 't')
+ WHERE C.relkind IN ('r', 't', 'm')
GROUP BY C.oid, N.nspname, C.relname;
CREATE VIEW pg_stat_xact_all_tables AS
@@ -422,7 +434,7 @@ CREATE VIEW pg_stat_xact_all_tables AS
FROM pg_class C LEFT JOIN
pg_index I ON C.oid = I.indrelid
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
- WHERE C.relkind IN ('r', 't')
+ WHERE C.relkind IN ('r', 't', 'm')
GROUP BY C.oid, N.nspname, C.relname;
CREATE VIEW pg_stat_sys_tables AS
@@ -467,7 +479,7 @@ CREATE VIEW pg_statio_all_tables AS
pg_class T ON C.reltoastrelid = T.oid LEFT JOIN
pg_class X ON T.reltoastidxid = X.oid
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
- WHERE C.relkind IN ('r', 't')
+ WHERE C.relkind IN ('r', 't', 'm')
GROUP BY C.oid, N.nspname, C.relname, T.oid, X.oid;
CREATE VIEW pg_statio_sys_tables AS
@@ -494,7 +506,7 @@ CREATE VIEW pg_stat_all_indexes AS
pg_index X ON C.oid = X.indrelid JOIN
pg_class I ON I.oid = X.indexrelid
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
- WHERE C.relkind IN ('r', 't');
+ WHERE C.relkind IN ('r', 't', 'm');
CREATE VIEW pg_stat_sys_indexes AS
SELECT * FROM pg_stat_all_indexes
@@ -520,7 +532,7 @@ CREATE VIEW pg_statio_all_indexes AS
pg_index X ON C.oid = X.indrelid JOIN
pg_class I ON I.oid = X.indexrelid
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
- WHERE C.relkind IN ('r', 't');
+ WHERE C.relkind IN ('r', 't', 'm');
CREATE VIEW pg_statio_sys_indexes AS
SELECT * FROM pg_statio_all_indexes
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
