Re: Using a VIEW as a temporary mechanism for renaming a table

Update on this –
Thanks for the feedback.
We just completed this process successfully. One thing we noticed was,
after creating the view and having the code start to read from it instead
of the table, we saw a significant postgres memory spike. It lasted until
we dropped the view and renamed the table a few minutes later. I don't know
exactly why – was it caching the view in memory? – or whether it would have
persisted if we hadn't dropped the view, but FYI if you want to do the same.

Overall I think this was a successful process and I'd do it again if we had
the same need to rename a table.

On Wed, Jun 8, 2016 at 3:55 PM, Berend Tober <btober(at)computer(dot)org> wrote:

> Ben Buckman wrote:
>
>> 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?
>>
>
> I would totally do it this way ... and after creating the view, I'd
> probably leave it as the normal interface. In fact, I've adopted a practice
> of utilizing views as the user interface generally and not exposing the
> actual tables at all.
>
> As you may realize, but I'll point out for completeness, that for more
> complicated situations (i.e, when the view is not just representing a
> single table as your current case), if the view represents a multi-table
> join, you can use triggers to intercept DML on the view and implement logic
> to interact with the multiple underlying tables for inserts and updates.
>
> Additionally, if you don't want to modify the application, consider
> creating the view, using the same original table name but in a separate
> schema and setting the search_path so the the view is found before the
> table. Then you can rename the table, simultaneously redefining the view to
> point the the new table.
>
>
> -- B
>
>
>
>

--

[image: Shyp]
*Ben Buckman / Platform Engineering*
M. 415.471.4180
www.shyp.com
Shipping made easy <https://www.shyp.com/>