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

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 <andy(at)squeakycode(dot)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(at)postgresql(dot)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/>