I am using Materialized Views from a PostgreSQL v12 DB for an app. The app initially refreshes the Materialized Views correctly at launch, and there is a button which essentially refreshes the Materialized Views. But when the button is pressed, the app hangs and nothing is refreshed.
I checked the PostgreSQL DB, and the state of the session is 'active' when the app is initially launched and the Materialized Views load correctly. But when they are refreshed later, the session is 'idle'.
I've also queried 'pg_stat_activity', and when the session is 'active' the query REFRESH MATERIALIZED VIEW is also 'active'. But when the session is 'idle', the query is also 'idle'.
When the session and query are 'idle' they stay in this state until I manually terminate the session. When this happens, I can properly refresh the materialized views initially. Then the same behavior happens where the session and query are 'idle' and hangs indefinitely.
Any ideas why this would be the case? I have a hunch it has something to do with a lock or a related transaction that is hanging, but admittedly, I do not know how to check this. I also want to note that this does not lock up or effect the rest of the DB. As always, let me know if you have any questions. Thanks!