postgre存储过程如何写transaction

[kimoliang]

postgresql:

CREATE OR REPLACE FUNCTION test()
RETURNS void AS
$body$
DECLARE
BEGIN
insert into A(aa) values('aa');
insert into B(bb) values('bb');
END
$body$
LANGUAGE plpgsql;


要把两条insert语句放到一个transaction里，应该如何写？

[kimoliang]

[Quote=引用 1 楼 zuoxingyu 的回复:]

CREATE OR REPLACE FUNCTION test()
RETURNS void AS
$body$
DECLARE
BEGIN
START TRANSACTION;
insert into A(aa) values('aa');
insert into B(bb) values('bb');
COMMIT;
END
$body$
LANGU……
[/Quote]

报错：
ERROR: SPI_execute_plan_with_paramlist failed executing query "START TRANSACTION": SPI_ERROR_TRANSACTION
CONTEXT: PL/pgSQL function "test" line 3 at SQL statement

********** 错误 **********

ERROR: SPI_execute_plan_with_paramlist failed executing query "START TRANSACTION": SPI_ERROR_TRANSACTION
SQL 状态: XX000
上下文:PL/pgSQL function "test" line 3 at SQL statement

drop table if exists A;
drop table if exists B;

create table A
(
a text
);

create table B
(
b text
);

CREATE OR REPLACE FUNCTION test(aa text,bb text)
RETURNS void AS   
$body$
DECLARE
BEGIN
  START TRANSACTION;
  insert into A(a) values($1);
  insert into B(b) values($2);
  COMMIT;
END
$body$
LANGUAGE plpgsql;

select test('ss','fff');

[trainee]

postgresql在function不能用start transaction, commit 或rollback

因为它已经设定：所有的function里的语句只能含在一个transaction之中。也必然在一个transaction之中。

它是含在调用function语句的transaction之中。

所以不用写start transaction 就能满足你的要求

[zuoxingyu]

CREATE OR REPLACE FUNCTION test()
RETURNS void AS
$body$
DECLARE
BEGIN
START TRANSACTION;
insert into A(aa) values('aa');
insert into B(bb) values('bb');
COMMIT;
END
$body$
LANGUAGE plpgsql;

[trainee]

你的第二个例子，其实是个误解
你的客户端软件在这三条语句执行前隐藏加了个start transaction.

假如你用psql运行这3条sql, 这3条语句每一条，都是一个transaction, 除非你手动加begin或start transaction

[trainee]

postgresql没有autocommit开关，以前曾经有，后来取消了，默认都是autocommit=1，也即是每执行一条语句就是一个事务，除非手工用begin或start transaction开启一个事务。

这种方式最简单，同时意味着不容易混淆。

一个事务的最小单位是连接层的一条执行语句，无法细到function层的语句之中，所以function里面的所有语句只能含在一个事务里。

以psql运行2个例子为例
1、
select functionA() --- 假如functionA（）里有语句执行不成功，全部rollback

2、
start transaction
insert into ()...
select functionA()
insert into ()
commit
假如执行到第3步，也就是functionA的时候， functionA出错，接下去的第4、5个语句是无法执行下去，它只允许执行rollback来结束这个事务。

[kimoliang]

[Quote=引用 3 楼 trainee 的回复:]

postgresql在function不能用start transaction, commit 或rollback

因为它已经设定：所有的function里的语句只能含在一个transaction之中。也必然在一个transaction之中。

它是含在调用function语句的transaction之中。

所以不用写start transaction 就能满足你的要求


……
[/Quote]


我试了一下：

table A
(
a int
)
table B
(
b int
)

function test里直接写：
insert into A values(1);
insert into B values("ttt");
insert into B values(2);

然后select test();
没有数据会被插入，按你的说法没有问题；

在sql脚本里直接写：
insert into A values(1);
insert into B values("ttt");
insert into B values(2);

然后执行，也没有数据被插入，也就是说在以上三句也是作为一个transaction的。

那么，postgre的transaction在哪里会用到呢？

[kimoliang]

好！多谢train

test query:

START TRANSACTION;
  insert into test values(99);
COMMIT;

insert into test values(4);
insert into test values(5);

START TRANSACTION;
  insert into test values(99);
COMMIT;

insert into test1 values(6);
insert into test values('ss');
insert into test1 values(4);
insert into test1 values(4);
insert into test values(4);

START TRANSACTION;
  insert into test values(96);
  insert into test1 values(96);
COMMIT;