Postgres: Update table joined with materialized view? Error: view cannot lock rows in materialized view
In Postgres 9.3.3, I have a materialized view that does some heavy/intensive calculations. I want to use that materialized view joined to the original table to determine what value to change it's status. How do I do this using an update join in Postgres?
UPDATE account SET status = 3 FROM account a JOIN mv_account mv ON mv.id = a.id WHERE mv.very_long_calc = true ERROR: cannot lock rows in materialized view "mv_account" SQL state: 42809
Probably related to this bug:
Allow materialized views to be referenced in UPDATE and DELETE commands (Michael Paquier)
Previously such queries failed with a complaint about not being able to lock rows in the materialized view.
There is no reason for a self join. Just do:
update account a set status = 3 from mv_account mv where mv.id = a.id and mv.very_long_calc