Dependent Views and recreating views

[bamorim]

Well, I have a test suite that I keep database up do date with ActiveRecord::Migration.maintain_test_schema!

The problem is that it tries to recreate the database and sometimes it drops a view that others views depends, so it raises this exception

/home/bamorim/.rvm/gems/ruby-2.1.0@respondeai/gems/activerecord-4.1.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:128:in `async_exec': PG::DependentObjectsStillExist: ERROR:  cannot drop view analysis_periods because other objects depend on it (ActiveRecord::StatementInvalid)
DETAIL:  view impression_counts_by_period depends on view analysis_periods
view activity_statuses_by_period depends on view impression_counts_by_period
view subscription_statuses_by_period depends on view analysis_periods
view user_metrics_by_period depends on view analysis_periods
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
: DROP VIEW IF EXISTS "analysis_periods"

I have two approaches for this but can't realize which one is better because they have flaws.

The first one is using CREATE OR REPLACE VIEW istead o DROP and then CREATING.
The problem is that with this approach we can't change the type of the return, I mean, we cannot change the type of each column nor the order or the number of columns.

The other approach is using DROP VIEW ... CASCADE, but with this one we wouldn't know what views are going to be dropped and then needed to be rebuilt.

What do you guys think?

[bamorim]

Another solution that I don't know if it is possible would be always adding a drop_view in reverse order in schema.rb, but that is somewhat not DRY.

[ronen]

I think DROP VIEW ... CASCADE would be OK. it should be fine to drop all views that depend on the current view, since presumably the migration is going to be recreating all those other views anyway. no?

(as you say, another choice would be to explicitly include drop_views in reverse order -- but in order for that to work we'd need to drop all the views that depend on it, which is what CASCADE would do anyway.)

[hoenth]

@bamorim - I am curious about your statement that the CREATE OR REPLACE VIEW statement cannot be used if you are changing the type, order, or number of columns. Is this a postgres limitation. Mysql does not appear to have the same limitations, as the CREATE OR REPLACE VIEW is synonymous with ALTER VIEW (https://dev.mysql.com/doc/refman/5.0/en/alter-view.html).

If it is a postgres issue, then the mysql adapter might always use CREATE OR REPLACE VIEW instead of create. This would solve for us the same issue you are currently facing.