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.
Best Answer
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_proctable, changing the default value of a column just requires making an update to some row inpg_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.