Postgresql 9.4 allows to REFRESH materialized views CONCURRENTLY, if they have a UNIQUE index.
I am looking fo a SQL-query that lists all materialized views that DO NOT have such a UNIQUE or PRIMARY KEY index.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityPostgresql 9.4 allows to REFRESH materialized views CONCURRENTLY, if they have a UNIQUE index.
I am looking fo a SQL-query that lists all materialized views that DO NOT have such a UNIQUE or PRIMARY KEY index.
Tip: use psql's -E flag to get it to show you how what queries it is issuing for e.g. its \dm
meta-command, and adjust from there.
Here's a rough and not at all cleaned-up query, but it should do the trick...
WITH matviews_with_unqiue_keys AS (
SELECT c.oid, c.relname, c2.relname AS idx_name
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
LEFT JOIN pg_catalog.pg_constraint con ON (
conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u'))
WHERE
c.relkind = 'm' AND
c.oid = i.indrelid AND i.indexrelid = c2.oid AND indisunique
)
SELECT c.relname
FROM pg_class c
WHERE c.relkind = 'm'
EXCEPT
SELECT mwk.relname
FROM matviews_with_unique_keys as mwk;