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

On 6/8/2016 12:57 PM, 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?
>
> Thank you
>
>
> --


Oh, one other minor comment.  I usually have a temp schema staging area
with exact table structures but new data, and when everything is ready I
run:

start trans;

drop table public.tableA;
alter table tmp.tableA new schema public;

... same for 100 more tables ...
commit;

99% of the time it works great, but every once and a while I get a
deadlock error.  I just re-run it real quick and it works fine.

when you do your drop view, rename table, if you happen to get a
deadlock, I wouldnt worry too much.  Just re-run it.  Also, I'm still on
9.3 so maybe its not as much of a problem anymore.

-Andy