一、回滚DDL
1. 回滚表的创建
postgres=# begin;
BEGIN
postgres=# create table test (id int, name text);
CREATE TABLE
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | test | table | postgres
(1 row)
postgres=# rollback ;
ROLLBACK
postgres=# \dt
No relations found.
2. 回滚表结构的修改
postgres=# create table test (id int, name text);
CREATE TABLE
postgres=# begin ;
BEGIN
postgres=# alter table test add column score int;
ALTER TABLE
postgres=# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
name | text |
score | integer |
postgres=# rollback ;
ROLLBACK
postgres=# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
name | text |
3. 回滚表的删除
postgres=# begin;
BEGIN
postgres=# drop table test ;
DROP TABLE
postgres=# \dt
No relations found.
postgres=# rollback ;
ROLLBACK
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | test | table | postgres
(1 row)
4. 回滚表的清空
postgres=# insert into test values (1, 'a');
INSERT 0 1
postgres=# select * from test ;
id | name
----+------
1 | a
(1 row)
postgres=# begin ;
BEGIN
postgres=# truncate test ;
TRUNCATE TABLE
postgres=# select * from test ;
id | name
----+------
(0 rows)
postgres=# rollback ;
ROLLBACK
postgres=# select * from test ;
id | name
----+------
1 | a
(1 row)
5. 回滚索引的创建
postgres=# begin ;
BEGIN
postgres=# create index on test (id);
CREATE INDEX
postgres=# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
name | text |
Indexes:
"test_id_idx" btree (id)
postgres=# rollback ;
ROLLBACK
postgres=# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
name | text |
6. 回滚索引的删除
postgres=# create index on test (id);
CREATE INDEX
postgres=# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
name | text |
Indexes:
"test_id_idx" btree (id)
postgres=# begin ;
BEGIN
postgres=# drop index test_id_idx ;
DROP INDEX
postgres=# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
name | text |
postgres=# rollback ;
ROLLBACK
postgres=# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
name | text |
Indexes:
"test_id_idx" btree (id)
注意如果索引的创建和删除加了CONCURRENTLY，则不能放在事务里
postgres=# drop index test_id_idx ;
DROP INDEX
postgres=# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
name | text |
postgres=# begin ;
BEGIN
postgres=# CREATE INDEX CONCURRENTLY ON test (id);
ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block
postgres=# rollback ;
ROLLBACK
postgres=# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
name | text |
二、回滚DML
1. 回滚insert
postgres=# select * from test ;
id | name
----+------
1 | a
(1 row)
postgres=# begin ;
BEGIN
postgres=# insert into test values (2, 'b');
INSERT 0 1
postgres=# select * from test ;
id | name
----+------
1 | a
2 | b
(2 rows)
postgres=# rollback ;
ROLLBACK
postgres=# select * from test ;
id | name
----+------
1 | a
(1 row)
2. 回滚update
postgres=# select * from test ;
id | name
----+------
1 | a
(1 row)
postgres=# begin ;
BEGIN
postgres=# update test set id = 2 where id = 1;
UPDATE 1
postgres=# select * from test ;
id | name
----+------
2 | a
(1 row)
postgres=# rollback ;
ROLLBACK
postgres=# select * from test ;
id | name
----+------
1 | a
(1 row)
3. 回滚delete
postgres=# select * from test ;
id | name
----+------
1 | a
(1 row)
postgres=# begin ;
BEGIN
postgres=# delete from test where id = 1;
DELETE 1
postgres=# select * from test ;
id | name
----+------
(0 rows)
postgres=# rollback ;
ROLLBACK
postgres=# select * from test ;
id | name
----+------
1 | a
(1 row)
