Rails does not populate Postgres materialized view by default

Question

I have following migration:

  execute <<-SQL
    CREATE TABLE test_table(
      name char(20)
    );
    INSERT INTO test_table(name) values ('test name');

    CREATE MATERIALIZED VIEW test AS
      SELECT * from test_table
    WITH DATA;
  SQL

Note that I add "WITH DATA". This does not populate the data (as I get "materialized view has not been populated" error when I try to do concurrent refresh on the view) and adds "WITH NO DATA" in structure.sql:

CREATE MATERIALIZED VIEW public.test AS
 SELECT test_table.name
   FROM public.test_table
  WITH NO DATA;

What am I doing wrong? Postgres documentation about materialized views says The query is executed and used to populate the view at the time the command is issued (unless WITH NO DATA is used) So even without specifying "WITH DATA" it should generate "WITH DATA" by default but I get "WITH NO DATA" instead. When I use scenic library it does the same...

My issue seems to be similar to ActiveRecord migration not populating a Postgres materialized view

EDIT I learnt that the data is actually populated on development environment (although it sets WITH NO DATA in structure.sql). The problem is in test environment, where it does not populate the data sometimes. Still investigating why... But structure.sql is definitely not correct, unfortunately.

Would it make sense to have anything else but `WITH NO DATA` in the `structure.sql`? It's supposed to represent database schema without data considerations, loading the structure would not add any data to the view anyway. You may want to see [this](https://github.com/scenic-views/scenic/issues/133). — Marcin Kołodziej, Dec 01 '18 at 15:13
This occurs inside of pg, without rails. Example: Create a materialized view manually using `rails dbconsole` using your `WITH DATA` option, Then dump with `pg_dump --format=p --file=test.sql `, and `rails db:migrate` and you'll note both your test.sql & structure.sql files specify `WITH NO DATA` If you include `REFRESH MATERIALIZED VIEW ` as a separate migration (or even in the same migration) does that solve your problem? — Jay Dorsey, Dec 02 '18 at 04:55
Scratch the `REFRESH` idea, that actually won't work. It doesn't get propagated to the structure.sql file, so it only gets run 1x (when you run migrations first time). This would fail for most rails specs because the db structure is loaded with `rails db:load_structure` so you'd have to manually call your REFRESH via a rake task — Jay Dorsey, Dec 02 '18 at 05:06
I actually need it in specs, so I just called simple refresh before everything and it helped. But still no idea why it gives structure with no data. — Kote, Dec 02 '18 at 11:17

score 1 · Answer 1 · answered May 13 '20 at 17:42

I hope this isn't eighteen months too late to be useful, but here's how I solved this.

Assuming you're using the (highly recommended!) scenic gem,

Suppose your materialized view-backed model looks like this. I've modified the refresh method slightly to take concurrently as an argument.

class Foo < ApplicationRecord
  # if you're using the excellent "scenic" gem
  def self.refresh(concurrently:)
    Scenic.database.refresh_materialized_view(table_name, concurrently: concurrently, cascade: false)
  end

  # if you're not using the excellent "scenic" gem (why not?)
  # have not tested this but should work
  def self.refresh(concurrently:)
    concurrently_string = concurrently ? "CONCURRENTLY" : ""
    ActiveRecord::Base.connection.execute(
      "REFRESH MATERIALIZED VIEW #{table_name} #{concurrently_string} WITH DATA"
    )
  end

  private

  def readonly?
    true
  end
end

Then, in spec/rails_helper.rb, just do this for each of your materialized view-backed models:

config.before(:suite) do
  Foo.refresh(concurrently: false)
  Bar.refresh(concurrently: false)
  # etc.
end

Note that, in your individual tests, you still may need to call #refresh manually in some cases if you want them to reflect data you've inserted into the underlying tables.

Rails does not populate Postgres materialized view by default

1 Answers1