drop table test1;
create table test1(a int);
CREATE PROCEDURE transaction_test1()
LANGUAGE plpgsql
AS $$
BEGIN
FOR i IN 0..9 LOOP
INSERT INTO test1 (a) VALUES (i);
IF i % 2 = 0 THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
END LOOP;
END;
$$;
CALL transaction_test1();
-- 结果
select * from test1;
a
---
0
2
4
6
8
drop table test1;
create table test1(a int);
CREATE PROCEDURE transaction_test2()
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO test1 (a) VALUES (1);
INSERT INTO test1 (a) VALUES (2);
commit;
INSERT INTO test1 (a) VALUES (3);
rollback;
INSERT INTO test1 (a) VALUES (4);
commit;
END;
$$;
CALL transaction_test2();
-- 结果
select * from test1;
a
---
1
2
4
drop table test1;
create table test1(a int);
CREATE PROCEDURE transaction_test3()
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO test1 (a) VALUES (1);
INSERT INTO test1 (a) VALUES (2);
commit;
INSERT INTO test1 (a) VALUES (3);
rollback;
raise division_by_zero;
END;
$$;
CALL transaction_test3();
-- 结果
select * from test1;
a
---
1
2
4
drop table test1;
create table test1(a int);
CREATE PROCEDURE transaction_test4()
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO test1 (a) VALUES (1);
INSERT INTO test1 (a) VALUES (2);
INSERT INTO test1 (a) VALUES (3);
raise division_by_zero;
END;
$$;
CALL transaction_test4();
-- 结果
select * from test1;
a
---
1
2
4
对于事务系统来说,内层函数、外层函数都在一个事务中,内层提交就等于把事务提交了,所以外层数据也在。
drop table test1;
create table test1(a int);
CREATE PROCEDURE transaction_test51()
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO test1 (a) VALUES (2);
commit;
END;
$$;
CREATE PROCEDURE transaction_test5()
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO test1 (a) VALUES (1);
call transaction_test51();
rollback;
END;
$$;
CALL transaction_test5();
-- 结果
select * from test1;
a
---
1
2
4
drop table test1;
create table test1(a int);
CREATE PROCEDURE transaction_test61()
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO test1 (a) VALUES (2);
rollback;
END;
$$;
CREATE PROCEDURE transaction_test6()
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO test1 (a) VALUES (1);
call transaction_test61();
commit;
END;
$$;
CALL transaction_test6();
-- 结果
select * from test1;
a
---
drop table test1;
create table test1(a int);
CREATE or replace FUNCTION transaction_test71()
RETURNS BOOLEAN LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO test1 (a) VALUES (2);
commit;
return true;
END;
$$;
select transaction_test71();
-- 结果
postgres=# select transaction_test71();
ERROR: invalid transaction termination
CONTEXT: PL/pgSQL function transaction_test71() line 4 at COMMIT
drop table tf1;
create table tf1(c1 int, c2 int, c3 varchar(32), c4 varchar(32), c5 int);
insert into tf1 values(1,1000, 'China','Dalian', 23000);
insert into tf1 values(2,4000, 'Janpan', 'Tokio', 45000);
insert into tf1 values(3,1500, 'China', 'Xian', 25000);
insert into tf1 values(4,300, 'China', 'Changsha', 24000);
insert into tf1 values(5,400,'USA','New York', 35000);
insert into tf1 values(6,5000, 'USA', 'Bostom', 15000);
postgres=# select * from tf1;
c1 | c2 | c3 | c4 | c5
----+------+--------+----------+-------
1 | 1000 | China | Dalian | 23000
2 | 4000 | Janpan | Tokio | 45000
3 | 1500 | China | Xian | 25000
4 | 300 | China | Changsha | 24000
5 | 400 | USA | New York | 35000
6 | 5000 | USA | Bostom | 15000
CREATE OR REPLACE PROCEDURE transaction_cur_test1()
AS $$
DECLARE
curs1 refcursor;
curs2 CURSOR FOR SELECT c1 FROM tf1;
curs3 CURSOR (key integer) FOR SELECT * FROM tf1 WHERE c1 > key;
x int;
y tf1%ROWTYPE;
BEGIN
open curs1 FOR SELECT * FROM tf1 WHERE c1 > 3;
RAISE NOTICE '================== curs1 =================';
fetch curs1 into y; RAISE NOTICE 'curs1 : %', y.c3;
fetch curs1 into y; RAISE NOTICE 'curs1 : %', y.c3;
commit;
RAISE NOTICE '------------------ commit------------------';
fetch curs1 into y; RAISE NOTICE 'curs1 : %', y.c3;
fetch curs1 into y; RAISE NOTICE 'curs1 : %', y.c3;
END;
$$ LANGUAGE plpgsql;
call transaction_cur_test1();
-- 结果
postgres=# call transaction_cur_test1();
NOTICE: ================== curs1 =================
NOTICE: curs1 : China
NOTICE: curs1 : USA
NOTICE: ---------------curs1 commit---------------
ERROR: cursor "<unnamed portal 5>" does not exist
CONTEXT: PL/pgSQL function transaction_cur_test1() line 16 at FETCH
drop table tf1;
create table tf1(c1 int, c2 int, c3 varchar(32), c4 varchar(32), c5 int);
insert into tf1 values(1,1000, 'China','Dalian', 23000);
insert into tf1 values(2,4000, 'Janpan', 'Tokio', 45000);
insert into tf1 values(3,1500, 'China', 'Xian', 25000);
insert into tf1 values(4,300, 'China', 'Changsha', 24000);
insert into tf1 values(5,400,'USA','New York', 35000);
insert into tf1 values(6,5000, 'USA', 'Bostom', 15000);
postgres=# select * from tf1;
c1 | c2 | c3 | c4 | c5
----+------+--------+----------+-------
1 | 1000 | China | Dalian | 23000
2 | 4000 | Janpan | Tokio | 45000
3 | 1500 | China | Xian | 25000
4 | 300 | China | Changsha | 24000
5 | 400 | USA | New York | 35000
6 | 5000 | USA | Bostom | 15000
CREATE OR REPLACE PROCEDURE transaction_cur_test2()
AS $$
DECLARE
curs1 refcursor;
curs2 CURSOR FOR SELECT c1 FROM tf1;
curs3 CURSOR (key integer) FOR SELECT * FROM tf1 WHERE c1 > key;
x int;
y tf1%ROWTYPE;
BEGIN
open curs2;
RAISE NOTICE '================== curs2 =================';
fetch curs2 into x; RAISE NOTICE 'curs2 : %', x;
fetch curs2 into x; RAISE NOTICE 'curs2 : %', x;
RAISE NOTICE '------------------ rollback --------------';
rollback;
fetch curs2 into x; RAISE NOTICE 'curs2 : %', x;
fetch curs2 into x; RAISE NOTICE 'curs2 : %', x;
END;
$$ LANGUAGE plpgsql;
call transaction_cur_test2();
-- 结果
postgres=# call transaction_cur_test2();
NOTICE: ================== curs2 =================
NOTICE: curs2 : 1
NOTICE: curs2 : 2
NOTICE: ------------------ rollback --------------
ERROR: cursor "curs2" does not exist
CONTEXT: PL/pgSQL function transaction_cur_test2() line 16 at FETCH
drop table tf1;
create table tf1(c1 int, c2 int, c3 varchar(32), c4 varchar(32), c5 int);
insert into tf1 values(1,1000, 'China','Dalian', 23000);
insert into tf1 values(2,4000, 'Janpan', 'Tokio', 45000);
insert into tf1 values(3,1500, 'China', 'Xian', 25000);
insert into tf1 values(4,300, 'China', 'Changsha', 24000);
insert into tf1 values(5,400,'USA','New York', 35000);
insert into tf1 values(6,5000, 'USA', 'Bostom', 15000);
postgres=# select * from tf1;
c1 | c2 | c3 | c4 | c5
----+------+--------+----------+-------
1 | 1000 | China | Dalian | 23000
2 | 4000 | Janpan | Tokio | 45000
3 | 1500 | China | Xian | 25000
4 | 300 | China | Changsha | 24000
5 | 400 | USA | New York | 35000
6 | 5000 | USA | Bostom | 15000
CREATE OR REPLACE PROCEDURE transaction_cur_test3()
AS $$
DECLARE
curs1 refcursor;
curs2 CURSOR FOR SELECT c1 FROM tf1;
curs3 CURSOR (key integer) FOR SELECT * FROM tf1 WHERE c1 > key;
x int;
y tf1%ROWTYPE;
BEGIN
open curs1 FOR SELECT * FROM tf1 WHERE c1 > 3;
RAISE NOTICE '================== curs1 =================';
fetch curs1 into y; RAISE NOTICE 'curs1 : %', y.c3;
fetch curs1 into y; RAISE NOTICE 'curs1 : %', y.c3;
commit;
RAISE NOTICE '------------------ commit------------------';
open curs1 FOR SELECT * FROM tf1 WHERE c1 > 3;
RAISE NOTICE '-------------- curs1 reopen----------------';
fetch curs1 into y; RAISE NOTICE 'curs1 : %', y.c3;
fetch curs1 into y; RAISE NOTICE 'curs1 : %', y.c3;
END;
$$ LANGUAGE plpgsql;
call transaction_cur_test3();
-- 结果
postgres=# call transaction_cur_test3();
NOTICE: ================== curs1 =================
NOTICE: curs1 : China
NOTICE: curs1 : USA
NOTICE: ------------------ commit------------------
NOTICE: -------------- curs1 reopen----------------
NOTICE: curs1 : China
NOTICE: curs1 : USA
CALL
drop table test1;
drop table test2;
create table test1(a int);
create table test2(x int);
insert into test2 values (1),(2);
CREATE PROCEDURE transaction_cur_test4()
LANGUAGE plpgsql
AS $$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT * FROM test2 ORDER BY x LOOP
INSERT INTO test1 (a) VALUES (r.x);
COMMIT;
END LOOP;
END;
$$;
CALL transaction_cur_test4();
-- 结果
postgres=# select * from test1;
a
---
1
2