3 ms·
> Rollback of DDLs Oracle can't roll back these (create table/drop index etc)? That really surprises me. Pretty certain MSSQL can, because I've used it. Amazed
by throwaway_pdp09 6y ago
> Rollback of DDLs
Oracle can't roll back these (create table/drop index etc)? That really surprises me. Pretty certain MSSQL can, because I've used it. Amazed, actually, that oracle can't.
- atombender 6y agoVery few databases support transactional DDL, meaning that you can do something like: BEGIN; DROP TABLE foo; CREATE TABLE bar (t int); ROLLBACK; ...and it will just work as you expect. Oracle doesn't, nor does MSSQL. As far as I know, other than Postgres, only Sybase ASE, Informix, DB2 UDB, and Firebird support transaction DDL.
- throwaway_pdp09 6y agoWell, https://www.mssqltips.com/sqlservertip/4591/ddl-commands-in-transactions-in-sql-server-versus-oracle/ https://www.mssqltips.com/sqlservertip/4591/ddl-commands-in-...
- mayoff 6y agoDDL is transactional in SQLite.
- castorp 6y ago> Very few databases support transactional DDL Postgres, SQL Server, Firebird, DB2, SQLite, Ingres, Informix, Teradata, Vertica all support transactional DDL
- nycdotnet 6y agoSQL Server partially supports it - indexes for example are not versioned which causes snapshot transactions to fail if someone rebuilds an index - even if it’s not in scope of the snapshot.
- throwaway_pdp09 6y agoThat doesn't sound right by my experience, Could you give a link please? I'm concerned I've not heard of this. Probably because it's MVCC/snapshot isolation which I'm not familiar with.
- diroussel 6y agoMSSQL can roll back DDL. But the DDL changes are not fully transactional, as other trans sacri one can see new columns and tables before the DDL transacrion commits. Postgres gets it right. DDL is contained in the transacrion until commit.
- throwaway_pdp09 6y agoThat sounds like a transaction isolation level being set too low. Really sounds like it.