Materialized views in postgres deployment do not update

[buremba]

What's the error that you see?

[evelant]

When viewing a report

ERROR: materialized view "$materialized_segment_leavers" has not been populated Hint: Use the REFRESH MATERIALIZED VIEW command.

If I open the report editor and click the "i" next to the materialized view the popup says

cache eviction duration PT5M
last updated at 1970-01-01T00:00:00Z

[buremba]

Are you using the latest version in master?

[evelant]

I was using an outdated version. I just deployed the latest code from master and now have a different issue

Error while updating materialized table '(SELECT * from taskhero."$materialized_segment_leavers" UNION ALL SELECT "id" FROM (SELECT * FROM taskhero."_users" WHERE "_time" > to_timestamp(1495130635)) data WHERE (("last_active_at" - interval '7 days') < "signup_date") ) data': ERROR: column "$server_time" does not exist Position: 112

[buremba]

Yes, now we require all collections to have $server_time field, the column is automatically added for new collections but we need to add it to existing collections. Could you please run the following query on your Rakam database? Note that the snippet adds $server_time column to all tables. if you're using the same database for different applications, it might cause some problems.

do $$
declare
    selectrow record;
begin
for selectrow in
    select 
      'ALTER TABLE "'|| T.schemaname ||'"."'|| T.mytable || '" add column "$server_time" timestamp without time zone default (current_timestamp at time zone ''UTC'')' as script 
   from 
      ( 
        select schemaname, tablename as mytable from pg_tables t where schemaname  != 'public' and schemaname != 'pg_catalog' 
and (select count(*) from information_schema.columns where table_schema = t.schemaname and table_name = t.tablename and column_name = '$server_time')  = 0 
and tablename not like '$materialized%' and tablename != '_users' and schemaname != 'information_schema'
      ) t
loop
execute selectrow.script;

end loop;
end;
$$;

[evelant]

OK I ran the script. I also had to add $server_time to my '_users' table.

Now I am still having different issues

Error while updating materialized table '(SELECT * from taskhero."$materialized_segment_leavers" UNION ALL SELECT "id" FROM (SELECT * FROM taskhero."_users" WHERE "_time" > to_timestamp(1495132433)) data WHERE (("last_active_at" - interval '7 days') < "signup_date") ) data': ERROR: cannot change materialized view "$materialized_segment_leavers"

on some of my segments, on others

ERROR: relation "$materialized_segment_takers" does not exist Position: 178

When I can clearly see that the materialized view exists in the database. I have restarted my rakam instance and this still occurs.