UPSERT пост-ГРЭС (ВСТАВЛЯЮТ или ОБНОВЛЯЮТ), только если значение отличается
Я обновляю базу данных Postgres 8.4 (из кода C#), и основная задача достаточно проста: или ОБНОВИТЕ существующую строку или ВСТАВЬТЕ новую, если Вы еще не существуете. Обычно я сделал бы это:
UPDATE my_table
SET value1 = :newvalue1, ..., updated_time = now(), updated_username = 'evgeny'
WHERE criteria1 = :criteria1 AND criteria2 = :criteria2
и если 0 строк были затронуты, затем делают ВСТАВКУ:
INSERT INTO my_table(criteria1, criteria2, value1, ...)
VALUES (:criteria1, :criteria2, :newvalue1, ...)
Существует небольшое скручивание, все же. Я не хочу изменять updated_time и updated_username столбцы, если любое из новых значений не на самом деле отличается от существующих значений, чтобы не вводить в заблуждение пользователей о том, когда данные были обновлены.
Если бы я только делал ОБНОВЛЕНИЕ затем, то я мог бы добавить, ГДЕ условия для значений также, но это не будет работать здесь, потому что, если бы DB уже актуален, ОБНОВЛЕНИЕ будет влиять на 0 строк и затем я попытался бы ВСТАВИТЬ.
Кто-либо может думать об изящном способе сделать это, кроме ВЫБОРА, затем или ОБНОВИТЬ или ВСТАВИТЬ?
3 ответа
Начать транзакцию. Используйте выбор, чтобы увидеть, существуют ли уже вставляемые данные, если они есть, ничего не делайте, в противном случае обновите, если они не существуют, затем вставьте. Наконец закройте сделку.
Здесь две вещи. Во-первых, в зависимости от уровней активности в вашей базе данных вы можете попасть в состояние гонки между проверкой записи и ее вставкой, когда другой процесс может создать эту запись в промежуточный период. В руководстве есть пример, как это сделать. пример ссылки
Чтобы избежать обновления, существует процедура 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;
Взгляните на триггер BEFORE UPDATE, чтобы проверить и установить правильные значения:
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