45

I have a materialized view in Postgres 9.3 that I'd like to update with new columns. However, other materialized views also depend upon this view, and the error message indicates that dropping a view isn't possible when other objects depend on it.

ERROR: cannot drop materialized view latest_charges because other objects depend on it

It also appears from the documentation that the REPLACE keyword isn't valid for a materialized view. Is there any shortcut aside from dropping all dependent objects and rebuilding each one?

2
  • 9
    Sadly, I think you're stuck with dropping them all and rebuilding. Jan 7, 2014 at 22:58
  • @CraigRinger interested in adding this as an answer? Sep 1, 2014 at 12:54

3 Answers 3

35

As of PostgreSQL 9.4: Different to the documentation of CREATE VIEW, the documentation of CREATE MATERIALIZED VIEW does NOT mention the REPLACE keyword. There seems to be no shortcut aside from dropping all dependent objects and rebuilding each one.

When you do so, I can only recommend two small things:

  1. Use DROP MATERIALIZED VIEW blabla CASCADE to get a list of all dependent objects
  2. Do the drop and recreation of all dependent object in one transaction.
1
  • 1
    Thanks, this is exactly how I've been doing it. It's just a pain to keep straight since I'm building one base materialized view for analytical purposes that gets reused in many other views. That base view rarely changes, but the ones that depend on it change daily.
    – John
    May 19, 2015 at 17:58
14

For my situation, I prefer to limit the drops by using a view layer:

  1. Create a copy of the materialized view suffixed with "_new" and also use "WITH NO DATA" for performance, make sure any indexes are also created with suffix and any other dependant objects discovered via DROP...CASCADE
  2. Create a view upon the new materialized view to provide the layer of abstraction so I only need to change it in one place
  3. ALTER the existing dependencies to instead refer to the new view (refreshing the data if needed beforehand)
  4. Drop the original materialized view and indices which should now not have any dependants
  5. ALTER THE materialized view and indices to drop the suffix to restore the original names

eg.

create table test (myfield int);
insert into test values (1);
create materialized view mv_test as select myfield from test;
create view v_test as select myfield from mv_test;
select * from v_test;
create materialized view mv_test_new as select myfield, myfield+1 as myfield2 from test;
alter view v_test rename to v_test_old;
alter materialized view mv_test rename to mv_test_old;
create view v_test as select myfield,myfield2 from mv_test_new;
select * from v_test;
alter materialized view mv_test_new rename to mv_test;
drop view v_test_old; -- when ready
drop materialized view mv_test_old; -- when ready
3
  • I'm not clear on those last two steps. Is 4 a sentence that runs on into 5? could you do a quick example with some named matviews and views?
    – kimbo305
    Jul 20, 2018 at 22:19
  • corrected the truncated sentence and added example
    – RuiDC
    Jul 23, 2018 at 9:16
  • I'm not sure the extra view is needed over the materialized view when the materialized views are not nested, but this is a good idea for mat views that depend on other mat views. As long as you create and make indexes on the new version of the materialized view, the drop and rename should not take very long to execute. But overall, a good strategy. (Make sure you create indexes on the NEW materialized view to match what's on the old one or you'll lose your performance. Do this before the drop & rename.)
    – ps2goat
    Feb 3, 2021 at 17:55
1

In PgAdmin (version 4.x), I could easily modify the definition (I added a where clause) in the properties box. Your problem could be solved that way.

1
  • 4
    Using this method will result in dropping the materialized view and all its dependents. Mar 27, 2020 at 1:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.