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

Answers


Probably related to this bug:

http://www.postgresql.org/message-id/CAB7nPqTW3=97=e4aVO4qOp85ZA1OX74DvBhKTag1CEOgMZESaw@mail.gmail.com

And probably fixed in 9.3.4:

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

Need Your Help

how to make a ajax request in javascript/jquery

javascript jquery ios objective-c ajax

I am new to JavaScript and jQuery. I want to send an image to RTTI server. I have done the same task in native ios like below.

Query not ordering properly

sql oracle oracle11g

I have the following query which I did not write to produce the results: