Триггер отката при конфликте вставки

У меня было это:

CREATE FUNCTION upsert_user(u_name text, u_fullname text, u_email text, u_suffix text) RETURNS integer
    LANGUAGE plpgsql
    AS $$
DECLARE
    userid users.id_user%TYPE;
BEGIN
    LOOP
        -- first try to update
        UPDATE users SET "fullname" = u_fullname, "email" = u_email, "suffix" = u_suffix WHERE "name" = u_name RETURNING "id_user" INTO userid;
        -- check if the row is found
        IF FOUND THEN
            RETURN userid;
        END IF;
        -- not found so insert the row
        BEGIN
            INSERT INTO users ("name", "fullname", "email", "suffix") VALUES (u_name, u_fullname, u_email, u_suffix) RETURNING "id_user" INTO userid;
            RETURN userid;
            EXCEPTION WHEN unique_violation THEN
                -- do nothing and loop
        END;
    END LOOP;
END;
$$;

CREATE TRIGGER link_entity
  BEFORE INSERT
  ON public.users
  FOR EACH ROW
  EXECUTE PROCEDURE public.link_entity();

CREATE FUNCTION link_entity() RETURNS trigger
    LANGUAGE plpgsql
    AS $$    DECLARE
        entityid integer;
    BEGIN
        INSERT INTO privileges_entities (name) VALUES (NEW.name) RETURNING privileges_entities.id_entity INTO entityid;
        IF NOT FOUND THEN
            RETURN NULL;
        END IF;
        NEW.ref_entity := entityid;
        RETURN NEW;
    END;
$$;

После обновления postgresql до версии 9.5 я изменил функцию upsert_user, чтобы использовать новую инструкцию ON CONFLICT:

CREATE FUNCTION upsert_user(u_name text, u_fullname text, u_email text, u_suffix text) RETURNS integer
    LANGUAGE sql
    AS $$
  INSERT INTO users (name, fullname, email, suffix)
  VALUES (u_name, u_fullname, u_email, u_suffix)
  ON CONFLICT (name) DO UPDATE SET name=EXCLUDED.name, fullname=EXCLUDED.fullname, email=EXCLUDED.email, suffix=EXCLUDED.suffix
  RETURNING id_user;
$$;

Проблема в том, что теперь новые строки вставляются в таблицу привилегий_энтити, даже если вставка в таблицу пользователей не удалась. Можно ли откатить триггер, если вставка пользователя приводит к конфликту?


postgresql sql postgresql-9.5 plpgsql
person T'lash    schedule 03.02.2016    source источник
comment
Можно ли сделать его триггером «ПОСЛЕ»? Может тогда и не загорится.   -  person byrnedo    schedule 03.02.2016
comment
Триггер устанавливает столбцы, которые не могут быть нулевыми. Поэтому я не могу сделать это триггером «ПОСЛЕ».   -  person T'lash    schedule 03.02.2016

Ответы (1)


arrow_upward
1
arrow_downward

Это действительно побочный эффект использования нового предложения ON CONFLICT.

Мое решение здесь состояло бы в том, чтобы добавить проверку в саму функцию link_entity() и предотвратить ее продолжение, если пользователь уже существует. Так:

CREATE FUNCTION link_entity() RETURNS trigger
    LANGUAGE plpgsql
    AS $$    DECLARE
        entityid integer;
        nameExists boolean;
    BEGIN
        EXECUTE format('SELECT EXISTS(SELECT 1 FROM %I.%I WHERE name = NEW.name)', TG_TABLE_SCHEMA, TG_TABLE_NAME) INTO nameExists;
        IF nameExists THEN
            RETURN NEW; -- just return, entity already linked
        END IF;

        INSERT INTO privileges_entities (name) VALUES (NEW.name) RETURNING privileges_entities.id_entity INTO entityid;
        IF NOT FOUND THEN
            RETURN NULL;
        END IF;
        NEW.ref_entity := entityid;
        RETURN NEW;
    END;
$$;
person Webmut    schedule 04.02.2016
comment
Привилегия_сущность может быть пользователем, группой или чем-то еще, поэтому я не могу проверить таблицу пользователей. Столбцы id_entity и name наследуются из таблицы entity_linkables. - person T'lash; 04.02.2016
comment
Ах, тогда столбец privetities_entities.name уникален или нет? - person Webmut; 05.02.2016
comment
Нет, это просто указание для людей ;) Идентификатор - единственный значимый столбец. - person T'lash; 05.02.2016
comment
Ладно, это становится немного сложно. Я обновил ответ, чтобы он больше не зависел от таблицы пользователей, однако он предполагает, что соответствующий столбец называется именем во всех таблицах сущностей. Будет ли этого достаточно? - person Webmut; 07.02.2016