ActiveRecord migration not populating a Postgres materialized view

Question

I have a MATERIALIZED VIEW that is created via a migration.

class MyView < ActiveRecord::Migration
  def up
    ActiveRecord::Base.connection.execute <<-SQL
    CREATE MATERIALIZED VIEW my_view AS (
      SELECT DISTINCT something, something_else, other.thing as real_thing, thing.some_id
          FROM some_table
          JOIN another_table on another_table.id = something
          JOIN one_more_table on some_table.id = other_id
          ORDER BY order_column)
      WITH DATA;
    SQL

    add_index :table, [:key_part_one, :key_part_two]
  end

  ...
end

Note: I've obfuscated the SELECT statement, just trust me that it works.

The important part to note here is that I've explicitly called WITH DATA, so the view should be populated and scannable right away.

This is not happening. The migration runs, shown below

==  MyView: migrating ========================
==  MyView: migrated (0.0763s) ===============

Later on in the db:refresh we see the following

Reindexing Something...
Reindex queued
Reindexing Another...
Reindex queued
Reindexing SomeOtherThing...
Reindex queued
Reindexing One::OtherThing...
Reindex queued
Reindexing MyViewModel...
rake aborted!
ActiveRecord::StatementInvalid: PG::ObjectNotInPrerequisiteState: ERROR:  materialized view "my_view" has not been populated
HINT:  Use the REFRESH MATERIALIZED VIEW command.

Um, what? I declared WITH DATA. I also have another consecutive migration that explicitly calls the REFRESH MATERIALIZED VIEW command on the view.

To no avail, in order to get the rake db:refresh task to complete, I have to go in and manually refresh the view.

Interesting note, in the structure.sql file, its shown as being created WITH NO DATA

CREATE MATERIALIZED VIEW my_view AS (
  SELECT DISTINCT something, something_else, other.thing as real_thing, thing.some_id
      FROM some_table
      JOIN another_table on another_table.id = something
      JOIN one_more_table on some_table.id = other_id
      ORDER BY order_column)
  WITH NO DATA;

I believe this is the real issue but I'm unaware of a fix/workaround. Its also confusing because even if it was created WITH NO DATA, the subsequent REFRESH MATERIALIZED VIEW should populate it and mark it as scannable.

Is there some issue with Postgres or AR that I'm not aware of that is preventing me from populating this materialized view?

Answer 1

I know the question was asked almost 2 years ago but maybe my answer will be useful for someone else.

Try using scenic gem. I have recently written a blog post about it.