Russ Brown <pickscrape@xxxxxxxxx> 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
