Hacker News Reader

Comment by mmt | Hacker News Reader

I only wish I were so lucky as to have been spoiled by Postgres for so long.

My favorite quote from an experience DBA is "MySQL's biggest feature is that it's retarded." Sadly, as someone who actually likes technology and working with it, I can't possibly see that as a feature.

DDL Doesn’t Respect Transactions

My Oracle DBA friend tells me Oracle doesn't, either, not that this is any kind of excuse.

Maybe with the first-class replication in the 9.0 release, it will happen sooner rather than later.

I thought RDS wasn't so great on MySQL replication, either, or is that only if one wishes to do it off-AWS?

View on HN

MS SQL Server and Oracle use CREATE SCHEMA and ALTER SCHEMA for DDL transactions. I think it is the ANSI/ISO standard way to do it. But, also "The CREATE SCHEMA command does not support Oracle extensions to the ANSI CREATE TABLE and CREATE VIEW commands (for example, the STORAGE clause)."

I'm certainly no expert on standard SQL, but I suspect that's academic.

Since my knowledge of transactional DDL is PostgreSQL[1], which supports effectively everything, with no onerous locking, I now have the question: what's the difference? Was my DBA friend in error, or merely out of date?

[1] first-hand knowledge, anyway. My 3rd-hand knowledge of MSSQL is that it's more or less Sybase, which does fully support DDL inside a transaction.

Yes, MSSQL does support transactional DDL, via locking rather than versioning. Not sure how PostresSQL implements it. Anyone? Row versioning would be more concurrency-friendly, and arguably more "right".

I'd be interested if anyone took advantage of versioned transactional DDL to persist arbitrary data structures on the fly.

Postgres does it with versioning, as that's how everything is stored on disk.

As I've seen DDL outside of a transaction take much longer than inside one, it may use locking there.

Can you link to documentation of ALTER SCHEMA for Oracle that does transactional DDL?

I don't think it exists.

You are right. I think ALTER SCHEMA might not be in standard SQL.

Oracle DDL occurs as transaction per command.

CREATE TABLE A (b number); -- one transaction; INSERT INTO a(b) VALUES(1); CREATE TABLE c (d number); -- another transaction, commits the insert.

Oracle DDL doesn't respect transactions, but at least you can wrap it into an autonomous transaction so it won't commit your main transaction.

Said that, it's a great feature. It allows you to deploy your stored procs transactionally, instead of the locking nightmare that Oracle's model may result into.

Reply on news.ycombinator.com