Search Postgresql Archives

Re: Transactional DDL

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux