I was trying to create a MV on a Postgres db, on a table with a few millions records.
During the development process, I migrated & rolled back (CREATE MATERIALIZED VIEW / DROP MATERIALIZED VIEW) a few times. The underlying requests I was trying were taking 10-20s, my Postgres statement timeout is set at 60s, so plenty of times to execute it.
When executing the underlying request, it was always going through.
At first, the view was created fine, but suddenly it stopped working (I didn't even change the query), and it was always timing out at 60s.
Executing the underlying query still worked fine.
I tried VACUUMing, didn't change anything either.
And, then for some reason I changed the name of the view, and it miraculously worked again!
So, my problem is gone, but I'd love to know why that happened if someone here knows. Is there some kind of cache that got bloated by the successive creations/deletions, slowing down the query?