PostgreSQL事务特性之ROLLBACK

最新推荐文章于 2025-06-20 15:41:15 发布

weixin_33701617

最新推荐文章于 2025-06-20 15:41:15 发布

阅读量1.2k

点赞数 1

CC 4.0 BY-SA版权

文章标签：数据库 python

原文链接：https://my.oschina.net/aven92/blog/497695

本文详细介绍了在PostgreSQL数据库中如何使用回滚操作来撤销数据定义语言（DDL）和数据操纵语言（DML）的各种变更，包括创建、修改、删除表及索引等操作，同时展示了事务中插入、更新、删除数据的回滚方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

为什么80%的码农都做不了架构师？>>>

一、回滚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)

转载于:https://my.oschina.net/aven92/blog/497695