PostgreSQL触发器使名称唯一 [英] postgresql trigger to make name unique

问题描述

我正在使用Postgres 9.4；我有一个具有唯一索引的表。我想通过添加后缀以确保名称唯一来更改名称。

I'm using postgres 9.4; I have a table with a unique index. I would like to mutate the name by adding a suffix to ensure the name is unique.

我创建了一个后触发器来计算后缀。它在自动提交模式下运行良好。但是，如果在同一事务中插入两个具有相同名称的项目，它们都将具有相同的唯一后缀。

I have created a "before" trigger which computes a suffix. It works well in autocommit mode. However, if two items with the same name are inserted in the same transaction, they both get the same unique suffix.

完成任务的最佳方法是什么？有没有一种方法可以使用触发器来处理它，还是应该...嗯...将插入内容或更新内容保存在保存点中，然后处理错误？

What is the best way to accomplish my task? Is there a way to handle it with a trigger, or should I ... hmm... wrap the insert or update in a savepoint and then handle the error?

更新（来自@Haleemur Ali的评论）：

UPDATE (re comment from @Haleemur Ali ):

我不认为我的问题取决于细节。突出的一点是，我查询要在其上强制执行唯一性的集合的子集，然后
选择一个新名称...但是，当查询在两个对象中以相同的名称运行时，似乎

I don't think my question depends on the details. The salient point is that I query the subset of the collection over which I want to enforce uniqueness, and choose a new name... however, it would seem that when the queries are run on two objects identically named in the same transaction, one doesn't see the others' modification to the new value.

但是...以防万一...我的触发器包含（类型为触发函数的固定参数）：

But ... just in case... my trigger contains ("type" is fixed parameter to the trigger function):

select find_unique(coalesce(new.name, capitalize(type)),
    'vis_operation', 'name', format(
        'sheet_id = %s', new.sheet_id )) into new.name;

其中 find_unique包含：

Where "find_unique" contains:

create or replace function find_unique(
            stem text, table_name text, column_name text, where_expr text = null) 
        returns text language plpgsql as $$
declare
    table_nt text = quote_ident(table_name);
    column_nt text = quote_ident(column_name);
    bstem text = replace(btrim(stem),'''', '''''');
    find_re text = quote_literal(format('^%s(( \d+$)|$)', bstem));
    xtct_re text = quote_literal(format('^(%s ?)', bstem));
    where_ext text = case when where_expr is null then '' else 'and ' || where_expr end;
    query_exists text = format(
        $Q$ select 1 from %1$s where btrim(%2$s) = %3$s %4$s $Q$,
        table_nt, column_nt, quote_literal(bstem), where_ext );
    query_max text = format($q$
          select max(coalesce(nullif(regexp_replace(%1$s, %4$s, '', 'i'), ''), '0')::int)
          from %2$s where %1$s ~* %3$s %5$s
        $q$,
        column_nt, table_nt, find_re, xtct_re, where_ext );
    last int;
    i int;
begin
    -- if no exact match, use exact
    execute query_exists;
    get diagnostics i = row_count;
    if i = 0 then
        return coalesce(bstem, capitalize(right(table_nt,4)));
    end if;

    -- find stem w/ number, use max plus one.
    execute query_max into last;
    if last is null then
        return coalesce(bstem, capitalize(right(table_nt,4)));
    end if;
    return format('%s %s', bstem, last + 1);
end;
$$;

推荐答案

A 之前触发器将看到由当前正在运行的语句修改的行。所以这应该工作。参见下面的演示。

A BEFORE trigger sees rows modified by the statement that is currently running. So this should work. See demo below.

但是，在存在并发的情况下，您的设计将不起作用。您必须 LOCK TABLE ...在独占模式下您要更新的表，否则并发事务可以得到相同的后缀。或者，在存在 UNIQUE 约束的情况下，除了一个约束之外的所有错误都会出错。

However, your design will not work in the presence of concurrency. You have to LOCK TABLE ... IN EXCLUSIVE MODE the table you're updating, otherwise concurrent transactions could get the same suffix. Or, with a UNIQUE constraint present, all but one will error out.

我个人建议：

• 创建带有基本名称和计数器的边桌


• 创建条目时，锁定边表以排他性模式显示。这将序列化所有创建条目的会话，这是必需的，这样您就可以：


• UPDATE side_table SET counter = counter + 1 WHERE name = $ 1 RETURNING counter 获取下一个免费ID。如果行数为零，则：


• 如果正在创建基本名称且计数器设置为零，则在边表中创建一个新条目。

• Create a side table with the base names and a counter
• When you create an entry, lock the side table in EXCLUSIVE mode. This will serialize all sessions that create entries, which is necessary so that you can:
• UPDATE side_table SET counter = counter + 1 WHERE name = $1 RETURNING counter to get the next free ID. If you get zero rows, then instead:
• Create a new entry in the side table if the base name being created and the counter set to zero.

演示显示 BEFORE 触发器可以看到在同一语句中插入的行，但不能看到触发触发器的行：

Demo showing that BEFORE triggers can see rows inserted in the same statement, though not the row that fired the trigger:

craig=> CREATE TABLE demo(id integer);
CREATE TABLE
craig=> \e
CREATE FUNCTION
craig=> CREATE OR REPLACE FUNCTION demo_tg() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE
    row record;
BEGIN
    FOR row IN SELECT * FROM demo
    LOOP
        RAISE NOTICE 'Row is %',row;
    END LOOP;
    IF tg_op = 'DELETE' THEN
        RETURN OLD;
    ELSE
        RETURN NEW;
    END IF;
END;
$$;
CREATE FUNCTION
craig=> CREATE TRIGGER demo_tg BEFORE INSERT OR UPDATE OR DELETE ON demo FOR EACH ROW EXECUTE PROCEDURE demo_tg();
CREATE TRIGGER
craig=> INSERT INTO demo(id) VALUES (1),(2);
NOTICE:  Row is (1)
INSERT 0 2
craig=> INSERT INTO demo(id) VALUES (3),(4);
NOTICE:  Row is (1)
NOTICE:  Row is (2)
NOTICE:  Row is (1)
NOTICE:  Row is (2)
NOTICE:  Row is (3)
INSERT 0 2
craig=> UPDATE demo SET id = id + 100;
NOTICE:  Row is (1)
NOTICE:  Row is (2)
NOTICE:  Row is (3)
NOTICE:  Row is (4)
NOTICE:  Row is (2)
NOTICE:  Row is (3)
NOTICE:  Row is (4)
NOTICE:  Row is (101)
NOTICE:  Row is (3)
NOTICE:  Row is (4)
NOTICE:  Row is (101)
NOTICE:  Row is (102)
NOTICE:  Row is (4)
NOTICE:  Row is (101)
NOTICE:  Row is (102)
NOTICE:  Row is (103)
UPDATE 4
craig=> 

这篇关于PostgreSQL触发器使名称唯一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！