Recently I found out that MySQL doesn't support rollback of DDL such as "alter table"... Being used to PostgreSQL, that struck me as odd, but a friend of mine told me that even Oracle doesn't allow it.. Are there technical reasons for not supporting it? Is it simply an "uninteresting" feature for them?
Edit: just found this comparison. It looks like there are many DBMSes that do support transactional DDL.
The reason why this works in PostgreSQL is that the system catalogs are regular tables. So creating a new function, for example, just requires inserting a row into the pg_proc table, changing the default value of a column just requires making an update to some row in pg_attrdef, and so on. Since tables are transactional anyway, you'd almost have to go out of your way not to have it work that way. (Lots of painful implementation details omitted here. ;-) )
I suppose, not knowing the source code, that other database engines use some custom internal structures to represent their system catalog information. And so they'd have to put in extra effort, a lot of extra effort likely, to make transactional DDL work, and it's apparently not a priority for them.
The flip side of this is that this is the reason why major version upgrades of PostgreSQL are so painful. Other products can presumably design their internal metadata structures with changes and updates in mind, and so there are no issues with upgrading to a new major version. In PostgreSQL, there is no way to change a system catalog table to suddenly look like a newer version of a system catalog table, at least not while keeping the system online, because that would require access to the system catalogs. Urgh.
Most don't? Bummer.
I principally use SQL Server and it does. I know Oracle doesn't but I thought Oracle might be an aberration.
In SQL Server, I'm quite certain you can run multiple DDL statements in a single transaction although I also think there's a couple of restrictions (which I have all forgotten). You can do a create or an alter or a drop of most things and roll it back, if you like. Red-Gate SQL Compare (a tool I love) takes advantage of this.
The problem with doing this is that your transaction scope becomes fairly interesting... When you involve the system catalogs in an update transaction (DDL), you run the risk of taking some really important locks and you may block access to the system catalogs. Users can't do much if their queries can't find their tables in the catalogs!
On balance, though, it's handy to be able to include DDL in a multi-statement transaction.
More usefully, the SQL Server DDL command TRUNCATE can also be an element of a multi-statement transaction. You can truncate a target table (very fast), build it, and then do a commit if you like the result. If something goes wrong, you rollback and voila!, it's like you never disturbed the table. Log space is also minimized. I take advantage of that fairly often.
TRUNCATE couldn't be rolled back. I was wrong.
Commented
Oct 17, 2011 at 15:16
In SQL Server we can rollback DDL statements, it's not using auto commit at the end of the statement. In other DBMS I don't know, but I remember that in Oracle one can't do the same. I believe it's specific to each DBMS, not sure what would the SQL standard say about this, but I'm sure no producer implements 100% the standard.
There's a similar question on SO: Is it possible to run multiple DDL statements inside a transaction (within SQL Server)?
Oracle has shared query parsing, so a SELECT * FROM table_a done by one session is (normally) the same as that of another session. That would break if one session thought there was ten columns in the table and another thought there were eleven.
DROPor aRENAME?