Can I atomically rename / replace 2 or more tables and views?
Given the X table and the Y view (which has the same structure as X), is there a way to rename X to Z and Y to X atomically so that no query ever sees anything named X? Renaming X and creating a view will also be valid.
The point will be to replace the old tables with views that mimic the old version from the new version as part of the schema migration plan, so that both sets of client code can run at the same time.
MySQL is my choice, but information on others will be helpful as well.
source to share
This is trivial in postgres and not possible in mysql.
mysql releases schema changes from transactions. I have a postgres background so the obvious thing to do (start a transaction, do some things, commit or rollback - postgres is not picky about "doing some things").
You can find several errors on this if you browse the mysql error database.
source to share
MySQL, like Oracle, treats most DDL statements (CREATE TABLE, ALTER TABLE, etc.) as causing "implicit commits". You cannot protect other connections from seeing DDL changes using the transaction model. (Postgres is unusual in this regard, I find.)
You might be able to lock tables using LOCK TABLES, but I suspect you will lose the lock as soon as you rename the table! LOCK DATABASE may work, but it may well require all other clients to shut down completely before you are given a lock request.
This will of course cause other clients to block when renaming tables, which is probably an inexpensive operation.
source to share