GaussDB(DWS)迁移-upsert支持

举报
譡里个檔 发表于 2021/04/29 09:46:10 2021/04/29
【摘要】 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级数仓黑科技,后台还可获取众多学习资料哦~

图标--大尾标.png

【声明】本内容来自华为云开发者社区博主,不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息,否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。