Using a VIEW as a temporary mechanism for renaming a table

Поиск
Список
Период
Сортировка
От Ben Buckman
Тема Using a VIEW as a temporary mechanism for renaming a table
Дата
Msg-id CAFCabS6RaAmNsV+1TuvHPk3Hcatrs_QJXZYk0EqU54xtd-v9xA@mail.gmail.com
обсуждение исходный текст
Ответы Re: Using a VIEW as a temporary mechanism for renaming a table  (Andy Colson <andy@squeakycode.net>)
Re: Using a VIEW as a temporary mechanism for renaming a table  (Andy Colson <andy@squeakycode.net>)
Re: Using a VIEW as a temporary mechanism for renaming a table  (Berend Tober <btober@computer.org>)
Список pgsql-general
Hello,
I would like to rename a table with ~35k rows (on pgsql 9.4), let's say from `oldthings` to `newthings`.
Our application is actively reading from and writing to this table, and the code will break if the table name suddenly changes at runtime. So I can't simply run an `ALTER TABLE oldthings RENAME TO newthings`, unless we take downtime, which we'd prefer not to do. (I'd also prefer to avoid a data migration from one table to another, which would require dual-writes or some other way to handle data written during the transition.)

It seems that a reasonable approach to do this without downtime, would be to use a temporary VIEW. We can `CREATE VIEW newthings AS SELECT * FROM oldthings;`. Views in pg9.4 that are backed by a single table support writes. So my plan is like this:

1. Create the view, essentially as an alias to the table.
2. In the code, change all references from the old name to the new name. The code would "think" it's using a renamed table, but would really be using a view. 
  (At this point, I expect that all basic CRUD operations on the view should behave as if they were on the table, and that the added performance impact would be negligible.)
3. In a transaction, drop the view and rename the table, so `newthings` is now the original table and `oldthings` no longer exists. (In my testing, this operation took <10ms.)
  (When this is done, the view will have only existed and been used by the application for a few minutes.)

What are people's thoughts on this approach? Is there a flaw or potential danger that I should be aware of? Is there a simpler approach I should consider instead?

Thank you


--


Shyp
Ben Buckman / Platform Engineering
www.shyp.com
Shipping made easy

В списке pgsql-general по дате отправления:

Предыдущее
От: Sameer Kumar
Дата:
Сообщение: Re: connection pooling, many users, many datasources
Следующее
От: Matthew Kelly
Дата:
Сообщение: Re: Monitoring and insight into NOTIFY queue