Hacker News Reader

Comment by throwaway_pdp09 | Hacker News Reader

> 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.

View on HN

Very 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.

Well, https://www.mssqltips.com/sqlservertip/4591/ddl-commands-in-...

DDL is transactional in SQLite.

> Very few databases support transactional DDL

Postgres, SQL Server, Firebird, DB2, SQLite, Ingres, Informix, Teradata, Vertica all support transactional DDL

SQL 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.

That 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.

MSSQL 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.

That sounds like a transaction isolation level being set too low. Really sounds like it.

Reply on news.ycombinator.com