5

PostgreSQL 9.4 supports REFRESH MATERIALIZED VIEW CONCURRENTLY.

I'm not sure, but I think the REFRESH command is viewed as DDL by PostgreSQL, and PostgreSQL wraps all DDL within transactions so that simultaneous transactions can't see the effects of DDL commands issued by other concurrent transactions.

I verified this works by opening two transactions, inserting data and then refreshing the materialized view in one of the transactions, and then querying the materialized view in both transactions. Only the first transaction saw the updated materialized view.

There's a few edge cases I'm curious about though--I tried testing these, but my dataset was too small so the refresh finishes too fast:

  1. What happens if two transactions try to refresh a materialized view in parallel? Is this allowed or will it throw an error?

  2. If allowed, will the refreshes be executed in parallel or serial? Will they slow each other down? Which result will ultimately 'win'?

  3. Let's say I have an application with many users. Can this create problems where the application opens a transaction for the first user, realizes the materialized view is missing an entry so it refreshes the materialized view before returning the data, and then another user's transaction comes along before the first refresh finishes and does the same thing. Regardless of whether they're in parallel or serial, it seems this could create a temporary backlog effect that quickly stacks up where the refresh commands are being emitted faster than they're processed. As soon as one of the refreshes finishes first one finishes, any new transactions won't try to refresh it, but this could still take a long time. Especially if concurrent refreshes slow each other down, this could get painful quickly. Do I instead need to manually set a global flag in my app indicating that a refresh is currently happening so that this scenario doesn't happen?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.