|
|
|
|
|
|
by rodeoclown
2718 days ago
|
|
|
You can even do SQL DDL ALTER TABLEs in a Transaction on production, do the change, run smoke tests in a nested transaction that are all rolled back when complete, and if anything fails you roll back the DDL changes and are back where you started clean. |
|
|
In mysql (and I think also mariadb) ALTER TABLE cannot be rolled back, and also implicitly does a COMMIT on any in-progress transaction before altering the table.
In PostgreSQL you generally can do ALTER TABLE in transactions, but there are dome restrictions or exceptions. If the alteration is to add a value to an enum, it cannot be done in a transaction.
MS SQL Server seems to be OK with ALTER TABLE in the midst of a transaction, according to this article [1]. Oracle seems to be similar to mysql.
Even the ones that support this well, like MS SQL Server, have restrictions on other DDL, such as creating indexes.
I think I'd rather just assume no DDL in transactions and design my schema update procedure accordingly, rather than asking whoever is designing the new schema to try to limit themselves to changes from the old that avoid whatever statements whatever DB server we are using doesn't allow.
[1] https://www.mssqltips.com/sqlservertip/4591/ddl-commands-in-...