ActiveRecord迁移未填充Postgres实例化视图 [英] ActiveRecord migration not populating a Postgres materialized view

问题描述

我有一个通过迁移创建的材料视图。

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

注意：我混淆了SELECT语句，只相信它可以工作。

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

这里要注意的重要部分是，我已明确调用 WITH DATA ，因此该视图应立即填充并可以扫描。

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) ===============

稍后在 db：refresh 中，我们看到以下内容

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.

嗯，什么？我声明了有数据。我还进行了另一个连续迁移，该迁移明确地在视图上调用了 REFRESH MATERIALIZED VIEW 命令。

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

无济于事，为了完成rake db：refresh任务，我必须进入并手动刷新视图。

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

有趣的注释，在structure.sql文件中，其显示为没有数据创建的

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;

我相信这是真正的问题，但我不知道解决方法。它也令人困惑，因为即使创建时没有数据，后续的REFRESH MATERIALIZED VIEW也应填充它并将其标记为可扫描。

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.

Postgres或AR是否存在某些问题

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

推荐答案

我知道这个问题几乎被问到了2几年前，但也许我的回答对其他人会有用。

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.

这篇关于ActiveRecord迁移未填充Postgres实例化视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！