Menu
I am considering the problems with materialized views in Postgres 9.4. Sometimes query "refresh materialized view xxx" just hangs forever.
The only way I found is to completely restart postgres service. After restart execution takes seconds.
My view is not so complex it use about 10 tables.
I've executed select * from pg_stat_activity where state = 'active' and the only running query was refresh view.
This problem appears with CentOS operating system.
What should I look when this problems comes next time? locks? or something else?
210
Scheduling the REFRESH operation And then your materialized view will be refreshed at each 30 minutes.
Automatic refresh. By default, materialized views are automatically refreshed within 5 minutes of a change to the base tables, but no more frequently than every 30 minutes.
- Use super-fast solid-state disks - The easiest and most reliable way is to speed-up a materialized view refresh is to move the target tables and MV's to SSD. SSD runs several hundred times faster than platter disk, and it plops right in, just a few hours to install.
If you want the materialized view to be refreshed automatically you should use ON COMMIT refresh method. Since you have specified an ON DEMAND refresh you will have to manually refresh the materialized view using DBMS_MVIEW. REFRESH method. There are lot of considerations for refreshing a materialized view.
In my case the problem was in unclosed transaction.
There was a transaction in which my materialized view was queried. That transaction was never closed.
I've added a commit after query and it solves the issue. Looks like postgre hold some locks on view until the transaction become closed.
78
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
