Postgres UPSERT (INSERT или UPDATE) только если значение отличается

Я обновляю базу данных Postgres 8.4 (из кода С#), и основная задача достаточно проста: либо ОБНОВЛЯЙТЕ существующую строку, либо INSERT - новую, если она еще не существует. Обычно я бы сделал это:

UPDATE my_table
SET value1 = :newvalue1, ..., updated_time = now(), updated_username = 'evgeny'
WHERE criteria1 = :criteria1 AND criteria2 = :criteria2

и если были затронуты 0 строк, выполните INSERT:

INSERT INTO my_table(criteria1, criteria2, value1, ...)
VALUES (:criteria1, :criteria2, :newvalue1, ...)

Тем не менее, есть небольшой поворот. Я не хочу менять столбцы updated_time и updated_username, если какое-либо из новых значений фактически не отличается от существующих значений, чтобы не вводить в заблуждение пользователей о том, когда данные были обновлены.

Если бы я только делал UPDATE, тогда я мог бы добавить условия WHERE для этих значений, но это не будет работать, потому что, если БД уже обновлена, UPDATE будет влиять на 0 строк, а затем я попытаюсь к INSERT.

Может ли кто-нибудь подумать об элегантном способе сделать это, кроме SELECT, затем либо ОБНОВИТЬ, либо ВСТАВИТЬ?

Ответ 1

Две вещи здесь. Во-первых, в зависимости от уровня активности в вашей базе данных вы можете столкнуться с состоянием гонки между проверкой записи и ее вставкой, где другой процесс может создать эту запись в промежуточный период. В руководстве содержится пример того, как это сделать пример ссылки

Чтобы избежать обновления, существует процедура suppress_redundant_updates_trigger(). Чтобы использовать это, как вы хотите, вы должны иметь два перед триггерами обновлений, первый вызовет suppress_redundant_updates_trigger(), чтобы прервать обновление, если изменения не были сделаны, а второе - установить отметку времени и имя пользователя, если обновление выполнено. Триггеры запускаются в алфавитном порядке. Это также означало бы изменение кода в приведенном выше примере, чтобы попробовать вставить сначала перед обновлением.

Пример того, как работает обновление подавления:

    DROP TABLE sru_test;

    CREATE TABLE sru_test(id integer not null primary key,
    data text,
    updated timestamp(3));

    CREATE TRIGGER z_min_update
    BEFORE UPDATE ON sru_test
    FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger();

    DROP FUNCTION set_updated();

    CREATE FUNCTION set_updated()
    RETURNS TRIGGER
    AS $$
    DECLARE
    BEGIN
        NEW.updated := now();
        RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;

    CREATE TRIGGER zz_set_updated
    BEFORE INSERT OR UPDATE ON sru_test
    FOR EACH ROW EXECUTE PROCEDURE  set_updated();

insert into sru_test(id,data) VALUES (1,'Data 1');
insert into sru_test(id,data) VALUES (2,'Data 2');

select * from sru_test;

update sru_test set data = 'NEW';

select * from sru_test;

update sru_test set data = 'NEW';

select * from sru_test;

update sru_test set data = 'ALTERED'  where id = 1;

select * from sru_test;

update sru_test set data = 'NEW' where id = 2;

select * from sru_test;

Ответ 2

Взгляните на триггер ДОПОЛНЕНИЯ, чтобы проверить и установить правильные значения:

CREATE OR REPLACE FUNCTION my_trigger() RETURNS TRIGGER LANGUAGE plpgsql AS
$$
BEGIN
    IF OLD.content = NEW.content THEN
        NEW.updated_time= OLD.updated_time; -- use the old value, not a new one.
    ELSE
        NEW.updated_time= NOW();
    END IF;
    RETURN NEW;
END;
$$;

Теперь вам даже не нужно упоминать поле updated_time в вашем запросе UPDATE, он будет обрабатываться с помощью триггера.

http://www.postgresql.org/docs/current/interactive/plpgsql-trigger.html

Ответ 3

Postgres получает поддержку UPSERT. В настоящее время он находится в tree с 8 мая 2015 года (commit):

Эта функция часто упоминается как upsert.

Это реализовано с использованием новой инфраструктуры, называемой "спекулятивной вставка". Это оптимистичный вариант регулярной вставки, который сначала выполняет предварительную проверку существующих кортежей, а затем пытается вставить. Если одновременно был вставлен нарушающий кортеж, speculatively вставленный кортеж удаляется и предпринимается новая попытка. Если предварительная проверка находит соответствующий кортеж альтернативой DO NOTHING или DO Выполнено действие UPDATE. Если вставка прошла успешно, не обнаружив конфликт, кортеж считается вставленным.

Снимок доступен для скачивания. Он еще не сделал выпуск.

Ответ 4

Предложение RETURNING позволяет вам связывать ваши запросы; второй запрос использует результаты из первого. (в этом случае, чтобы избежать повторного касания одних и тех же строк) (RETURNING доступно после postgres 8.4)

Показан здесь, встроенный в функцию a, но он работает и для простого SQL, также

DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;

CREATE TABLE my_table
        ( updated_time timestamp NOT NULL DEFAULT now()
        , updated_username varchar DEFAULT '_none_'
        , criteria1 varchar NOT NULL
        , criteria2 varchar NOT NULL
        , value1 varchar
        , value2 varchar
        , PRIMARY KEY (criteria1,criteria2)
        );

INSERT INTO  my_table (criteria1,criteria2,value1,value2)
SELECT 'C1_' || gs::text
        , 'C2_' || gs::text
        , 'V1_' || gs::text
        , 'V2_' || gs::text
FROM generate_series(1,10) gs
        ;

SELECT * FROM my_table ;

CREATE function funky(_criteria1 text,_criteria2 text, _newvalue1 text, _newvalue2 text)
RETURNS VOID
AS $funk$
WITH ins AS (
        INSERT INTO my_table(criteria1, criteria2, value1, value2, updated_username)
        SELECT $1, $2, $3, $4, COALESCE(current_user, 'evgeny' )
        WHERE NOT EXISTS (
                SELECT * FROM my_table nx
                WHERE nx.criteria1 = $1 AND nx.criteria2 = $2
                )
        RETURNING criteria1 AS criteria1, criteria2 AS criteria2
        )
        UPDATE my_table upd
        SET value1 = $3, value2 = $4
        , updated_time = now()
        , updated_username = COALESCE(current_user, 'evgeny')
        WHERE 1=1
        AND criteria1 = $1 AND criteria2 = $2 -- key-condition
        AND (value1 <> $3 OR value2 <> $4 )   -- row must have changed
        AND NOT EXISTS (
                SELECT * FROM ins -- the result from the INSERT
                WHERE ins.criteria1 = upd.criteria1
                AND ins.criteria2 = upd.criteria2
                )
        ;
$funk$ language sql
        ;

SELECT funky('AA', 'BB' , 'CC', 'DD' );            -- INSERT
SELECT funky('C1_3', 'C2_3' , 'V1_3', 'V2_3' );    -- (null) UPDATE 
SELECT funky('C1_7', 'C2_7' , 'V1_7', 'V2_7777' ); -- (real) UPDATE 

SELECT * FROM my_table ;

РЕЗУЛЬТАТ:

        updated_time        | updated_username | criteria1 | criteria2 | value1 | value2  
----------------------------+------------------+-----------+-----------+--------+---------
 2013-03-13 16:37:55.405267 | _none_           | C1_1      | C2_1      | V1_1   | V2_1
 2013-03-13 16:37:55.405267 | _none_           | C1_2      | C2_2      | V1_2   | V2_2
 2013-03-13 16:37:55.405267 | _none_           | C1_3      | C2_3      | V1_3   | V2_3
 2013-03-13 16:37:55.405267 | _none_           | C1_4      | C2_4      | V1_4   | V2_4
 2013-03-13 16:37:55.405267 | _none_           | C1_5      | C2_5      | V1_5   | V2_5
 2013-03-13 16:37:55.405267 | _none_           | C1_6      | C2_6      | V1_6   | V2_6
 2013-03-13 16:37:55.405267 | _none_           | C1_8      | C2_8      | V1_8   | V2_8
 2013-03-13 16:37:55.405267 | _none_           | C1_9      | C2_9      | V1_9   | V2_9
 2013-03-13 16:37:55.405267 | _none_           | C1_10     | C2_10     | V1_10  | V2_10
 2013-03-13 16:37:55.463651 | postgres         | AA        | BB        | CC     | DD
 2013-03-13 16:37:55.472783 | postgres         | C1_7      | C2_7      | V1_7   | V2_7777
(11 rows)

Ответ 5

Запустите транзакцию. Используйте select, чтобы увидеть, существуют ли данные, которые вы вставляете, уже существует, если они это делают, ничего не делать, иначе обновление, если оно не существует, а затем вставить. Наконец, закройте транзакцию.