- 论坛徽章:
- 0
|
例八:
test=> begin;
BEGIN
test=> execute t_test1(41,42,43,44);
INSERT 0 4
test=> savepoint one;
SAVEPOINT
test=> execute t_test1(45,46,47,4;
INSERT 0 4
test=> select tableoid,ctid,cmin,cmax,xmin,xmax,* from tbl_test order by id;
tableoid | ctid | cmin | cmax | xmin | xmax | id
----------+--------+------+------+------+------+----
16388 | (0,1) | 0 | 0 | 668 | 0 | 1
16388 | (0,2) | 0 | 0 | 668 | 0 | 2
16388 | (0,3) | 0 | 0 | 668 | 0 | 3
16388 | (0,4) | 0 | 0 | 668 | 0 | 4
16388 | (0,5) | 0 | 0 | 669 | 0 | 5
16388 | (0,6) | 1 | 1 | 669 | 0 | 6
16388 | (0,7) | 2 | 2 | 669 | 0 | 7
16388 | (0, | 3 | 3 | 669 | 0 | 8
16388 | (0,9) | 0 | 0 | 670 | 0 | 9
16388 | (0,10) | 0 | 0 | 670 | 0 | 10
16388 | (0,11) | 0 | 0 | 670 | 0 | 11
16388 | (0,12) | 0 | 0 | 670 | 0 | 12
16388 | (0,13) | 0 | 0 | 671 | 0 | 13
16388 | (0,14) | 0 | 0 | 671 | 0 | 14
16388 | (0,15) | 0 | 0 | 671 | 0 | 15
16388 | (0,16) | 0 | 0 | 671 | 0 | 16
16388 | (0,17) | 0 | 0 | 672 | 0 | 17
16388 | (0,1 | 1 | 1 | 672 | 0 | 18
16388 | (0,19) | 2 | 2 | 672 | 0 | 19
16388 | (0,20) | 3 | 3 | 672 | 0 | 20
16388 | (0,21) | 0 | 0 | 673 | 0 | 21
16388 | (0,22) | 0 | 0 | 673 | 0 | 22
16388 | (0,23) | 0 | 0 | 673 | 0 | 23
16388 | (0,24) | 0 | 0 | 673 | 0 | 24
16388 | (0,27) | 0 | 0 | 675 | 0 | 25
16388 | (0,2 | 1 | 1 | 675 | 0 | 26
16388 | (0,31) | 4 | 4 | 677 | 0 | 27
16388 | (0,32) | 5 | 5 | 677 | 0 | 28
16388 | (0,33) | 0 | 0 | 678 | 0 | 29
16388 | (0,34) | 1 | 1 | 678 | 0 | 30
16388 | (0,25) | 4 | 4 | 680 | 0 | 31
16388 | (0,26) | 5 | 5 | 680 | 0 | 32
16388 | (0,29) | 0 | 0 | 681 | 0 | 33
16388 | (0,30) | 0 | 0 | 681 | 0 | 34
16388 | (0,35) | 0 | 0 | 681 | 0 | 35
16388 | (0,36) | 0 | 0 | 681 | 0 | 36
16388 | (0,37) | 1 | 1 | 681 | 0 | 37
16388 | (0,3 | 1 | 1 | 681 | 0 | 38
16388 | (0,39) | 1 | 1 | 681 | 0 | 39
16388 | (0,40) | 1 | 1 | 681 | 0 | 40
16388 | (0,41) | 0 | 0 | 682 | 0 | 41
16388 | (0,42) | 0 | 0 | 682 | 0 | 42
16388 | (0,43) | 0 | 0 | 682 | 0 | 43
16388 | (0,44) | 0 | 0 | 682 | 0 | 44
16388 | (0,45) | 1 | 1 | 683 | 0 | 45
16388 | (0,46) | 1 | 1 | 683 | 0 | 46
16388 | (0,47) | 1 | 1 | 683 | 0 | 47
16388 | (0,4 | 1 | 1 | 683 | 0 | 48
(48 rows)
test=> rollback to one;
ROLLBACK
在第二个SESSION执行:
test=> vacuum tbl_test;
VACUUM
回到第一个SESSION执行:
test=> commit;
COMMIT
test=> select tableoid,ctid,cmin,cmax,xmin,xmax,* from tbl_test order by id;
tableoid | ctid | cmin | cmax | xmin | xmax | id
----------+--------+------+------+------+------+----
16388 | (0,1) | 0 | 0 | 668 | 0 | 1
16388 | (0,2) | 0 | 0 | 668 | 0 | 2
16388 | (0,3) | 0 | 0 | 668 | 0 | 3
16388 | (0,4) | 0 | 0 | 668 | 0 | 4
16388 | (0,5) | 0 | 0 | 669 | 0 | 5
16388 | (0,6) | 1 | 1 | 669 | 0 | 6
16388 | (0,7) | 2 | 2 | 669 | 0 | 7
16388 | (0, | 3 | 3 | 669 | 0 | 8
16388 | (0,9) | 0 | 0 | 670 | 0 | 9
16388 | (0,10) | 0 | 0 | 670 | 0 | 10
16388 | (0,11) | 0 | 0 | 670 | 0 | 11
16388 | (0,12) | 0 | 0 | 670 | 0 | 12
16388 | (0,13) | 0 | 0 | 671 | 0 | 13
16388 | (0,14) | 0 | 0 | 671 | 0 | 14
16388 | (0,15) | 0 | 0 | 671 | 0 | 15
16388 | (0,16) | 0 | 0 | 671 | 0 | 16
16388 | (0,17) | 0 | 0 | 672 | 0 | 17
16388 | (0,1 | 1 | 1 | 672 | 0 | 18
16388 | (0,19) | 2 | 2 | 672 | 0 | 19
16388 | (0,20) | 3 | 3 | 672 | 0 | 20
16388 | (0,21) | 0 | 0 | 673 | 0 | 21
16388 | (0,22) | 0 | 0 | 673 | 0 | 22
16388 | (0,23) | 0 | 0 | 673 | 0 | 23
16388 | (0,24) | 0 | 0 | 673 | 0 | 24
16388 | (0,27) | 0 | 0 | 675 | 0 | 25
16388 | (0,2 | 1 | 1 | 675 | 0 | 26
16388 | (0,31) | 4 | 4 | 677 | 0 | 27
16388 | (0,32) | 5 | 5 | 677 | 0 | 28
16388 | (0,33) | 0 | 0 | 678 | 0 | 29
16388 | (0,34) | 1 | 1 | 678 | 0 | 30
16388 | (0,25) | 4 | 4 | 680 | 0 | 31
16388 | (0,26) | 5 | 5 | 680 | 0 | 32
16388 | (0,29) | 0 | 0 | 681 | 0 | 33
16388 | (0,30) | 0 | 0 | 681 | 0 | 34
16388 | (0,35) | 0 | 0 | 681 | 0 | 35
16388 | (0,36) | 0 | 0 | 681 | 0 | 36
16388 | (0,37) | 1 | 1 | 681 | 0 | 37
16388 | (0,3 | 1 | 1 | 681 | 0 | 38
16388 | (0,39) | 1 | 1 | 681 | 0 | 39
16388 | (0,40) | 1 | 1 | 681 | 0 | 40
16388 | (0,41) | 0 | 0 | 682 | 0 | 41
16388 | (0,42) | 0 | 0 | 682 | 0 | 42
16388 | (0,43) | 0 | 0 | 682 | 0 | 43
16388 | (0,44) | 0 | 0 | 682 | 0 | 44
(44 rows)
从结果上看,只要是CMIN,CMAX值一样的都被回滚掉了.VACUUM并不会影响一个事务中的CMIN CMAX值。
例九:
test=> begin;
BEGIN
test=> insert into tbl_Test (id) values (29);
INSERT 0 1
test=> insert into tbl_Test (id) values (30);
INSERT 0 1
test=> savepoint one;
SAVEPOINT
test=> insert into tbl_Test (id) values (31);
INSERT 0 1
test=> insert into tbl_Test (id) values (32);
INSERT 0 1
test=> select tableoid,ctid,cmin,cmax,xmin,xmax,* from tbl_test order by id;
tableoid | ctid | cmin | cmax | xmin | xmax | id
----------+--------+------+------+------+------+----
16388 | (0,1) | 0 | 0 | 668 | 0 | 1
16388 | (0,2) | 0 | 0 | 668 | 0 | 2
16388 | (0,3) | 0 | 0 | 668 | 0 | 3
16388 | (0,4) | 0 | 0 | 668 | 0 | 4
16388 | (0,5) | 0 | 0 | 669 | 0 | 5
16388 | (0,6) | 1 | 1 | 669 | 0 | 6
16388 | (0,7) | 2 | 2 | 669 | 0 | 7
16388 | (0,8) | 3 | 3 | 669 | 0 | 8
16388 | (0,9) | 0 | 0 | 670 | 0 | 9
16388 | (0,10) | 0 | 0 | 670 | 0 | 10
16388 | (0,11) | 0 | 0 | 670 | 0 | 11
16388 | (0,12) | 0 | 0 | 670 | 0 | 12
16388 | (0,13) | 0 | 0 | 671 | 0 | 13
16388 | (0,14) | 0 | 0 | 671 | 0 | 14
16388 | (0,15) | 0 | 0 | 671 | 0 | 15
16388 | (0,16) | 0 | 0 | 671 | 0 | 16
16388 | (0,17) | 0 | 0 | 672 | 0 | 17
16388 | (0,18) | 1 | 1 | 672 | 0 | 18
16388 | (0,19) | 2 | 2 | 672 | 0 | 19
16388 | (0,20) | 3 | 3 | 672 | 0 | 20
16388 | (0,21) | 0 | 0 | 673 | 0 | 21
16388 | (0,22) | 0 | 0 | 673 | 0 | 22
16388 | (0,23) | 0 | 0 | 673 | 0 | 23
16388 | (0,24) | 0 | 0 | 673 | 0 | 24
16388 | (0,27) | 0 | 0 | 675 | 0 | 25
16388 | (0,28) | 1 | 1 | 675 | 0 | 26
16388 | (0,31) | 4 | 4 | 677 | 0 | 27
16388 | (0,32) | 5 | 5 | 677 | 0 | 28
16388 | (0,33) | 0 | 0 | 678 | 0 | 29
16388 | (0,34) | 1 | 1 | 678 | 0 | 30
16388 | (0,35) | 2 | 2 | 679 | 0 | 31
16388 | (0,36) | 3 | 3 | 679 | 0 | 32
(32 rows)
test=> rollback to one;
ROLLBACK
打开第二个SESSION执行:
test=> vacuum tbl_test;
VACUUM
回到第一个SESSION执行:
test=> insert into tbl_Test (id) values (31);
INSERT 0 1
test=> insert into tbl_Test (id) values (32);
INSERT 0 1
test=> select tableoid,ctid,cmin,cmax,xmin,xmax,* from tbl_test order by id;
tableoid | ctid | cmin | cmax | xmin | xmax | id
----------+--------+------+------+------+------+----
16388 | (0,1) | 0 | 0 | 668 | 0 | 1
16388 | (0,2) | 0 | 0 | 668 | 0 | 2
16388 | (0,3) | 0 | 0 | 668 | 0 | 3
16388 | (0,4) | 0 | 0 | 668 | 0 | 4
16388 | (0,5) | 0 | 0 | 669 | 0 | 5
16388 | (0,6) | 1 | 1 | 669 | 0 | 6
16388 | (0,7) | 2 | 2 | 669 | 0 | 7
16388 | (0,8) | 3 | 3 | 669 | 0 | 8
16388 | (0,9) | 0 | 0 | 670 | 0 | 9
16388 | (0,10) | 0 | 0 | 670 | 0 | 10
16388 | (0,11) | 0 | 0 | 670 | 0 | 11
16388 | (0,12) | 0 | 0 | 670 | 0 | 12
16388 | (0,13) | 0 | 0 | 671 | 0 | 13
16388 | (0,14) | 0 | 0 | 671 | 0 | 14
16388 | (0,15) | 0 | 0 | 671 | 0 | 15
16388 | (0,16) | 0 | 0 | 671 | 0 | 16
16388 | (0,17) | 0 | 0 | 672 | 0 | 17
16388 | (0,18) | 1 | 1 | 672 | 0 | 18
16388 | (0,19) | 2 | 2 | 672 | 0 | 19
16388 | (0,20) | 3 | 3 | 672 | 0 | 20
16388 | (0,21) | 0 | 0 | 673 | 0 | 21
16388 | (0,22) | 0 | 0 | 673 | 0 | 22
16388 | (0,23) | 0 | 0 | 673 | 0 | 23
16388 | (0,24) | 0 | 0 | 673 | 0 | 24
16388 | (0,27) | 0 | 0 | 675 | 0 | 25
16388 | (0,28) | 1 | 1 | 675 | 0 | 26
16388 | (0,31) | 4 | 4 | 677 | 0 | 27
16388 | (0,32) | 5 | 5 | 677 | 0 | 28
16388 | (0,33) | 0 | 0 | 678 | 0 | 29
16388 | (0,34) | 1 | 1 | 678 | 0 | 30
16388 | (0,25) | 4 | 4 | 680 | 0 | 31
16388 | (0,26) | 5 | 5 | 680 | 0 | 32
(32 rows)
test=> commit;
从结果上看,只要是CMIN,CMAX值一样的都被回滚掉了.VACUUM并不会影响一个事务中的CMIN CMAX值,VACUUM只是更新了FSM信息,所以VACUUM后插入的记录CTID使用25,26的PAGE位置存储。
总结:
BATCH模式可以通过如下手段实施:
insert into tbl_test (id) values (v_1),(v_2),(v_3),.....................(v_n);
insert into tbl_test (id) select v from other_table;
insert into tbl_test (id) select v_1 union all select v_2 union all select v_3 union all ..............;
prepare t_test1(int8,int8,int8,int8,...................) as insert into tbl_test (id) values ($1),($2),($3),($4),.........................;
execute t_test1(v_1,v_2,v_3,v_4,......................); |
|