REFRESH MATERIALIZED VIEW in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the result of refreshing a materialized view?

Consider a materialized view sales_summary that aggregates sales data. After new sales are inserted into the base table, what will be the result of running REFRESH MATERIALIZED VIEW sales_summary;?

PostgreSQL

REFRESH MATERIALIZED VIEW sales_summary;

A The materialized view is deleted and recreated from scratch, losing all previous data.

B The materialized view data is updated to reflect the latest sales data from the base tables.

C The materialized view remains unchanged until the database is restarted.

D The materialized view is locked and cannot be queried until manually unlocked.

💡 Hint

Think about what a materialized view stores and what refreshing it means.

Refreshing a materialized view updates its stored data to match the current state of the underlying tables. It does not delete or lock the view permanently.

📝 Syntax

intermediate

Which REFRESH MATERIALIZED VIEW syntax is correct?

Choose the correct syntax to refresh a materialized view named inventory_status in PostgreSQL.

A REFRESH MATERIALIZED VIEW inventory_status;

B REFRESH VIEW MATERIALIZED inventory_status;

C UPDATE MATERIALIZED VIEW inventory_status;

D REFRESH MATERIALIZED inventory_status VIEW;

💡 Hint

Remember the exact order of keywords in the command.

The correct syntax is REFRESH MATERIALIZED VIEW view_name;. Other options are invalid SQL.

❓ optimization

advanced

How to refresh a materialized view without locking reads?

You want to refresh a materialized view daily_report but allow users to continue reading the old data during the refresh. Which option achieves this?

PostgreSQL

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_report;

A REFRESH MATERIALIZED VIEW CONCURRENTLY daily_report;

B REFRESH MATERIALIZED VIEW daily_report WITH NO LOCK;

C REFRESH MATERIALIZED VIEW daily_report NOWAIT;

D REFRESH MATERIALIZED VIEW daily_report SKIP LOCK;

💡 Hint

Look for the keyword that allows concurrent reads during refresh.

The CONCURRENTLY option refreshes the materialized view without locking out reads, allowing users to query the old data until the refresh completes.

🔧 Debug

advanced

Why does REFRESH MATERIALIZED VIEW CONCURRENTLY fail with this error?

You run REFRESH MATERIALIZED VIEW CONCURRENTLY sales_data; but get the error: ERROR: materialized view must have a unique index to use CONCURRENTLY. What is the cause?

PostgreSQL

REFRESH MATERIALIZED VIEW CONCURRENTLY sales_data;

A The materialized view is empty and cannot be refreshed concurrently.

B The base tables of the materialized view are missing primary keys.

C The materialized view <code>sales_data</code> does not have a unique index defined.

D The database user lacks permission to refresh materialized views concurrently.

💡 Hint

Check the requirements for using CONCURRENTLY with materialized views.

To refresh a materialized view concurrently, it must have at least one unique index to identify rows during the refresh process.

🧠 Conceptual

expert

What happens internally during REFRESH MATERIALIZED VIEW?

When you execute REFRESH MATERIALIZED VIEW, which of the following best describes what happens inside the database?

A The database copies the materialized view data to a temporary table and merges changes asynchronously.

B The database marks the materialized view as stale but does not update data until queried.

C The database updates only the changed rows in the materialized view using incremental changes from the base tables.

D The database reruns the query defining the materialized view and replaces the stored data with the new result set.

💡 Hint

Think about how materialized views store data and how refresh works.

REFRESH MATERIALIZED VIEW reruns the original query and replaces the stored data entirely. PostgreSQL does not support incremental refresh by default.