Greenplum merge insert 用法与性能 (insert on conflict) - 2
背景
《Greenplum merge insert 用法与性能 (insert on conflict)》
这一篇写的是批量的合并写（有则更新，无则插入）。
在Greenplum开放了segment节点的写后，可以采用传统的方法来实现，不需要批量操作。
DEMO
Greenplum暂时不支持insert on conflict语法，因此需要使用UDF来实现。
使用UDF，实现有则更新，无则写入：
create table test(id int primary key, info text, crt_time timestamp);
create or replace function f_upsert(int, text, timestamp) returns void as $$
declare
res int;
begin
update test set info=$2,crt_time=$3 where id=$1;
if not found then
insert into test (id,info,crt_time) values ($1,$2,$3);
end if;
exception when others then
update test set info=$2,crt_time=$3 where id=$1;
end;
$$ language plpgsql strict;
Greenplum开放segment写的方法：
postgres=# select * from gp_segment_configuration ;
dbid | content | role | preferred_role | mode | status | port | hostname | address | replication_port | san_mounts
------+---------+------+----------------+------+--------+-------+-------------------+-----------+------------------+------------
2 | 0 | p | p | s | u | 25432 | ********** | localhost | |
3 | 1 | p | p | s | u | 25433 | ********** | localhost | |
4 | 2 | p | p | s | u | 25434 | ********** | localhost | |
5 | 3 | p | p | s | u | 25435 | ********** | localhost | |
6 | 4 | p | p | s | u | 25436 | ********** | localhost | |
7 | 5 | p | p | s | u | 25437 | ********** | localhost | |
8 | 6 | p | p | s | u | 25438 | ********** | localhost | |
9 | 7 | p | p | s | u | 25439 | ********** | localhost | |
10 | 8 | p | p | s | u | 25440 | ********** | localhost | |
11 | 9 | p | p | s | u | 25441 | ********** | localhost | |
12 | 10 | p | p | s | u | 25442 | ********** | localhost | |
13 | 11 | p | p | s | u | 25443 | ********** | localhost | |
14 | 12 | p | p | s | u | 25444 | ********** | localhost | |
15 | 13 | p | p | s | u | 25445 | ********** | localhost | |
16 | 14 | p | p | s | u | 25446 | ********** | localhost | |
17 | 15 | p | p | s | u | 25447 | ********** | localhost | |
18 | 16 | p | p | s | u | 25448 | ********** | localhost | |
19 | 17 | p | p | s | u | 25449 | ********** | localhost | |
20 | 18 | p | p | s | u | 25450 | ********** | localhost | |
21 | 19 | p | p | s | u | 25451 | ********** | localhost | |
22 | 20 | p | p | s | u | 25452 | ********** | localhost | |
23 | 21 | p | p | s | u | 25453 | ********** | localhost | |
24 | 22 | p | p | s | u | 25454 | ********** | localhost | |
25 | 23 | p | p | s | u | 25455 | ********** | localhost | |
26 | 24 | p | p | s | u | 25456 | ********** | localhost | |
27 | 25 | p | p | s | u | 25457 | ********** | localhost | |
28 | 26 | p | p | s | u | 25458 | ********** | localhost | |
29 | 27 | p | p | s | u | 25459 | ********** | localhost | |
30 | 28 | p | p | s | u | 25460 | ********** | localhost | |
31 | 29 | p | p | s | u | 25461 | ********** | localhost | |
32 | 30 | p | p | s | u | 25462 | ********** | localhost | |
33 | 31 | p | p | s | u | 25463 | ********** | localhost | |
34 | 32 | p | p | s | u | 25464 | ********** | localhost | |
35 | 33 | p | p | s | u | 25465 | ********** | localhost | |
36 | 34 | p | p | s | u | 25466 | ********** | localhost | |
37 | 35 | p | p | s | u | 25467 | ********** | localhost | |
38 | 36 | p | p | s | u | 25468 | ********** | localhost | |
39 | 37 | p | p | s | u | 25469 | ********** | localhost | |
40 | 38 | p | p | s | u | 25470 | ********** | localhost | |
41 | 39 | p | p | s | u | 25471 | ********** | localhost | |
42 | 40 | p | p | s | u | 25472 | ********** | localhost | |
43 | 41 | p | p | s | u | 25473 | ********** | localhost | |
44 | 42 | p | p | s | u | 25474 | ********** | localhost | |
45 | 43 | p | p | s | u | 25475 | ********** | localhost | |
46 | 44 | p | p | s | u | 25476 | ********** | localhost | |
47 | 45 | p | p | s | u | 25477 | ********** | localhost | |
48 | 46 | p | p | s | u | 25478 | ********** | localhost | |
49 | 47 | p | p | s | u | 25479 | ********** | localhost | |
(49 rows)
export PGOPTIONS='-c gp_session_role=utility'
psql -h 127.0.0.1 -p 25432 -U digoal postgres
参考
《Greenplum merge insert 用法与性能 (insert on conflict)》
《PostgreSQL upsert功能(insert on conflict do)的用法》
《PostgreSQL、Greenplum DML合并操作 最佳实践》