If the schema of the original table is changed to require columns not in the view, like say a new NOT NULL column is added, insertions on the view will start to fail. The demo goes on to show how an updatable view is useful for a rename, but isn’t guaranteed to stay updatable. After it’s online and the old processes’ve been cleared out, we’d run a follow up migration of DROP VIEW chainwheel. In real life, that’d be a new deployment of the original app that’s had its table references updated from chainwheel to sprocket. Inserted and read 100 records of 'sprocket' We can then start a process that uses the new name: $ TABLE_NAME=sprocket bundle exec ruby app.rb While it’s running, migrate to the new name: bundle exec sequel -m migrations/ -M 2 postgres://localhost:5432/postgres-table-rename-testĪnd notice how the app continues to run happily. Inserted and read 100 records of 'chainwheel' We start a tiny app to read and write out of the table with its existing name: $ TABLE_NAME=chainwheel bundle exec ruby app.rb I made a little demo project that demonstrates the safely of a rename even while a program is running. This is the linchpin feature that makes the change possible with no user impact. There’s never a moment for other database consumers where the table has been renamed but the new view isn’t yet available. Postgres supports transactional DDL so that the RENAME TO and CREATE VIEW happen atomically. Postgres supports updatable views, meaning that with some caveats, views can support INSERT, UPDATE, and DELETE operations that will target their underlying table. To support programs still running against the old name, we create a view with the table’s old name of chainwheel as a stand-in for the table. RENAME TO immediately makes the table available under its new name of sprocket. Here’s some copy/pastable migration code for renaming a table (in this example, chainwheel -> sprocket): BEGIN Īnd with a post-deploy (after all clients are rotated) follow up of: DROP VIEW chainwheel Luckily, Postgres makes this possible relatively easily. We’d like to rename things, but to also do so with zero downtime and zero user impact. Copy this codeīut to schema hygiene fanatics out there (like myself), that’s not a satisfactory answer. I’d hazard to guess that this is how most shops run – people would generally prefer to rename as appropriate, but in practice it’s more time, risk, and effort than it’s worth. Practically speaking, the easiest way to administer a production database is to never rename anything, and live with the fact that some names are less-than-optimal. It’s annoying for users, and painful if a service is doing some business critical. Anything that was still running against the old name when a rename takes place will immediately break, causing downtime and major user impact.Īn alternative would be to disable all clients temporarily and then do the rename, and indeed a “we’re down for maintenance” screen was a pretty common sight in the 2000s, but serious services in the 2020s aim to never have downtime at all. The problem isn’t in the database itself, but in database clients. Anyone who’s run a production database before will recognize that outside of an academic context, it’s actually kind of hard. The SQL is a dead simple one-liner of ALTER. At first glance, renaming entities in a database seems like it should be easy.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |