我有一个MATERIALIZED VIEW
,是通过迁移创建的。
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语句,但请相信它是有效的。
这里需要注意的重要部分是,我明确调用了WITH DATA
,因此视图应该立即被填充和扫描。
但是这并没有发生。 迁移正在运行,如下所示:
== MyView: migrating ========================
== MyView: migrated (0.0763s) ===============
稍后在 db:refresh
中,我们会看到以下内容
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.
嗯,什么?我声明了 WITH DATA
。我还有另一个连续的迁移,明确调用了视图上的 REFRESH MATERIALIZED VIEW
命令。
徒劳无功,为了完成 rake db:refresh 任务,我必须手动刷新视图。
有趣的是,在 structure.sql 文件中,它显示被创建为 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;
我认为这是真正的问题,但我不知道有什么解决方法/变通方法。这也令人困惑,因为即使它是使用NO DATA创建的,后续的REFRESH MATERIALIZED VIEW应该会填充它并将其标记为可扫描。
是否有一些关于Postgres或AR的问题,我不知道,这导致我无法填充这个材料化视图?