DOC.PROTOTYPES.RU

Главная > Базы данных > PostgreSQL > Рецепты >

Рецепты PostgreSQL

UPDATE OR INSERT (REPLACE)

Зачастую возникают задачи, когда требуется добавить в таблицу запись если она еще не существует. В MySQL для подобных операций существует команда REPLACE в PostreSQL такой команды нет, поэтому будем делать небольшие финты.

Например, есть таблица учета количества просмотров:

SQL код (1)
CREATE TABLE "public"."views" (
    "item_id"       INTEGER         NOT NULL, 
    "counter"       INTEGER         DEFAULT 1 NOT NULL, 
    CONSTRAINT "views_pkey" PRIMARY KEY("item_id")
) WITHOUT OIDS;

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

Perl код (1)
my $row = $dbh->selectrow_hashref('SELECT * FROM "public"."views" WHERE item_id = ?', {}, $item_id);
if ($row) {
    $dbh->do('UPDATE "public"."views" SET counter = counter + 1 WHERE item_id = ?', {}, $item_id);
} else {
    $dbh->do('INSERT INTO "public"."views" (item_id, counter) VALUES (?, ?)', {}, $item_id, 1);
}

Оптимизировав запросы и код можно получить такой вариант:

Perl код (2)
$dbh->selectrow_hashref('UPDATE "public"."views" SET counter = counter + 1 WHERE item_id = ? RETURNING *', {}, $item_id) ||
    $dbh->do('INSERT INTO "public"."views" (item_id, counter) VALUES (?, ?)', {}, $item_id, 1);

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

С запросами (WITH)

Самым простым будет выполнение совместных запросов:

SQL код (2)
WITH
    u AS (
            UPDATE "public"."view"
                SET counter = counter + [N]
                WHERE item_id = [ID]
                RETURNING item_id
        )
    INSERT INTO "public"."view" (item_id, counter)
        SELECT [ID], [N]
            WHERE NOT EXISTS (SELECT 1 FROM u);

Но в данном примере немаловажным является то, что при одновременном вызове данной команды с одним и тем же item_id, для которого еще нет записи в таблице, для одного из них мы получим ошибку duplicate key value violates unique constraint. При чем для фиксации этой проблемы мне не пришлось даже использовать транзакции.

Так же не всякая ORM поддерживает конструкции запросов такого типа, да и в ручную такие запросы в приложении писать не самая тривиальная задача

Хранимая процедура

Еще простой способ решить данную задачу на уровне базы данных - это создать хранимую процедуру и осуществить нужные запросы в ней:

SQL код (3)
CREATE OR REPLACE FUNCTION public.update_view_function (
    arg_item_id         integer,
    arg_counter         integer = 1
)
RETURNS public.views AS
$body$
DECLARE
    view_row    "public"."views";
BEGIN
    UPDATE "public"."views"
        SET counter = counter + arg_counter
        WHERE item_id = arg_item_id
        RETURNING * INTO view_row;
    IF view_row."item_id" IS NULL THEN
        INSERT INTO "public"."views"
            (item_id, counter)
            VALUES (arg_item_id, arg_counter)
            RETURNING * INTO view_row;
    END IF;
    RETURN view_row;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
COST 10;

В данном примере так же как и в предыдущем при одновременном вызове данной процедуры с одним и тем же item_id, для которого еще нет записи в таблице, для одного из них мы получим ошибку duplicate key value violates unique constraint

Для решения этой проблемы следует использовать исключения (EXCEPTION):

SQL код (4)
CREATE OR REPLACE FUNCTION public.update_view_function (
    arg_item_id         integer,
    arg_counter         integer = 1
)
RETURNS public.views AS
$body$
DECLARE
    view_row    "public"."views";
BEGIN
    UPDATE "public"."views"
        SET counter = counter + arg_counter
        WHERE item_id = arg_item_id
        RETURNING * INTO view_row;
    IF view_row."item_id" IS NULL THEN
        INSERT INTO "public"."views"
            (item_id, counter)
            VALUES (arg_item_id, arg_counter)
            RETURNING * INTO view_row;
    END IF;
    RETURN view_row;
EXCEPTION
    WHEN unique_violation THEN
        UPDATE "public"."views"
            SET counter = counter + arg_counter
            WHERE item_id = arg_item_id
            RETURNING * INTO view_row;
        RETURN view_row;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
COST 10;

Так, при возникновении коллизий уникальности первичного ключа при вставке, будет производится обновление этой строки.

Я сделал её так, что бы она еще и возвращала обновленную или вставленную строку в качестве результата, соответсвенно вызов этой функции может выглядеть так:

Perl код (2)
    my $view_now = $dbh->selectrow_hashref('SELECT * FROM "public"."update_view"('.$item_id.', '.$increment.')');

Как и в оптимизированном коде я использую RETURNING * и вам советую, это экономит лишние запрос к базе. И да, в случае с автозаполняемыми полями или правилами на уровне UPDATE которые могут изменять данные переданные от приложения, не требуется делать выборку строки после UPDATE что бы узнать, что получилось в итоге.

Впрочем, ничто не мешает использовать системную переменную FOUND, но она отражает только факт того, что было произведено (в нашем случае) успешное обновление, но не то с какими данными.

Но в некоторых случаях это может быть не совсем удобно, например, тот же DBI в Perl не поддерживает Bind Values в параметрах вызова хранимой процедуры или ORM для работы с базой обычно не дружат с хранимыми процедурами. Впрочем, это мелочи.

Правило

PostgreSQL позволяет устанавливать определенные правила на определенные события (запросы) таблицы, при чем эти правила могут отменять собственно событие и заменять его произвольным SQL запросом. Классически это можно сделать так:

SQL код (5)
CREATE RULE update_view AS ON INSERT TO public.views 
    WHERE (
        EXISTS (
            SELECT 1 FROM "public"."views" AS pv WHERE pv.item_id = NEW.item_id
        )
    )
    DO INSTEAD (
        UPDATE "public"."views"
            SET counter = counter + NEW.counter
            WHERE item_id = NEW.item_id;
    );

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

Правда, при использовании данного решения возникают следующие сложности:

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

Остается еще одно решение, которое включает в себя возможности (и ограничения) как функции, так и правила:

Триггер

Для решения задач с помощью триггеров я в основном использую PL/pgSQL реже - чистый SQL. Классически зачачу можно решить так:

SQL код (6)
CREATE OR REPLACE FUNCTION public.views_insert_trigger ()
    RETURNS trigger AS
$body$
DECLARE
    view_row    "public"."views";
BEGIN
    UPDATE "public"."views"
        SET counter = counter + NEW.counter
        WHERE item_id = NEW.item_id
        RETURNING * INTO view_row;
    IF view_row.item_id IS NULL THEN
        RETURN NULL;
    END IF;
    RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
COST 10;

CREATE TRIGGER views_insert
    BEFORE INSERT 
    ON public.views FOR EACH ROW 
    EXECUTE PROCEDURE public.views_insert_trigger();

Данное решение по сути является более продвинутой версией правила (RULE), в котором убран лишний запрос проверки существования записи, но остались те же проблемы с RETURNING и коллизий уникальности первичного ключа.

Увы результат работы триггера возвращается не клиенту, а процессу инициировавшему INSERT и соответсвенно с результат работы триггера влияет на процесс, а не на возвращаемый результат, поэтому RETURNING использовать не представляется возможным вообще.

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

Для решения проблемы коллизий можно использовать управляющее поле.

Внимание! Представленное ниже решение является потенциально опасным из-за возможности рекурсии, поэтому использовать его можно, только если вы полностью понимаете что и как происходит!

Добавляем поле:

SQL код (7)
ALTER TABLE "public"."views"
    ADD COLUMN _trigger_off BOOLEAN;

ALTER TABLE "public"."views"
    ALTER COLUMN _trigger_off SET DEFAULT NULL;

Изменяем триггерную функцию:

SQL код (8)
CREATE OR REPLACE FUNCTION "public"."views_insert_trigger" (
)
RETURNS trigger AS
$body$
DECLARE
    view_row    "public"."views";
BEGIN
-- check and flush _trigger_off
    IF NEW._trigger_off IS TRUE THEN
        NEW._trigger_off := NULL;
        RETURN NEW;
    END IF;
    NEW._trigger_off := NULL;
-- update row
    UPDATE "public"."views"
        SET counter = counter + NEW.counter
        WHERE item_id = NEW.item_id
        RETURNING * INTO view_row;
    IF view_row.item_id IS NULL THEN
-- insert row if not exists
        NEW._trigger_off := TRUE;
        INSERT INTO "public"."views" VALUES (NEW.*);
    END IF;
-- always return NULL
    RETURN NULL;
EXCEPTION
    WHEN unique_violation THEN
        UPDATE "public"."views"
            SET counter = counter + NEW.counter
            WHERE item_id = NEW.item_id;
        RETURN NULL;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
COST 10;

И обязательно следует создать триггер для UPDATE, что бы поле _trigger_off было всегда NULL

SQL код (9)
CREATE FUNCTION "public"."views_update_trigger" (
)
RETURNS trigger AS
$body$
BEGIN
    NEW._trigger_off := NULL;
    RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE;

CREATE TRIGGER "views_update"
    BEFORE UPDATE 
    ON "public"."views" FOR EACH ROW 
    EXECUTE PROCEDURE "public"."views_update_trigger"();

Самое главное, если вы до конца не понимаете и не разобрались,

Как видно, сам процесс вставки будет выполняться только при значении поля _trigger_off IS TRUE. При этом обязательно всегда это поле сбрасывать в NULL. Этим полем мы исключаем возможность рекурсии, но все равно, требуется быть достаточно внимательным при использовании подобного подхода.

Для тех, кому лениво использовать триггеры, либо по другим религиознным соображениям, при добавлении в таблицу поля _trigger_off, появляется возможность использовать хранимую процедуру (SQL код 3) в правиле (SQL код 4), правда потребуется изменить хранимую процедуру в части возвращаемых данных (она ничего не должна возвращать).

Заключение

Как видно из статьи, вариантов решения задачи более одного, и выбор решения лежит исключительно на предпочтениях разработчика. Единственно, что я могу порекомендовать, так это то, что выбор задачи должен быть обусловлен пониманием разработчика и сректра применяемых технологий (не имеет смысла создавать триггер, если в основном используются хранимые процедуры).

Я использую триггера, но это обусловлено тем, что кроме этой задачи, триггера выполняют целый спектр других задач, а отдельно хранимые процедуры используются в исключительных случаях.

Сергей Томулевич aka Phoinix (21.04.2014 г.)
Copyright © 2011 Сергей Томулевич