Обсуждение: Using a VIEW as a temporary mechanism for renaming a table
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
![]() |
Ben Buckman / Platform Engineering www.shyp.com Shipping made easy |
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 > > > -- > > > Shyp > *Ben Buckman / Platform Engineering* > www.shyp.com > Shipping made easy <https://www.shyp.com/> > > I think it kinda depends on how you roll out an application. Will you kick everyone out, updated it and let them back in? Sounds like you want to avoid that to avoid downtime. But, if your old app is hitting a view, and inserts data via the view into the new table, will it be good data? Will the new app be ok with it? -Andy
Thanks Andy.
My understanding, and please correct me if I'm wrong, is that the view will effectively inherit the table's constraints, because writes to the view that can't be written to the table will fail on the table. Re: "will the data be good data," what risks should I be considering?
In terms of rollout, we would 1) create the view, 2) deploy code that uses the new [view] name, 3) drop the view and rename the table. Deployments are "rolling" so there would be no downtime. The app and users shouldn't notice/care if they're hitting the table or the view.
Thank you
On 6/8/2016 2:57 PM, Ben Buckman wrote: > Thanks Andy. > > My understanding, and please correct me if I'm wrong, is that the view > will effectively inherit the table's constraints, because writes to the > view that can't be written to the table will fail on the table. Re: > "will the data be good data," what risks should I be considering? > > In terms of rollout, we would 1) create the view, 2) deploy code that > uses the new [view] name, 3) drop the view and rename the table. > Deployments are "rolling" so there would be no downtime. The app and > users shouldn't notice/care if they're hitting the table or the view. > > Thank you I'd assumed new version of app would have new columns in the table. That's what I meant by good data. New columns would not get populated by the old app. But if the table structure isn't changing, then I'd say your plan sounds like it should work. I've never tried it, personally, but I would if I were in the same boat. -Andy
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
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@squeakycode.net> wrote:
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: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
--
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
![]() |
Ben Buckman / Platform Engineering M. 415.471.4180 www.shyp.com Shipping made easy |
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
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@computer.org> wrote:
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.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?
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
![]() |
Ben Buckman / Platform Engineering M. 415.471.4180 www.shyp.com Shipping made easy |