PG中的DDL支持回滚
简介
在Oracle或MySQL中，当执行到DDL语句时，会隐式的将当前回话的事务进行一次“COMMIT”操作，因此在MySQL或Oracle中执行DDL语句时，应该严格地将DDL和DML完全分开，不能混合在一起执行。
在PG中，多数DDL语句是可以被回滚的，但是有一些DDL语句例如CREATE INDEX CONCURRENTLY、CREATE DATABAE 、CREATE TABLESPACE等则不能回滚。
什么是Transactional DDL?
Transactional(事务)在关系型数据库是指一组SQL语句，要么提交，要么全部回滚。事务中包含的语句通常是DML语句，如INSERT、UPDATE、DELETE等。但是对于DDL语句呢？是否可以在事务中包含诸如CREATE、ALTER、DROP等DDL命令？
所谓Transactional DDL就是我们可以把ddl放到事务中，做到事务中的ddl语句要么全部提交，要么全部回滚。
Transactional DDL的好处
在进行一些模式升级等复杂工作时，可以利用此功能保护数据库。我们可以将所有更改都放入事务块中，确保它们都以原子方式应用，或者根本不应用。这大大降低了数据库因模式更改中的输入错误或其他此类错误而损坏数据库的可能性。
Oracle
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | SYS@LHR11G> SET TRANSACTION NAME 't1'; Transaction set. SYS@LHR11G> create table aa(id int); Table created. SYS@LHR11G> select * from aa; no rows selected SYS@LHR11G> insert into aa values(1); 1 row created. SYS@LHR11G> select * from aa; ID ---------- 1 SYS@LHR11G> rollback; Rollback complete. SYS@LHR11G> select * from aa; no rows selected |
在Oracle的一个事务中，DDL语句不会被回滚。
MySQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | MySQL [lhrdb]> begin; Query OK, 0 rows affected (0.05 sec) MySQL [lhrdb]> MySQL [lhrdb]> create table bb(id int); Query OK, 0 rows affected (0.11 sec) MySQL [lhrdb]> rollback; Query OK, 0 rows affected (0.06 sec) MySQL [lhrdb]> select * from bb; Empty set (0.08 sec) MySQL [lhrdb]> begin; Query OK, 0 rows affected (0.05 sec) MySQL [lhrdb]> MySQL [lhrdb]> MySQL [lhrdb]> create table cc(id int); Query OK, 0 rows affected (0.64 sec) MySQL [lhrdb]> insert into cc values(1); Query OK, 1 row affected (0.05 sec) MySQL [lhrdb]> select * from cc; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.05 sec) MySQL [lhrdb]> rollback; Query OK, 0 rows affected (0.05 sec) MySQL [lhrdb]> select * from cc; Empty set (0.05 sec) |
在MySQL的一个事务中，DDL语句不会被回滚。
PostgreSQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | postgres=# begin; BEGIN postgres=*# create table aa(id int); CREATE TABLE postgres=*# select * from aa; id ---- (0 rows) postgres=*# rollback; ROLLBACK postgres=# select * from aa; ERROR: relation "aa" does not exist LINE 1: select * from aa; ^ postgres=# begin; BEGIN postgres=*# create table bb(id int); CREATE TABLE postgres=*# insert into bb values(1); INSERT 0 1 postgres=*# commit; COMMIT postgres=# select * from bb; id ---- 1 (1 row) postgres=# begin; BEGIN postgres=*# truncate table bb; TRUNCATE TABLE postgres=*# rollback; ROLLBACK postgres=# select * from bb; id ---- 1 (1 row) postgres=# begin; BEGIN postgres=*# create database db1; ERROR: CREATE DATABASE cannot run inside a transaction block postgres=!# |
可以看到，在PG中，DDL语句可以进行回滚（示例测试了create table和truncate table），而create database不能在事务块中执行。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 | postgres@lhrpg13:~$ psql psql (13.8 (Debian 13.8-1.pgdg110+1)) Type "help" for help. postgres=# begin; BEGIN postgres=*# create sequence s1; CREATE SEQUENCE postgres=*# select nextval('s1'); nextval --------- 1 (1 row) postgres=*# select nextval('s1'); nextval --------- 2 (1 row) postgres=*# rollback; ROLLBACK postgres=# select currval('s1'); ERROR: relation "s1" does not exist LINE 1: select currval('s1'); ^ postgres=# create sequence s1; CREATE SEQUENCE postgres=# begin; BEGIN postgres=*# select nextval('s1'); nextval --------- 1 (1 row) postgres=*# select nextval('s1'); nextval --------- 2 (1 row) postgres=*# select currval('s1'); currval --------- 2 (1 row) postgres=*# rollback; ROLLBACK postgres=# select currval('s1'); currval --------- 2 (1 row) postgres=# |
可以看到，在PG中，创建序列语句create sequence可以回滚，但是序列的值更新后，不会被回滚，所以nextval和setval调用绝不会回滚！！
总结
1、transactional ddl是指可以把ddl放到事务中，做到事务中的ddl语句要么全部提交，要么全部回滚。
2、PG大部分ddl都支持Transactional ddl，除了一些CREATE INDEX CONCURRENTLY、CREATE DATABAE 、CREATE TABLESPACE等语句。
3、在begin中，如果是创建序列，那么会回滚掉；如果是nextval和setval，则不会回滚
4、Oracle和MySQL不支持DDL回滚。
人大金仓的数据库和PG一样，都可以进行DDL回滚！