PostgreSQL支持Transactional DDL吗

小半 • 2022年10月26日下午1:41 • 其他分类 • 阅读 343

什么是Transactional DDL?

Transactional(事务)在关系型数据库是指一组SQL语句，要么提交，要么全部回滚。事务中包含的语句通常是DML语句，如INSERT、UPDATE、DELETE等。但是对于DDL语句呢？是否可以在事务中包含诸如CREATE、ALTER、DROP等DDL命令？

所谓Transactional DDL就是我们可以把ddl放到事务中，做到事务中的ddl语句要么全部提交，要么全部回滚。

PG是否支持Transactional DDL?

看个pg的例子

postgres=# begin;
BEGIN
postgres=*# create table a_test(id int);
CREATE TABLE
postgres=*# insert into a_test values(1);
INSERT 0 1
postgres=*# rollback;
ROLLBACK
postgres=# select * from a_test;
ERROR:  relation "a_test" does not exist
LINE 1: select * from a_test;
                      ^
postgres=#

可见，在postgresql中，是支持transactional ddl的，在上例中，create table语句被回滚掉了。

并不是所有数据库都支持Transactional ddl，比如mysql。

看个mysql的例子：

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

mysql>  create table a_test (id int);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into a_test values(1);
Query OK, 1 row affected (0.01 sec)

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

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

可以看到mysql这个例子里，不仅create语句没有回滚掉，insert语句也没有回滚掉。这是因为：在mysql中，当执行ddl语句时，会隐式地将当前会话的事务进行一次commit操作。所以我们应该严格地将DDL和DML完全分开，不能混合在一起执行。

一些特例

需要注意的是在pg中并不是所有的ddl都支持Transactional ddl。比如CREATE INDEX CONCURRENTLY、CREATE DATABAE 、CREATE TABLESPACE等等。

postgres=# begin
postgres-# ;
BEGIN
postgres=*# CREATE INDEX CONCURRENTLY idx_id ON a_test (id);
ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block