注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL: How to execute a sql script in a single transaction ?  

2012-09-09 11:11:05|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

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

    接下来会分两种场景进行演示:


场景一:普通的调用脚本方式
--1.1 创建测试表

 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

 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

 [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 测试

 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;

 mydb=> truncate table test_single;
TRUNCATE TABLE

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

   

--2.2 以事务方式执行脚本

[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 再次查询测试

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

     备注:再查询表 test_single 时,无数据,说明以上的脚本执行的四条 INSERT 已经回滚了。
  

       在 PostgreSQL 中,默认是 autocommit 语句,即执行一条SQL 成功后,默认是 commit 的,这和 Oracle 完全不一样,
在  oracle 中,在执行命令后,可以执行 commit/rollback 命令,提交或者回滚; 但在 pg 中,可以使用 begin/end 来
完成,例如:

 mydb=> begin;
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 的朋友可能不习惯;另外也可以用这种方式以事务方式调用脚本,
             代码如下:

 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.
 

  评论这张
 
阅读(21000)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016