CODEWITHSUNDEEP
postgresqlsql-updatematerialized-views
thames
thames

Reputation: 6083

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

Upvotes: 1

Views: 869

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125254

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

Upvotes: 2

Related Questions