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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general