3

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.

3

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;
  • Very fast and very helpfull. Thanks for sharing! – alfonx May 19 '15 at 19:21
  • matviews_with_keys is listing all mat. views that have any index. For CONCURRENTLY to work, it must be UNIQUE indexes. (I will see if i get this improved...) – alfonx Jun 8 '15 at 21:17
  • I eddited your code, adding a "and indisunique" to the WHERE. Hope thats OK. Now only UNIQUE indexes that can be used for REFRESH CONCURRENTLY wil be counted. – alfonx Jun 8 '15 at 21:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.