postgresql trigger to make name unique

Question

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?

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;

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;
$$;

code example of your attempt please

Haleemur Ali
– Haleemur Ali

2015-04-13 03:12:01 +00:00
Commented Apr 13, 2015 at 3:12 — Haleemur Ali
– Haleemur Ali, Commented Apr 13, 2015 at 3:12

Craig Ringer · Accepted Answer · 2015-04-13 05:26:32Z

1

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

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.

Personally I suggest:

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.

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=>

edited Apr 13, 2015 at 5:26

answered Apr 13, 2015 at 5:17

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

shaunc Over a year ago

Thanks Craig! Is the benefit of the side table (vs simply locking "target" table) just to allow more concurrent operations as long as they aren't changing the name? (And --ah -- minimizing name parsing.) I was also wondering if I could make better use of the unique index to optimize by only doing something if I got exception. However, manual says using "exception" in plpsql is slow - is unconditional check on side table faster?

Craig Ringer Over a year ago

@shaunc You could trap the exception and only generate a new value then, but you might run into deadlocks due to locking order issues. I can't really offer you much more advice without knowing a lot more about what you're trying to do, the workload, etc.

shaunc Over a year ago

thanks -- I'll go with the method you suggested as a base then experiment.

Craig Ringer Over a year ago

@MaxHodges Yes, but it'll serialize the work the same way as table locking or a counter row

maxhodges Over a year ago

@CraigRinger isn't that kind of necessary to deal with concurrency issues here/

Collectives™ on Stack Overflow

postgresql trigger to make name unique

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related