FAQ / Troubleshooting

Practical answers to common questions and failure modes when working with mat_views (definitions → services → jobs → rake tasks), the demo app, and PostgreSQL materialized views.

Quick diagnostics (copy-paste)

Is my MV there? What’s its schema?

SELECT schemaname, matviewname FROM pg_matviews ORDER BY 1,2;

What unique indexes exist on an MV?

SELECT i.relname AS index_name, idx.indisunique, a.attname AS column
FROM   pg_class t
JOIN   pg_index idx ON idx.indrelid = t.oid
JOIN   pg_class i  ON i.oid = idx.indexrelid
LEFT   JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(idx.indkey)
JOIN   pg_namespace n ON n.oid = t.relnamespace
WHERE  n.nspname = 'public' AND t.relname = 'mv_user_activity';

Who’s holding a lock on my MV?

SELECT pid, usename, state, query
FROM pg_stat_activity
WHERE query ILIKE '%mv_user_activity%'
ORDER BY query_start;

Latest run status per view (create):

SELECT d.name, r.status, r.duration_ms, r.finished_at
FROM mat_view_definitions d
LEFT JOIN LATERAL (
  SELECT * FROM mat_view_runs r
  WHERE r.mat_view_definition_id = d.id
  AND r.operation = 'create'
  ORDER BY r.created_at DESC
  LIMIT 1
) r ON true
ORDER BY d.name;

Latest run status per view (refresh):

SELECT d.name, r.status, r.duration_ms, r.finished_at
FROM mat_view_definitions d
LEFT JOIN LATERAL (
  SELECT * FROM mat_view_runs r
  WHERE r.mat_view_definition_id = d.id
  AND r.operation = 'refresh'
  ORDER BY r.created_at DESC
  LIMIT 1
) r ON true
ORDER BY d.name;

Latest run status per view (drop):

SELECT d.name, r.status, r.duration_ms, r.finished_at
FROM mat_view_definitions d
LEFT JOIN LATERAL (
  SELECT * FROM mat_view_runs r
  WHERE r.mat_view_definition_id = d.id
  AND r.operation = 'drop'
  ORDER BY r.created_at DESC
  LIMIT 1
) r ON true
ORDER BY d.name;

Latest run status per view (all):

SELECT d.name, r.status, r.duration_ms, r.finished_at
FROM mat_view_definitions d
LEFT JOIN LATERAL (
  SELECT * FROM mat_view_runs r
  WHERE r.mat_view_definition_id = d.id
  ORDER BY r.created_at DESC
  LIMIT 1
) r ON true
ORDER BY d.name;

General

“Nothing happens when I run a rake task”

  • Ensure Rails environment is loaded (=> :environment is present).
  • Tasks log via Rails.logger (not puts). In development:
    • Ensure the logger outputs to STDOUT and the log level isn’t too high.
  • If the task enqueues jobs, make sure your queue backend is running (or use config.active_job.queue_adapter = :inline for immediate execution in dev).
  • Add --yes or YES=1 to bypass confirmation prompts.

“Adapter guesses the wrong backend?”

  • It does not guess. The adapter calls the unified API: MatViews::Jobs::Adapter.enqueue(job, queue:, args:) It relies on your app’s configured backend (ActiveJob, Sidekiq, Resque). Configure your backend per your app’s needs.

Definitions (schema & SQL)

“Invalid view name format”

  • Definitions must use simple identifiers (no dots): mv_user_activity ✅, public.mv_user_activity ❌
  • Schema is resolved via search_path and quoted safely by services.

“View exists in DB but there’s no MatViewDefinition”

  • Tasks intentionally raise a guardrail error if you pass public.my_mv and there’s no matching MatViewDefinition.
  • Fix: create the definition record (preferred) or drop the orphan MV manually if it’s truly obsolete.

Create

“Create failed with SQL error”

  • Check your definition’s sql. Run it as a plain SELECT first.
  • Verify permissions (e.g., on referenced tables).
  • For large outputs, consider WITH NO DATA → index → refresh (see Best Practices).

“Create idempotently”

  • Use the job/task flow; pass force only when you want to rebuild.
  • Pattern: CREATE ... WITH NO DATA, then build indexes concurrently, then refresh concurrently.

Refresh

“cannot refresh materialized view concurrently without a unique index”

  • Concurrent refresh requires a unique index that covers all rows of the MV result.

  • Create it first (often on id/user_id):

    CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS idx_mv_user_activity_user_id
ON "public"."mv_user_activity"(user_id);

“Concurrent refresh fails in a transaction”

  • REFRESH MATERIALIZED VIEW CONCURRENTLY cannot run inside a transaction block.
  • Ensure your orchestration does not wrap it with BEGIN … COMMIT.

“Regular refresh locks readers”

  • That’s expected for regular refresh. Use concurrent (requires unique index) or the swap strategy to minimize read downtime.

“Refresh fails due to locks”

  • Check pg_stat_activity to find long transactions or sessions referencing the MV.
  • Consider retry logic, stagger refresh schedules, or switch to swap if appropriate.

Swap refresh

“Indexes/privileges disappeared after swap”

  • The new temp MV must receive the same indexes and grants before you rename it into place.
  • Ensure your SwapRefresh implementation recreates all required indexes and permissions.

Delete

“Dependencies exist. Use cascade: true”

  • Another object (e.g., a view) depends on the MV. Drop dependents first or pass cascade: true.
  • Rake task: mat_views:delete_by_name[mv_name,true,--yes]

“Delete should be idempotent”

  • Use if_exists: true (default in the service). Result is :skipped when the MV isn’t present.

Rake tasks

“How do I skip confirmation?”

  • Pass --yes as the last arg or set YES=1:

    bin/rake 'mat_views:create_all[,--yes]'
YES=1 bin/rake 'mat_views:refresh_all[]'

“Which arguments do tasks accept?”

  • Create: [view_name, force, --yes] / [mat_view_definition_id, force, --yes] / [force, --yes]
  • Refresh: [view_name, row_count_strategy, --yes] (estimated exact blank)
  • Delete: [view_name, cascade, --yes] (true false)

“Name vs schema-qualified name?”

  • You may pass public.mv_name. If a physical MV exists but no definition, tasks raise to avoid acting on unknown objects.
  • Prefer passing definition names (simple identifier).

Jobs & Adapter

“Which queue do jobs use?”

  • MatViews.configuration.job_queue (default :default). Override in your initializer.

“Do jobs record results anywhere?”

  • Yes. Each job writes a run row:
    • Create → mat_view_runs with operation: :create
    • Refresh → mat_view_runs with operation: :refresh
    • Delete → mat_view_runs with operation: :drop
  • They include status, started_at, finished_at, duration_ms, error, and meta.

“How do I enqueue?” 

MatViews::Jobs::Adapter.enqueue(
  MatViews::RefreshViewJob,
  queue: MatViews.configuration.job_queue,
  args:  [definition.id, :estimated] # or :exact
)
  • The adapter does not detect your backend; it assumes it’s already configured (ActiveJob/Sidekiq/Resque).

Validation & Benchmarking

“Validator says: no MVs found”

  • Create views first (bootstrap demo or your own definitions):

    bin/rake 'mat_views:bootstrap_demo[1,--yes]'
# or
bin/rake 'mat_views:create_all[,--yes]' && bin/rake 'mat_views:refresh_all[,--yes]'

“Found MVs in DB but no definitions”

  • The validator needs definitions to get the baseline SQL.
  • Ensure MatViews::MatViewDefinition exists for each MV name.

“Row counts differ (baseline vs MV)”

  • Your MV definition may have drifted from the baseline SQL or missed filters/joins.
  • Recreate/swap the MV with the current definition.
  • For stricter checks, extend the validator to compare aggregates or sampled rows.

“Huge variance in timings”

  • Run more iterations (e.g., 100–300).
  • Ensure a quiet DB; watch pg_stat_activity.
  • Consider a warmup run if you want hot-cache numbers.

Permissions

“Permission denied on MV”

  • Grant read access explicitly:

    GRANT SELECT ON "public"."mv_user_activity" TO app_user;

  • Also verify privileges on underlying tables if you run baseline SQL in validation.

Search path & quoting

“My view is in a non-public schema”

  • The services resolve schema from search_path, including quoted tokens and $user. If the resolved schema doesn’t exist, they fallback to public.
  • For explicit control, keep search_path predictable per environment.

“Why do you quote identifiers?”

  • We quote schema and relation with quote_table_name / identifier rules to avoid conflicts with mixed case or reserved words.

Testing & CI

“How do I silence logs in specs?”

  • In RSpec, mock Rails.logger with a spy:

    let(:fake_logger) { instance_spy(Logger, info: ->(*){}, warn: ->(*){}, error: ->(*){}, debug: ->(*){}) }
allow(Rails).to receive(:logger).and_return(fake_logger)

“How do I test enqueue calls?”

  • Set up a spy:

    allow(MatViews::Jobs::Adapter).to receive(:enqueue)
# ...
expect(MatViews::Jobs::Adapter).to have_received(:enqueue)
  .with(MatViews::RefreshViewJob, queue: anything, args: [defn.id, :estimated])

Common error → fix cheatsheet

Error message (snippet) Likely cause Fix
Invalid view name format Definition name has a dot or invalid chars Use simple identifier (e.g., mv_users)
cannot refresh ... concurrently without a unique index Missing unique index Create unique index; refresh again
... inside a transaction block Running concurrent refresh in a transaction Run outside BEGIN ... COMMIT
dependencies exist. Use cascade: true Dropping MV with dependents Drop dependents or cascade: true
No MatViews::MatViewDefinition found Name mismatch / missing definition Create the definition or rename MV
“No MVs found” in validator Nothing in pg_matviews Create MVs (bootstrap or create tasks)
Row counts mismatch MV SQL drifted Recreate/swap MV; compare SQL carefully
Jobs don’t run Backend not running Start your queue engine or use :inline in dev

Still stuck?

  • Inspect the run rows (create/refresh/delete) for error strings and timing.
  • Look at meta[:sql] in responses or logs for the exact SQL executed.
  • Enable more verbose DB logs (Postgres log_statement, log_duration) in a dev/staging environment.
  • Raise an issue on the project’s GitHub with details about your setup, Postgres version, and any relevant logs.
  • We offer Professional support for complex issues or custom integrations. Contact us at sales@codevedas.com for inquiries.