Digoal.zhou

Digoal.zhou

公益是一辈子的事, I'm digoal, Just do it.

batch insert in PostgreSQL

27 minute read

背景

今天在互联网上看到有人发表了这样的感慨，其中

“INSERT INTO my_table SELECT 1, ‘a’ UNION SELECT 2, ‘b’ UNION SELECT 3, ‘c’ UNION …”这句可能是笔误，

应该改成

“INSERT INTO my_table SELECT 1, ‘a’ UNION ALL SELECT 2, ‘b’ UNION ALL SELECT 3, ‘c’ UNION ALL…”

其他的都蛮对的。

You can use the INSERT INTO tbl <query> syntax to accelerate the speed of inserts by batching them together.   
  
For example...  
  
INSERT INTO my_table SELECT 1, 'a' UNION SELECT 2, 'b' UNION SELECT 3, 'c' UNION ...  
  
If you batch up many sets of values per INSERT statement and batch up multiple INSERT statements per transaction, you can achieve significantly faster insertion performance.   
  
I managed to achieve almost 8x faster inserts on a PostgreSQL 8.1 / Win2K installation by batching up 100 (small) using this technique.  
  
If anyone knows a faster/more elegant way to construct the row set than using SELECT..UNION then please let me know!

正文

下面来用例子分析一下:

首先要介绍几个PostgreSQL的系统列(就不翻译了,还是原味比较好):

tableoid  
  
The OID of the table containing this row. This column is particularly handy for queries that select from inheritance hierarchies,   
since without it, it's difficult to tell which individual table a row came from.   
The tableoid can be joined against the oid column of pg_class to obtain the table name.   
  
xmin  
  
The identity (transaction ID) of the inserting transaction for this row version. (A row version is an individual state of a row;   
each update of a row creates a new row version for the same logical row.)   
  
cmin  
  
The command identifier (starting at zero) within the inserting transaction.   
  
xmax  
  
The identity (transaction ID) of the deleting transaction, or zero for an undeleted row version.   
It is possible for this column to be nonzero in a visible row version.   
That usually indicates that the deleting transaction hasn't committed yet, or that an attempted deletion was rolled back.   
  
cmax  
  
The command identifier within the deleting transaction, or zero.   
  
ctid  
The physical location of the row version within its table. Note that although the ctid can be used to locate the row version very quickly,   
a row's ctid will change if it is updated or moved by VACUUM FULL. Therefore ctid is useless as a long-term row identifier.   
The OID, or even better a user-defined serial number, should be used to identify logical rows.

例一:

test=> \d tbl_test  
    Table "test.tbl_test"  
 Column |  Type  | Modifiers   
--------+--------+-----------  
 id     | bigint | not null  
Indexes:  
    "tbl_test_pkey" PRIMARY KEY, btree (id)  
  
   
test=> insert into tbl_Test (id) values (1),(2),(3),(4);  
INSERT 0 4  
test=> select ctid,cmin,cmax,xmin,xmax,* from tbl_test;  
 ctid  | cmin | cmax | xmin | xmax | id   
-------+------+------+------+------+----  
 (0,1) |    0 |    0 |  668 |    0 |  1  
 (0,2) |    0 |    0 |  668 |    0 |  2  
 (0,3) |    0 |    0 |  668 |    0 |  3  
 (0,4) |    0 |    0 |  668 |    0 |  4  
(4 rows)

从结果上看四条记录都是同一个事务,同一个CMIN，CMAX值。

例二：

test=> begin;  
BEGIN  
test=> insert into tbl_Test (id) values (5);  
INSERT 0 1  
test=> insert into tbl_Test (id) values (6);  
INSERT 0 1  
test=> insert into tbl_Test (id) values (7);  
INSERT 0 1  
test=> insert into tbl_Test (id) values (8);  
INSERT 0 1  
test=> commit;  
COMMIT  
test=> select ctid,cmin,cmax,xmin,xmax,* from tbl_test order by id;  
 ctid  | cmin | cmax | xmin | xmax | id   
-------+------+------+------+------+----  
 (0,1) |    0 |    0 |  668 |    0 |  1  
 (0,2) |    0 |    0 |  668 |    0 |  2  
 (0,3) |    0 |    0 |  668 |    0 |  3  
 (0,4) |    0 |    0 |  668 |    0 |  4  
 (0,5) |    0 |    0 |  669 |    0 |  5  
 (0,6) |    1 |    1 |  669 |    0 |  6  
 (0,7) |    2 |    2 |  669 |    0 |  7  
 (0,8) |    3 |    3 |  669 |    0 |  8  
(8 rows)

从结果上看四条记录都是同一个事务,但是每一次插入操作占用了一个CMIN，CMAX值。

例三：

test=> insert into tbl_Test (id) select 9 union all select 10 union all select 11 union all select 12;  
INSERT 0 4  
test=> select ctid,cmin,cmax,xmin,xmax,* from tbl_test order by id;  
  ctid  | cmin | cmax | xmin | xmax | id   
--------+------+------+------+------+----  
 (0,1)  |    0 |    0 |  668 |    0 |  1  
 (0,2)  |    0 |    0 |  668 |    0 |  2  
 (0,3)  |    0 |    0 |  668 |    0 |  3  
 (0,4)  |    0 |    0 |  668 |    0 |  4  
 (0,5)  |    0 |    0 |  669 |    0 |  5  
 (0,6)  |    1 |    1 |  669 |    0 |  6  
 (0,7)  |    2 |    2 |  669 |    0 |  7  
 (0,8)  |    3 |    3 |  669 |    0 |  8  
 (0,9)  |    0 |    0 |  670 |    0 |  9  
 (0,10) |    0 |    0 |  670 |    0 | 10  
 (0,11) |    0 |    0 |  670 |    0 | 11  
 (0,12) |    0 |    0 |  670 |    0 | 12  
(12 rows)

从结果上看四条记录都是同一个事务,同一个CMIN，CMAX值。

例四：

test=> insert into tbl_test (id) select generate_series(13,16);  
INSERT 0 4  
test=> select ctid,cmin,cmax,xmin,xmax,* from tbl_test order by id;  
  ctid  | cmin | cmax | xmin | xmax | id   
--------+------+------+------+------+----  
 (0,1)  |    0 |    0 |  668 |    0 |  1  
 (0,2)  |    0 |    0 |  668 |    0 |  2  
 (0,3)  |    0 |    0 |  668 |    0 |  3  
 (0,4)  |    0 |    0 |  668 |    0 |  4  
 (0,5)  |    0 |    0 |  669 |    0 |  5  
 (0,6)  |    1 |    1 |  669 |    0 |  6  
 (0,7)  |    2 |    2 |  669 |    0 |  7  
 (0,8)  |    3 |    3 |  669 |    0 |  8  
 (0,9)  |    0 |    0 |  670 |    0 |  9  
 (0,10) |    0 |    0 |  670 |    0 | 10  
 (0,11) |    0 |    0 |  670 |    0 | 11  
 (0,12) |    0 |    0 |  670 |    0 | 12  
 (0,13) |    0 |    0 |  671 |    0 | 13  
 (0,14) |    0 |    0 |  671 |    0 | 14  
 (0,15) |    0 |    0 |  671 |    0 | 15  
 (0,16) |    0 |    0 |  671 |    0 | 16  
(16 rows)

从结果上看四条记录都是同一个事务,同一个CMIN，CMAX值。

例五：

test=> prepare t_test(int8) as insert into tbl_test (id) values ($1);  
PREPARE  
  
   
test=> begin;execute t_test(17);  
BEGIN  
INSERT 0 1  
test=> execute t_test(18);  
INSERT 0 1  
test=> execute t_test(19);  
INSERT 0 1  
test=> execute t_test(20);  
INSERT 0 1  
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,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  
(20 rows)

从结果上看四条记录都是同一个事务,但是每一次插入操作占用了一个CMIN，CMAX值。

例六：

test=> prepare t_test1(int8,int8,int8,int8) as insert into tbl_test (id) values ($1),($2),($3),($4);  
PREPARE  
  
   
test=> begin;  
BEGIN  
test=> execute t_test1(21,22,23,24);  
INSERT 0 4  
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,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  
(24 rows)

从结果上看四条记录都是同一个事务,同一个CMIN，CMAX值。

例七：

test=> begin;  
BEGIN  
test=> insert into tbl_Test (id) values (25);  
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  
(25 rows)  
  
   
test=> insert into tbl_Test (id) values (26);  
INSERT 0 1  
test=> savepoint one;  
SAVEPOINT  
test=> insert into tbl_Test (id) values (27);  
INSERT 0 1  
test=> insert into tbl_Test (id) values (28);  
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,29) |    2 |    2 |  676 |    0 | 27  
    16388 | (0,30) |    3 |    3 |  676 |    0 | 28  
(28 rows)  
  
   
test=> rollback to one;  
ROLLBACK  
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  
(26 rows)  
  
   
test=> insert into tbl_Test (id) values (27);  
INSERT 0 1  
test=> insert into tbl_Test (id) values (28);  
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  
(28 rows)  
  
   
test=> commit;  
COMMIT

从结果上看每一个SAVEPOINT会产生一个新的子事物号，但是CMIN , CMAX值是连续的，不会在新事物重置。回滚到SAVEPOINT后，CMIN,CMAX值的产生和序列差不多，不能回滚，继续往前。

例八：

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,48);  
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,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  
    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,38) |    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,48) |    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,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  
    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,38) |    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模式可以通过如下手段实施:

1.

insert into tbl_test (id) values (v_1),(v_2),(v_3),…………………(v_n);

2.

insert into tbl_test (id) select v from other_table;

3.

insert into tbl_test (id) select v_1 union all select v_2 union all select v_3 union all …………..;

4.

prepare t_test1(int8,int8,int8,int8,……………….) as insert into tbl_test (id) values ($1),($2),($3),($4),…………………….;

then

execute t_test1(v_1,v_2,v_3,v_4,………………….);

Flag Counter

digoal’s 大量PostgreSQL文章入口

You May Also Enjoy

PostgreSQL(PPAS 兼容Oracle) 从零开始入门手册 - 珍藏版

17 minute read

背景 云数据库PPAS版，是阿里云与EnterpriseDB公司(简称EDB)合作基于PostgreSQL高度兼容Oracle语法的数据库服务，为用户提供易于操作的迁移工具，兼容范围涵盖：PL/SQL、数据类型、高级函数、表分区等。 用户可以直接在阿里云购买PPAS进行使用。 如果在购买PPAS前，想试用一下...

PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合

1 minute read

背景 IoT场景，车联网场景，共享单车场景，人的行为位点等，终端实时上报的是孤立的位点，我们需要将其补齐成轨迹。 例如共享单车，下单，开锁，生成订单，骑行，关闭订单，关锁。这个过程有一个唯一的订单号，每次上报的位点会包含时间，订单号，位置。 根据订单号，将点聚合为轨迹。 使用pipelinedb插件，可以实...

PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法

2 minute read

背景 使用datediff，对时间或日期相减，得到的间隔，转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。 DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) 周 select datediff(week,'2009-01-0...