Now we can move on with our lives, without having weird legacy names and have BI/Analytics team ask questions about it every now and then.
You definitely shoud write a corresponding down method in case things go wrong.Īnd that’s it. Note that because of using execute the migration is irreversible. You just want to remember to delete the view next time the deployment is done. Most likely it won’t cause any negative side-effects for your application. Actually, our migrator reported 0.0s for this migration (which means less that 50 ms IIRC) for a table with few million records. for consistency execute "ALTER SEQUENCE old_name_id_seq RENAME TO new_name_id_seq" execute "ALTER INDEX old_name_something_id_index RENAME TO new_name_something_id_index" end Actually, original was written in Ecto.Migration: def up rename_table :old_name, :new_name execute "CREATE VIEW old_name AS SELECT * FROM new_name" # also rename sequences, indices etc. Specifically, we can use them to just create aliases.Īrmed with that knowledge, we may write our migration as something like this I’m using ActiveRecord migration in the example, but it can be ported to annything.
#POSTGRESQL RENAME UPDATE#
What’s even better? Simple Simple – meaning no aggregations, no window functions, no unions, no grouping, limits offsets… But even some joins count! views are updatable, which means you can insert to them or update them - and it will be perfectly reflected in an underlying table. Every time you select from a view, it is translated to an underlying query. These views are, in essence, an alias for a query. Since a couple of versions PostgreSQL supports usage of a regular dynamic views along its old materialized views. So, if only we could use some kind of an alias to route requests to old table name to a new name…įortunately, we can. As a result they still try to query a non-existing table, resulting in an exception (most likely). Now, when migrations are run and your renaming-the-table migration among them, the old table does not exist, but the old application instances still reference it. In fact, it’s not that uncommon to take a minute or longer, as you have to let the servers finish serving existing requests etc. The problem is that there is a non-zero gap between 2.
#POSTGRESQL RENAME CODE#
Restart application instances to use new code.Make the new code ready (build a Docker image, compile, copy to server).They vary from project to project, but usually contain these three in this order: Most I’m tempted to say “all”, but that’s probably not true of deployment processes these days consist of few sequential steps to get a new version up and running. Fortunately, with a little more ceremony you can do it right and here is how. If you do it naively by simply calling ALTER TABLE RENAME, it will harm your production. In my opinion it was worth it and I pushed the change through, without any downtime. Their stance was that it’s better to live with a too generic table name than to attempt a risky operation.
Their argument was that is won’t work without causing a downtime and generally it’s not worth it. When some time ago I announced that we need to rename one of the database tables in our recently launched service, some of my colleagues looked at me like I said something really stupid. Sometimes you don’t get it right in the first attempt.
0 kommentar(er)
