Graphql-engine: materialised views are not listed in console

Created on 11 Jul 2018 · 21Comments · Source: hasura/graphql-engine

information_schema.tables from where we currently read untracked tables does not list materialized views. We should probably switch to something more Postgres specific

console intermediate high

Source

0x777

👍26

Most helpful comment

@eads as a workaround you can create a regular view as follows:

create view somename as select * from materialized_view;

You can now access data from the view somename which in turn fetches data from the materialized view.

0x777 on 3 Oct 2018

👍14

All 21 comments

pg_class probably have what you are looking for:

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'table' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','f','v','m')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

mterron on 18 Jul 2018

👍 to this PR. Materialized views are a powerful tool for data analysis and it's a bummer I can't access them from Hasura currently.

eads on 3 Oct 2018

@eads as a workaround you can create a regular view as follows:

create view somename as select * from materialized_view;

You can now access data from the view somename which in turn fetches data from the materialized view.

0x777 on 3 Oct 2018

👍14

Does materialized view automatically get updated when we mutate the related tables ?

revskill10 on 19 Oct 2018

Nope by default.

You have to call REFRESH VIEW
https://www.postgresql.org/docs/11/static/sql-refreshmaterializedview.html

We use triggers with REFRESH CONCURRENTLY in some scenarios and a "CRON" in others.

amesas on 30 Oct 2018

@amesas Could hasura automatically handle Refreshing view for us ?

revskill10 on 30 Oct 2018

👍5

@0x777 Thanks for the hack!
Would be great to have a proper solution though

pyankoff on 23 Dec 2018

It's not just showing untracked that doesn't work, even if you create the materialized view within Hasura SQL tab and click "Track" it doesn't show up. So I'll use the view wrapper trick.