postgresql-uuid重复问题

报错信息

[SQL]select db_dataquality.PR_SELECT()
[Err] ERROR: duplicate key value violates unique constraint "pk_result_select"
DETAIL: Key (c_id)=(3d0e61c6615092883cc5e29198aaffb7) already exists.
CONTEXT: SQL statement "insert into DB_DATAQUALITY.RESULT_SELECT(C_ID,AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD)
select replace(cast(uuid_generate_v4() as varchar),'-','') as

排查问题

查看该函数

drop function "db_dataquality"."pr_select_bak"();
CREATE OR REPLACE FUNCTION "db_dataquality"."pr_select_bak"()
  RETURNS "pg_catalog"."void" AS $BODY$
  BEGIN
    truncate table DB_DATAQUALITY.result_select_bak;
    insert into DB_DATAQUALITY.result_select_bak(C_ID,AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,
		CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD)
    select replace(cast(uuid_generate_v4() as varchar),'-','') as C_ID,T1.AJLBID,T1.AJBSID,
		T1.AJBS,T1.AH,T1.JBFYID,T1.CBSPTID,T1.CBRID,T1.LARQ,T1.JARQ,T1.XGSJ,T1.AJJZJDID,T1.YZCD
     from (   
     	SelectdistinctAJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
     	from (select AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
            from  DB_DATAQUALITY.RESULT_SELECT_QT
            where  AJLBID = 1
            union all
            select AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
             from  DB_DATAQUALITY.RESULT_SELECT_SF where  AJLBID = 1
            union all
            select AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
             from   DB_DATAQUALITY.RESULT_SELECT_ZX where  AJLBID = 1
            union all
            select AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
             from   DB_DATAQUALITY.RESULT_SELECT_WS where  AJLBID = 1
            ) T2
        ) T1;    
    insert into DB_DATAQUALITY.result_select_bak(C_ID,AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,
		CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD)
    select replace(cast(uuid_generate_v4() as varchar),'-','') as C_ID,T1.AJLBID,T1.AJBSID,
		T1.AJBS,T1.AH,T1.JBFYID,T1.CBSPTID,T1.CBRID,T1.LARQ,T1.JARQ,T1.XGSJ,T1.AJJZJDID,T1.YZCD
     from (
     select distinct AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
    	from (select AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
             from    DB_DATAQUALITY.RESULT_SELECT_QT where  AJLBID = 2
--后面还有许多where条件不一样insert 的就不一一列举了
  ......
  END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100;
ALTER FUNCTION "db_dataquality"."pr_select_bak"() OWNER TO "atybase";

查看该存储过程并没有什么特别之处

观察uuid重复的规律

环境linux、数据库版本abase3.5.1、每次插入表总数：76824

调用15次存储过程操作查看uuid重复的条数：

无重复：3次
重复一条：5次
重复两条：4次
重复三条：2次
重复四条：1次

上网查了下uuid重复的概率:每秒产生10亿笔UUID，100年后只产生一次重复的机率是50%.如果地球上每个人都各有6亿笔UUID，发生一次重复的机率是50%

关于postgresql uuid重复的一片文章:连接当机器每微秒可以产生多个UUID时，在多个进程中有可能产生重复值。

原因就是前面对uuid.c的分析。因为本机唯一码必须确保同一个微秒内不能产生多个UUID，如果可以的话，就不要并行产生。

猜测uuid重复的可能原因

服务器生成uuid太快、导致重复？
还是说在服务器正常但是真的同一时刻产生了重复的uuid。(这种情况就像被陨石击中一样、从实验结果的高命中可以基本排除)

疑问

这些重复的uuid是不同的insert生成的、还是一个insert里面就能生成重复的uuid？

为了解开疑问：首先将临时表result_select_bak去掉主键约束、添加一个序号(XH)字段用于记录是哪个insert插入的数据。

测试过程

DROP TABLE IF EXISTS "db_dataquality"."result_select_bak";
CREATE TABLE "db_dataquality"."result_select_bak" (
"c_id" varchar(35) COLLATE "default" NOT NULL,
--中间字段不一一列举
"yzcd" int4,
--添加序号
"xh" int4
)
WITH (OIDS=FALSE);
CREATE OR REPLACE FUNCTION "db_dataquality"."pr_select_bak"()
  RETURNS "pg_catalog"."void" AS $BODY$
  BEGIN
    truncate table DB_DATAQUALITY.result_select_bak;
    insert into DB_DATAQUALITY.result_select_bak(C_ID,AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,
                                                  CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD,XH)
    select replace(cast(uuid_generate_v4() as varchar),'-','') as C_ID,T1.AJLBID,
			T1.AJBSID,T1.AJBS,T1.AH,T1.JBFYID,T1.CBSPTID,T1.CBRID,T1.LARQ,T1.JARQ,
			T1.XGSJ,T1.AJJZJDID,T1.YZCD,1
     from (   
      select distinct AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
       from 
        (
        select AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
             from    DB_DATAQUALITY.RESULT_SELECT_QT where  AJLBID = 1
            union all
            select AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
             from    DB_DATAQUALITY.RESULT_SELECT_SF where  AJLBID = 1
            union all
            select AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
             from    DB_DATAQUALITY.RESULT_SELECT_ZX  where  AJLBID = 1
            union all
            select AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
             from    DB_DATAQUALITY.RESULT_SELECT_WS where  AJLBID = 1
           ) T2
        ) T1;  
    insert into DB_DATAQUALITY.result_select_bak(C_ID,AJLBID,AJBSID,AJBS,AH,JBFYID, CBSPTID,
                                                  CBRID, LARQ,JARQ,XGSJ,AJJZJDID,YZCD,XH)
    select replace(cast(uuid_generate_v4() as varchar),'-','') as C_ID,T1.AJLBID,
					T1.AJBSID,T1.AJBS,T1.AH,T1.JBFYID,T1.CBSPTID,T1.CBRID,T1.LARQ,
					T1.JARQ,T1.XGSJ,T1.AJJZJDID,T1.YZCD,2
	.....
  END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100;
ALTER FUNCTION "db_dataquality"."pr_select_bak"() OWNER TO "atybase";

测试结果

abase2=# select c_id from DB_DATAQUALITY.result_select_bak group by c_id having count(*)>1;
               c_id               
----------------------------------
 69d74a5ed31b8d51a59cf6d244cef763
(1 row)
--相同序号、说明是一个insert里面产生了相同的uuid
abase2=# select c_id,xh from DB_DATAQUALITY.result_select_bak where c_id = '69d74a5ed31b8d51a59cf6d244cef763';
               c_id               | xh 
----------------------------------+----
 69d74a5ed31b8d51a59cf6d244cef763 |  2
 69d74a5ed31b8d51a59cf6d244cef763 |  2
(2 rows)
abase2=# select c_id,xh from DB_DATAQUALITY.result_select_bak where c_id = '0cac29558223c7b3cd72f53116d62a2d';
               c_id               | xh 
----------------------------------+----
 0cac29558223c7b3cd72f53116d62a2d |  2
 0cac29558223c7b3cd72f53116d62a2d |  1
(2 rows)
abase2=# select c_id,xh from DB_DATAQUALITY.result_select_bak where c_id = '1ea8c12e58169105fa93ec1d838b6f07';
               c_id               | xh 
----------------------------------+----
 1ea8c12e58169105fa93ec1d838b6f07 |  9
 1ea8c12e58169105fa93ec1d838b6f07 |  1
(2 rows)
...

经测试发现不管是同一个insert还是不同的insert都有可能生成相同的uuid。

到这一步我开始怀疑是不是服务器有问题了。但是这种小概率事件真的就发生在我身上了吗？
我还是不太相信小概率事件会发生

转换角度

想到默认abase安装扩展会有三个uuid函数：uuid_generate_v1()、uuid_generate_v4()、uuid_generate_v1mc()。所以考虑使用select uuid_generate_v1();替换掉uuid_generate_v4()看结果如何。但是查询不出结果。

开始怀疑

是不是插件的问题呢？

将abase3.5.1自带的uuid插件uuid-ossp.so。替换掉/opt/thunisoft/arterybase/3.5/lib/postgresql/uuid-ossp.so、然后重启数据库。
在db_dataquality下面创建扩展函数：create extension “uuid_ossp”

再次测试

执行最开始的存储过程没有发现重复uuid、多测试了几次还是没有、这个时候感觉找到问题所在了应该就是插件的问题。

为了验证正确性然后测试修改后添加了序号的存储过程发现还是有重复的数据。开始纳闷了!
详细对比这两函数获取uuid的方式:
正常获取、uuid:replace(cast(uuid_generate_v4() as varchar,’-’,’’))
异常获取、uuid:replace(public.uuid_generate_v4():text,’-’,’’)
正常获取:不加schema默认获取当前db_dataquality下面的uuid_generate_v4()函数。
异常获取:获取了public下面的uuid_generate_v4();

查看public下面的函数

CREATE OR REPLACE FUNCTION "public"."uuid_generate_v4()"
	RETURNS "pg_catalog"."varchar" AS $BODY$BEGIN
			--Routne body goes here...
				RETURN 	md5(random()::text || now::text);
END
$BODY
	LANGUAGE 'plpgsql' VOLATILE COST 100;
ALTER FUNCTION "public"."uuid_generate_v4"() OWNER TO "atybase";

对比自带uuid函数

CREATE OR REPLACE FUNCTION "public"."uuid_generate_v4"()
  RETURNS "pg_catalog"."uuid" AS '$libdir/uuid-ossp', 'uuid_generate_v4'
  LANGUAGE 'c' VOLATILE STRICT  COST 1;
ALTER FUNCTION "public"."uuid_generate_v4"() OWNER TO "sa";

发现问题

观察可以看到该函数被重新定义了、没有使用基础动态链接库、而是使用了随机数和当前时间组合md5加密的方式、导致uuid重复。

结语

在安装abase3.5.1以上版本时默认会再public下面创建uuid函数、直接调用即可、不需要再去手动创建。如果在脚本中使用了set search_path to db_xxx;然后去调用uuid_generate_v4(),会报错找不到该函数、可以使用set search_path to public,db_xxx;同时指定多个schema。