Oh yeah, the table structure will change after this is all done, but not in the middle of it. The view would only last a few minutes and maintain the exact same schema.
Thanks for the tip re: deadlocks, I'll keep that in mind! Ben On Wed, Jun 8, 2016 at 1:24 PM, Andy Colson <a...@squeakycode.net> wrote: > 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 > -- [image: Shyp] *Ben Buckman / Platform Engineering* M. 415.471.4180 www.shyp.com Shipping made easy <https://www.shyp.com/>
