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?

Lop

Have you tried creating and working with your view without ActiveRecord and Rails in the way? – Mielke

did you fix this issue? I have the same thing – Lambdacism

I dont think so, I think I would have answered the question if I did. – Lop

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.

Antisepsis

That gem looks great. Thanks for the link. However, I don't see what it accomplishes besides writing out a migration like the one OP created. Any idea why scenic works and doing it manually doesn't? Thanks for any insight. – Samualsamuel

For me scenic lib does the same. It adds with NO DATA for no reason – Lambdacism

Answer 2

I believe you'll need to setup your own strategy for calling a refresh on materialized views. The scenic gem has a method for refreshing that's worth looking into.

You could look at before hooks (for specs/tests) or rake before hook tasks to make this refresh a little more painless (chaining your refresh automatically after you call migrate, for example)

Interestingly, Postgres dumps (via pg_dump command) the materialized views created WITH DATA as WITH NO DATA as well. I'm not sure the root cause but I'm assuming this works as designed, based around the purpose of running a dump.

However, pg_dump DOES also include a REFRESH command in the dumped file (rails does not), which is why you need to come up with your own strategy for refreshing the materialized view.

After some further digging I noticed the --schema-only flag passed to a pg_dump command will not dump the REFRESH MATERIALIZED VIEW command. The rails db:structure:dump command for Postgres appears to use this command (as -s). That might be enough to help you identify a way to change the behavior but I think the other answer recommending use of scenic, coupled with some rake tasks to automatically refresh might be your best bet.

Reference: https://github.com/rails/rails/blob/8f2caec401c8e97d9eb1ea84d8263911c50e1ed6/activerecord/lib/active_record/tasks/postgresql_database_tasks.rb#L64

Bongbongo