Re: Transactional DDL

Russ Brown <pickscrape(at)gmail(dot)com> writes:
> Harpreet Dhaliwal wrote:
>> Whats so different in postgresql then?

> Try doing the same test in MySQL (using InnoDB so you get a supposedly
> ACID compliant table type).

> Or even in Oracle.

Examples (using mysql 5.0.40, reasonably current):

mysql> create table t1 (f1 int) engine = innodb;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+
| f1 |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+
| f1 |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+------+
| f1 |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

[ okay, so we can roll back an INSERT properly ]

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist

[ oops, DROP TABLE isn't transactional ]

mysql> create table t1 (f1 int) engine = innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(2);
Query OK, 1 row affected (0.00 sec)

mysql> create table t2 (f2 int) engine = innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t2;
Empty set (0.00 sec)

[ so CREATE TABLE isn't transactional, and what's more, now
the INSERT wasn't either: ]

mysql> select * from t1;
+------+
| f1 |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)

So it appears that mysql works just like Oracle on this point:
a DDL operation forces an immediate COMMIT.

regards, tom lane