1

I'm trying to run REFRESH MATERIALIZED VIEW CONCURRENTLY recipe_search;, but PostgreSQL gives me this error: ERROR 55000 (object_not_in_prerequisite_state): cannot refresh materialized view "public.recipe_search" concurrently.

The materialized view in question looks like this in psql:

# \d recipe_search

   Materialized view "public.recipe_search"
  Column  |          Type          | Modifiers
----------+------------------------+-----------
 id       | integer                |
 title    | character varying(255) |
 document | tsvector               |
Indexes:
    "recipe_search_document_index" gin (document)
    "recipe_search_title_trgm_index" gin (title gin_trgm_ops)

Why can't I refresh this concurrently?

Nathan Long
  • 122,748
  • 97
  • 336
  • 451
  • 1
    Somebody just downvoted this. If you're mad that I answered my own question, see https://stackoverflow.com/help/self-answer. When I ran into this problem, I searched the web for this error message and found no helpful information. After I solved the problem, I came here to document it so that future searchers will find it. This fits exactly with the mission of StackOverflow. – Nathan Long Nov 16 '17 at 14:58
  • Self-answered questions are okay, but the fact that you self-answer it doesn't affect the question quality. Your question lacks the information needed to reproduce the problem. Aside from that, searching for the error message gives me https://stackoverflow.com/questions/41803781/refresh-materialized-views-with-concurrency, which this looks a dupe of, and a comment there suggests the error message should have been followed by a hint, which is not present in your question. (No, I didn't vote, at least not yet. The idea of posting it here is indeed good.) –  Nov 16 '17 at 15:12
  • @hvd I hadn't seen the hint because I was running the `REFRESH` via a trigger and a db library - if I try the `REFRESH` via psql directly, I do see it. I probably should have added the `\d` of the view; I've updated the question accordingly. But the main thing I wanted to accomplish was to enable searches for this error message to find the solution. – Nathan Long Nov 16 '17 at 20:12

1 Answers1

5

The materialized view needs a unique index

A materialized view cannot be refreshed CONCURRENTLY unless it has at least one unique index, as described in the docs:

CONCURRENTLY

... This option is only allowed if there is at least one UNIQUE index on the materialized view which uses only column names and includes all rows; that is, it must not index on any expressions nor include a WHERE clause.

https://www.postgresql.org/docs/9.6/static/sql-refreshmaterializedview.html

Nathan Long
  • 122,748
  • 97
  • 336
  • 451