Re:Oracle vs. PostgreSQL – A Comment
by atombender on Saturday May 30, 2020 @12:48PM
(#23362480)
Very few databases support transactional DDL, meaning that you can do something like:
BEGIN;
DROP TABLE foo;
CREATE TABLE bar (t int);
ROLLBACK;
As far as I know, other than Postgres, only Sybase ASE, Informix, DB2 UDB, and Firebird support transaction DDL.
> 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.
Re:Oracle vs. PostgreSQL – A Comment
by throwaway_pdp09 on Saturday May 30, 2020 @05:42PM
(#23365025)
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.
DDL is transactional in SQLite.
Re:Oracle vs. PostgreSQL – A Comment
by throwaway_pdp09 on Saturday May 30, 2020 @01:23PM
(#23362819)
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.
Re:Oracle vs. PostgreSQL – A Comment
by throwaway_pdp09 on Saturday May 30, 2020 @01:24PM
(#23362826)
That sounds like a transaction isolation level being set too low. Really sounds like it.
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.