[proposal] rename view

[gagalago]

I would like to discuss a new feature to Scenic that will allow renaming (materialized) views.

Use case:

When a materialized view takes a long time to refresh, doing an update_view will either create a long time without data or will block the migration for a long time (depending on if you populate data inside the migration or just after).

Another way of working that prevents the previous behavior is:

1. first migration
   1. create the new version of the materialized view (ex: my_view) with another name (ex my_view_next) without data
2. populate it outside of the migration
3. second migration (maybe in a second deployment depending on how you populate the view)
   1. drop the actual view
   2. rename the new materialized view to the actual view (ALTER MATERIALIZED VIEW my_view_next RENAME TO my_view;)

In case of a cascade of dependencies, we can also need this to apply this workflow to normal view and not only to materialized views.

with this flow, we can achieve short migration and so lock the database during less time for the case where populating views take a lot of time.

Open questions

To implement this kind of feature in Scenic, few questions need to be discussed first:

• how to store renaming?
  1. use the version number as a temporary suffix. so we can use it in this way create_shallow_view :my_view, version: 2 then replace_shallow_view :my_view, version: 2 (I am open for better naming).
  2. only create the rename_view method. To maintain the usage of following migrations on the view (update, replace, remove), we need copy the db/views/my_view_next_1.sql as it is to db/views/my_view_2.sql and verified during the migration that these 2 files are identical (can still be changed later and be different and so introduce error for people running the migration)
  3. something else (make your proposition in comment)

I am in favor of option ii as I think that the genericity is better than having to maintain fewer files but also fewer use cases.

• Do we rename also indexes at the same time?
  • maybe thought an option

Do you think that this kind of feature can be a great addition to Scenic @derekprior @wasabigeek @calebthompson ?
Depending on what the community feels about this proposition, I can start to work on it next week.

[derekprior]

My initial reaction is that if we want to have a feature like this, it should "just work". I've definitely manually done things like this in the past when migrating materialized views.

Perhaps any migration that updates a materialized view and populates it should follow this pattern automatically? What would the downsides of that be? Is there any way that would be a breaking change?

[gagalago]

I will pay attention during the implementation to have something that 'just work'. Maybe I can provide different methods: some to provide a ruby version of the database renaming feature and some that use these ones to provide an easy way of following the described flow.

The only downside, that I can think of, is that if the storage of the materialized view is huge. Then during a time, we will use twice this amount of storage (the actual and the next version).
I don't think that adding methods for this will need breaking changes.