Developer Experience when update_view fails

[bmulholland]

If I make a migration to update a view, and the SQL I write is invalid, then the migration fails in a way that sets my dev database to an invalid state. That's hard to recover from, and I end up needing to do error-prone workarounds like temporarily changing update_view to create_view in the migration. Ideally, update_view would either (1) fail to the previous state, or (2) allow re-running when the view doesn't exist.

This happens to me fairly often, perhaps because I'm writing complex queries and my SQL isn't that strong.

Specifically, let's say I do scenic:view and then write a query that uses nested aggregates. I run the database migration and PG::GroupingError: ERROR: aggregate function calls cannot be nested. Darn, okay. So I fix it, and run the migration again: PG::UndefinedTable: ERROR: view "X" does not exist

There may be dev vs prod considerations. For example, drop if exists could be restricted to the dev env.

On the other hand, when I was thinking through these scenarios, the lack of restore to the previous state is particularly concerning for prod deploys. The view is then totally gone! A failed migration would block the deploy of new code that could depend on the updated view, so that's good -- but the old code would try to use the view that's now been pulled out from under it. That would be a really panicky recovery situation -- and it would be very slow to recover from. I imagine this should basically never happen, but maybe if DB versions drift between dev/CI and prod? A defense in depth approach with proper failure modes of update_view is worth considering.

Related: #210

[derekprior]

Is this with DDL transactions enabled in your migration?

[bmulholland]

Yes, since that's the migration code the scenic generator creates. It is expected to disable them?

[derekprior]

No, but my expectation is that the failure would roll back to the known good state if executed in a transaction.