GaussDB(DWS)迁移-upsert支持
【摘要】 upsert单条记录改写
GaussDB(DWS)从8.1.1版本开始支持UPSERT功能,8.1.1版本之前建议使用如下的UDF实现UPSERT
-- upStr为拼接的update语句
-- inStr为拼接的insert语句
-- 适合update记录数>insert记录数的场景
CREATE OR REPLACE FUNCTION public.do_upsert(upStr text, inStr text)
RETURNS void
LANGUAGE plpgsql
VOLATILE NOT FENCED NOT SHIPPABLE
AS $function$
DECLARE
val int;
BEGIN
-- do update first
EXECUTE IMMEDIATE upStr;
GET DIAGNOSTICS val = row_count;
IF val = 0 THEN
-- if update nothing,just do insert
EXECUTE IMMEDIATE inStr;
END IF;
-- if other session have inserted an conflicting tuples, ust do update
EXCEPTION WHEN UNIQUE_VIOLATION THEN
EXECUTE IMMEDIATE upStr;
END$function$
;
-- 适合insert记录数>update记录数的场景
CREATE OR REPLACE FUNCTION public.do_upsert(upStr text, inStr text)
RETURNS void
LANGUAGE plpgsql
VOLATILE NOT FENCED NOT SHIPPABLE
AS $function$
DECLARE
val int;
BEGIN
EXECUTE IMMEDIATE inStr;
EXCEPTION WHEN UNIQUE_VIOLATION THEN
EXECUTE IMMEDIATE upStr;
END$function$
;
函数do_upsert_update_prevails测试
postgres=# CREATE OR REPLACE FUNCTION public.do_upsert_update_prevails(upStr text, inStr text)
postgres-# RETURNS void
postgres-# LANGUAGE plpgsql
postgres-# VOLATILE NOT FENCED NOT SHIPPABLE
postgres-# AS $function$
postgres$# DECLARE
postgres$# val int;
postgres$# BEGIN
postgres$# -- do update first
postgres$# EXECUTE IMMEDIATE upStr;
postgres$# GET DIAGNOSTICS val = row_count;
postgres$# IF val = 0 THEN
postgres$# -- if update nothing,just do insert
postgres$# EXECUTE IMMEDIATE inStr;
postgres$# END IF;
postgres$# -- if other session have inserted an conflicting tuples, ust do update
postgres$# EXCEPTION WHEN UNIQUE_VIOLATION THEN
postgres$# EXECUTE IMMEDIATE upStr;
postgres$# END$function$
postgres-# ;
CREATE FUNCTION
Time: 17.373 ms
postgres=# DROP TABLE txx;
DROP TABLE
Time: 14.356 ms
postgres=# CREATE TABLE txx(a int, b int, c int)DISTRIBUTE BY HASH(a);
CREATE TABLE
Time: 9.475 ms
postgres=# CREATE UNIQUE INDEX idx ON txx(a, b);
CREATE INDEX
Time: 10.532 ms
postgres=# CALL public.do_upsert_update_prevails('update txx set c = c+1 where a = 1 and b = 2', 'insert into txx values(1, 2, 3)');
do_upsert_update_prevails
---------------------------
(1 row)
Time: 66.729 ms
postgres=# SELECT * FROM txx;
a | b | c
---+---+---
1 | 2 | 3
(1 row)
Time: 9.156 ms
postgres=# CALL public.do_upsert_update_prevails('update txx set c = c+1 where a = 1 and b = 2', 'insert into txx values(1, 2, 3)');
do_upsert_update_prevails
---------------------------
(1 row)
Time: 18.699 ms
postgres=# SELECT * FROM txx;
a | b | c
---+---+---
1 | 2 | 4
(1 row)
Time: 15.189 ms
postgres=#
函数do_upsert_insert_prevails测试
postgres=# CREATE OR REPLACE FUNCTION public.do_upsert_insert_prevails(upStr text, inStr text)
postgres-# RETURNS void
postgres-# LANGUAGE plpgsql
postgres-# VOLATILE NOT FENCED NOT SHIPPABLE
postgres-# AS $function$
postgres$# DECLARE
postgres$# val int;
postgres$# BEGIN
postgres$# EXECUTE IMMEDIATE inStr;
postgres$# EXCEPTION WHEN UNIQUE_VIOLATION THEN
postgres$# EXECUTE IMMEDIATE upStr;
postgres$# END$function$
postgres-# ;
CREATE FUNCTION
Time: 7.840 ms
postgres=# DROP TABLE txx;
DROP TABLE
Time: 16.716 ms
postgres=# CREATE TABLE txx(a int, b int, c int)DISTRIBUTE BY HASH(a);
CREATE TABLE
Time: 9.756 ms
postgres=# CREATE UNIQUE INDEX idx ON txx(a, b);
CREATE INDEX
Time: 14.154 ms
postgres=# CALL public.do_upsert_insert_prevails('update txx set c = c+1 where a = 1 and b = 2', 'insert into txx values(1, 2, 3)');
do_upsert_insert_prevails
---------------------------
(1 row)
Time: 5.652 ms
postgres=# SELECT * FROM txx;
a | b | c
---+---+---
1 | 2 | 3
(1 row)
Time: 2.726 ms
postgres=# CALL public.do_upsert_insert_prevails('update txx set c = c+1 where a = 1 and b = 2', 'insert into txx values(1, 2, 3)');
do_upsert_insert_prevails
---------------------------
(1 row)
Time: 9.574 ms
postgres=# SELECT * FROM txx;
a | b | c
---+---+---
1 | 2 | 4
(1 row)
Time: 2.269 ms
想了解GuassDB(DWS)更多信息,欢迎微信搜索“GaussDB DWS”关注微信公众号,和您分享最新最全的PB级数仓黑科技,后台还可获取众多学习资料哦~
【声明】本内容来自华为云开发者社区博主,不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息,否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱:
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论(0)