Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
Suppose, I have a table foo, which contains some statistics that are computed every now and then. It is heavily used by other queries.
That's why I want to compute more recent statistics in foo_new and swap them when computation is ready.
I could do
ALTER TABLE foo RENAME foo_tmp;
ALTER TABLE foo_new RENAME foo;
but what happens if a query needs table foo inbetween those two lines when there is no table foo? I guess I have to lock it somehow... or is there another way to do it?
Use this one command:
RENAME TABLE foo TO foo_old, foo_new To foo;
It is an atomic operation: both tables are locked together (and for a very short time), so any access occurs either before or after the RENAME.
tableX that has got parent constraint referencing foo. After this RENAME it will be now referencing foo_old but if there are no constraints referencing foo then you should be OK...
Jan 17, 2017 at 11:23