4 ms·
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
by mmt 16y ago
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?
- briansmith 16y agoMS 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)."
- mmt 16y agoI'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.
- pradocchia 16y agoYes, 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.
- mmt 16y agoPostgres 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.
- ora600 16y agoCan you link to documentation of ALTER SCHEMA for Oracle that does transactional DDL? I don't think it exists.
- briansmith 16y agoYou are right. I think ALTER SCHEMA might not be in standard SQL.
- julius_geezer 16y agoOracle 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.
- samuel 16y agoOracle 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.