Testing Materialized View in Rspec

Question

Using the Scenic gem I've built an activerecord model backed by a materialized view

class MatviewSales < ActiveRecord::Base
  self.table_name = 'matview_sales'
  self.primary_key = :id

  belongs_to :user
  belongs_to :account
  belongs_to :account_manager, class_name: User, foreign_key: 'manager_id'

  def self.refresh
    Scenic.database.refresh_materialized_view(table_name, concurrently: true)
  end
end

I'm now trying to test this model in RSpec but no matter what I do I can't get Postgres to populate the view with records:

> FactoryGirl.create(:sale_item)
> MatviewSales.refresh
> MatviewSales.all
=> #<ActiveRecord::Relation []>

How do I populate the materialized view with records for testing?

How is `:sale_item` related to `MatviewSales`? Can you show the schema for those 2 tables and the code for the `:sale_item` factory? — Midwire, Feb 15 '17 at 20:14
Agree with @Midwire. We'd need to see the schemas, the query for the materialized view, as well as the row that is created when you call the factory. Additionally, try setting `concurrently` to `false` in the refresh? — Derek Prior, Feb 19 '17 at 17:58

score 7 · Answer 1 · answered Mar 28 '17 at 11:01

Refreshing the materialized view won't take into account any not yet committed transactions.

When use rspec use_transactional_fixtures or DatabaseCleaner.strategy = :transaction this means, that your views won't see any prior FactoryGirl.create records.

The solution, I use is to turn off transactions for specific tests which use materialized views.

# spec_helper.rb
RSpec.configure do |config|
  config.use_transactional_fixtures = false
  # ... other configurations
  config.around(:each) do |example|
    DatabaseCleaner.strategy = example.metadata.fetch(:clean_database_with, :transaction)
    DatabaseCleaner.start

    example.run

    DatabaseCleaner.clean
  end
end

# your test
describe MatviewSales, clean_database_with: :truncation do
  it 'works :)' do
    FactoryGirl.create(:sale_item)
    MatviewSales.refresh
    expect(MatviewSales.count).to eq 1
  end
end

documentation about use_transactional_fixtures

Testing Materialized View in Rspec

1 Answers1