postgresql - 列默认函数无法创建唯一值

Question

我有一个这样声明的表:

CREATE TABLE my_table (
    ...
    guid uuid UNIQUE DEFAULT create_guid('my_table')
    ....
);

我有一个计算唯一 GUID 的函数。

CREATE FUNCTION create_guid(in_table text) 
RETURNS uuid AS $$
DECLARE 
    v_guid uuid;
    v_rows int;
BEGIN
    v_guid := md5(current_timestamp::text||random()::text);
    EXECUTE 'SELECT 1 FROM ' || quote_ident(in_table) ||'  WHERE guid=' || quote_literal(v_guid);
    GET DIAGNOSTICS v_rows = ROW_COUNT;
    WHILE v_rows > 0 LOOP -- can't use FOUND with EXECUTE
        v_guid := md5(current_timestamp::text||random()::text||v_guid::text);
        EXECUTE 'SELECT 1 FROM ' || quote_ident(in_table) ||'  WHERE guid=' || quote_literal(v_guid);
        GET DIAGNOSTICS v_rows = ROW_COUNT;
    END LOOP;
    RETURN v_guid;
END;
$$ LANGUAGE PLPGSQL VOLATILE;

我从未在我的生产环境中经历过 INSERT 失败，但在我的测试环境中我可以相当可靠地获得类似于以下的错误:

ERROR: duplicate key value violates unique constraint 'my_table_guid_key'
DETAIL:  Key (guid)=(fed050ad-61c4-d548-3008-2de01301c2fc) already exists.

我意识到 current_timestamp 可能是一个相同的值，我想 random() 也可能是相同的值，但它应该不太可能，但事实并非如此。即使它们相同，while 循环不会防止重复吗？

当然，我是在插入时使用默认值。这怎么会发生？什么可以修复它？

Answer 1

I have a function to compute unique GUIDs

这就是你的问题，你重新发明了一个 UUID 算法生成变体，这是一个有问题的变体。相反，只是:

CREATE EXTENSION "uuid-ossp";

SELECT uuid_generate_v4();

...产生如下输出:

           uuid_generate_v4           
--------------------------------------
 d3e3a21c-877d-4731-a119-09b25015cb7a
(1 row)

即使存在生日悖论，v4 UUID 也不会发生冲突。 Really .包括时间戳实际上会增加而不是减少碰撞的可能性。

当您使用 uuid 数据类型存储 UUID 时，它更快、更紧凑。或者至少以 bytea 形式将它们存储为大端值。除了缓慢且浪费的填充、格式化文本表示之外的任何内容。

---

Even if they they were the same, wouldn't the while loop prevent duplication?

不，因为它是 upsert / create-if-not-exists problem 的变体其中两个并发事务可以各自插入相同的值，但都不能看到对方的值。

---

我清理了该函数以使其更具可读性，并且除了非常弱的 uuid 生成方案之外没有发现任何明显的逻辑错误并且它在并发时不起作用。

CREATE FUNCTION create_guid(in_table text) 
RETURNS uuid AS $$
DECLARE 
    v_guid uuid;
    v_matched boolean = 1;
BEGIN
    WHILE v_matched
    LOOP
      v_guid := md5(current_timestamp::text||random()::text);
      EXECUTE format('SELECT 1 FROM %I  WHERE guid = %L', in_table, v_guid)
      INTO v_matched;
    END LOOP;
    RETURN v_guid;
END;
$$ LANGUAGE PLPGSQL VOLATILE;

它被声明为 volatile，它使用两个 volatile 函数，并且它正在执行一个重新检查循环。如果您在目标表上持有独占锁，那么它看起来很正常。 (如果您不持有独占锁，如果有其他并发插入器，它仍然会因重复键错误而失败)。

问题似乎出在测试工具或此处可见之外的其他组件中。