在数据库维护过程中，对生产数据库跑 SQL 脚本是再平常不过的操作了，比如业务升级，需要向某张表插入一批数据，或者更改好几张表的数据，为了保证数据的一致性，我们有必要使用事务，比如有一张表需要一次性插入5000条数据，我们希望这个操作要么全部执行成功，如有失败则全部回滚，在 Oracle 的 SQLPLUS 中可以使用 commit 命令达到这个需求，在 PostgreSQL 中也可以实现这个需求，尽管它的方式和 Orace 有一定区别。接下来会分两种场景进行演示：

场景一: 普通调用脚本方式

1.1 创建测试表

1
2
3
4
5
6
7
8
9
10
11
12

mydb=> create table test_single (id int4 primary key, name varchar(32));  
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_single_pkey" for table "test_single"  
CREATE TABLE

mydb=> /d test_single  
 Table "mydb.test_single"  
Column | Type | Modifiers  
--------+-----------------------+-----------  
id | integer | not null  
name | character varying(32) |  
Indexes:  
 "test_single_pkey" PRIMARY KEY, btree (id)

1.2 编写脚本 insert_1.sql

1
2
3
4
5

insert into test_single (id ,name ) values (1,'a');  
insert into test_single (id ,name ) values (2,'b');  
test_error1;  
insert into test_single (id ,name ) values (3,'b');  
insert into test_single (id ,name ) values (4,'b');

备注：脚本 insert_1.sql 有五条 sql，前两条向 test_single 插入两条记录，第三行 “test_error1;“ 为错误命令，为了测试。

1.3 执行脚本 insert_1.sql

1
2
3
4
5
6
7
8

[postgres@pgb tf]$ psql -d mydb -U mydb -f insert_1.sql  
INSERT 0 1  
INSERT 0 1  
psql:insert_1.sql:3: ERROR: syntax error at or near "test_error1"  
LINE 1: test_error1;  
 ^  
INSERT 0 1  
INSERT 0 1

备注：命令在执行到第三行时报错，报语法错误，这正是我们预期的。

1.4 查询表 test_single 测试

1
2
3
4
5
6
7
8

mydb=> select * From test_single;  
id | name  
----+------  
 1 | a  
 2 | b  
 3 | b  
 4 | b  
(4 rows)

备注：执行完 1.2 的调用脚本后，再次回到 psql 客户端查看到表 test_single 数据，发现四条数据都已进去了，说明在默认方式下，使用psql 的 “-f” 参数调用脚本时，当遇到 ERROR 时会继续往下执行，在很多情况下，这是我们所不希望看到的，我们希望发现错误并且修正它，并且所有操作都 rollback，接下来看第二个场景。

场景二: 事务方式调用脚本

2.1重新测试把，先清空原表 test_single;

1
2
3
4
5
6
7

mydb=> truncate table test_single;  
TRUNCATE TABLE

mydb=> select * From test_single;  
id | name  
----+------  
(0 rows)

2.2 以事务方式执行脚本

1
2
3
4
5
6
7
8

[postgres@pgb tf]$ psql -d mydb -U mydb single-transaction-f insert_1.sql  
INSERT 0 1  
INSERT 0 1  
psql:insert_1.sql:3: ERROR: syntax error at or near "test_error1"  
LINE 1: test_error1;  
 ^  
psql:insert_1.sql:4: ERROR: current transaction is aborted, commands ignored until end of transaction block  
psql:insert_1.sql:5: ERROR: current transaction is aborted, commands ignored until end of transaction block

备注：这个脚本加了参数 “–single-transaction” ，表示以事务方式调用脚本，其本质是在执行脚本时，会默认将命令“BEGIN/COMMIT ” 包裹脚本，脚本中遇到ERROR时，不会继续往下执行，并且所有操作都回滚，关于这个命令的详细信息参考本文末尾的附录部分。

2.3 再次查询测试

1
2
3
4

mydb=> select * From test_single;  
id | name  
----+------  
(0 rows)

备注：再查询表 test_single 时，无数据，说明以上的脚本执行的四条 INSERT 已经回滚了。在 PostgreSQL 中，默认是 autocommit ，即执行一条SQL 成功后，默认是 commit 的，这和 Oracle 完全不一样，在 oracle 中，在执行命令后，可以执行 commit/rollback 命令，提交或者回滚; 但在 pg 中，可以使用 begin/end 来 完成，例如：

1
2
3
4
5
6
7
8
9

mydb=> begin;  
mydb=> select clock_timestamp();  
 clock_timestamp  
-------------------------------  
2012-09-09 10:48:20.480879+08  
(1 row)  
mydb=> sql ...  
mydb=> sql ..  
mydb=> end;

备注：但在 PostgreSQL 中，不可以中途 commit ，全部操作要么全执行成功，出现任务错误则全部回滚，而不像 Oracle 那样可以中途 commit，在这点上，习惯 Oracle 的朋友可能不习惯；另外也可以用这种方式以事务方式调用脚本，代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

mydb=> begin;  
BEGIN  

mydb=> /i /home/postgres/script/tf/insert_1.sql  
INSERT 0 1  
INSERT 0 1  
psql:/home/postgres/script/tf/insert_1.sql:3: ERROR: syntax error at or near "test_error1"  
LINE 1: test_error1;  
 ^  
psql:/home/postgres/script/tf/insert_1.sql:4: ERROR: current transaction is aborted, commands ignored until end of transaction block  
psql:/home/postgres/script/tf/insert_1.sql:5: ERROR: current transaction is aborted, commands ignored until end of transaction block  

mydb=> select * from test_single;  
ERROR: current transaction is aborted, commands ignored until end of transaction block  
mydb=> end;  
ROLLBACK

备注：/i 表示调用 SQL 脚本。

附: psql 选项说明

-1
–single-transaction
When psql executes a script with the -f option, adding this option wraps BEGIN/COMMIT around the script to execute it as a single transaction. This ensures that either all the commands complete successfully, or no changes are applied.If the script itself uses BEGIN, COMMIT, or ROLLBACK, this option will not have the desired effects. Also, if the script contains any command that cannot be executed inside a transaction block, specifying this option will cause that command (and hence the whole transaction) to fail.