Недавно я придумал решение для каскадного мягкого удаления с помощью Postgres 9.6, которое использует наследование для разделения записей на удаленные и не удаленные. Вот копия документа, который я пишу для нашего проекта:
Каскадное мягкое удаление
Абстрактный
В этом документе я описываю наш текущий подход к удалению объектов в нашей базе данных Postgres и представляю недостатки текущей реализации. Например, до сих пор у нас нет возможности иметь каскадное мягкое удаление. Затем я показываю метод, сочетающий сильные стороны каскадного жесткого удаления Postgres и подхода к архивированию, который легко реализовать, поддерживать и который обеспечивает повышение производительности всех поисковых запросов.
О мягких удалениях в ГОРМ
В проекте fabric8-services / fabric8-wit, написанном на Go, мы используем объектно-ориентированный преобразователь для нашей базы данных под названием GORM.
GORM предлагает способ мягкого удаления записей базы данных:
Если модель имеет поле DeletedAt, она автоматически получит возможность мягкого удаления! тогда он не будет навсегда удален из базы данных при вызове Delete, а только установит значение поля DeletedAt на текущее время.
Предположим, у вас есть определение модели, другими словами структура Go, которая выглядит так:
// User is the Go model for a user entry in the database
type User struct {
ID int
Name string
DeletedAt *time.Time
}
Допустим, вы загрузили существующую запись пользователя по ее ID из БД в объект u.
id := 123
u := User{}
db.Where("id=?", id).First(&u)
Если вы затем удалите объект с помощью GORM:
db.Delete(&u)
запись БД не будет удалена с использованием DELETE в SQL, но строка будет обновлена, а для deleted_at будет установлено текущее время:
UPDATE users SET deleted_at="2018-10-12 11:24" WHERE id = 123;
Проблемы с мягким удалением в GORM - Инверсия зависимостей и отсутствие каскада
Вышеупомянутое мягкое удаление удобно для архивирования отдельных записей, но может привести к очень странным результатам для всех записей, которые от него зависят. Это связано с тем, что мягкое удаление с помощью GORM не каскадно, как потенциальный DELETE в SQL, если бы внешний ключ был смоделирован с ON DELETE CASCADE.
Когда вы моделируете базу данных, вы обычно создаете таблицу, а затем, возможно, другую, у которой есть внешний ключ к первой:
CREATE TABLE countries (
name text PRIMARY KEY,
deleted_at timestamp
);
CREATE TABLE cities (
name text,
country text REFERENCES countries(name) ON DELETE CASCADE,
deleted_at timestamp
);
Здесь мы смоделировали список стран и городов, которые ссылаются на конкретную страну. Когда вы DELETE записываете страну, все города также будут удалены. Но поскольку в таблице есть столбец deleted_at, который содержится в структуре Go для страны или города, средство отображения GORM только мягко удалит страну и оставит принадлежащие ей города нетронутыми.
Передача ответственности с БД пользователю / разработчику
Таким образом, GORM дает разработчику возможность (программно) удалить все зависимые города. Другими словами, то, что ранее было смоделировано как отношение от городов к странам, теперь перевернуто как отношение от стран к городам. Это связано с тем, что пользователь / разработчик теперь несет ответственность за (мягкое) удаление всех городов, принадлежащих стране, при удалении страны.
Предложение
Разве не было бы замечательно, если бы у нас были мягкие удаления и все преимущества ON DELETE CASCADE?
Оказывается, это можно получить без особых усилий. Давайте пока сосредоточимся на одной таблице, а именно на таблице countries.
Таблица архива
Предположим на секунду, что у нас может быть другая таблица с именем countries_archive, которая имеет точно ту же структуру, что и таблица countries. Также предположим, что все будущие миграции схемы, которые выполняются до countries, применяются к countries_archive таблице. Единственное исключение - ограничения уникальности и внешние ключи не будут применяться к countries_archive.
Думаю, это уже звучит слишком хорошо, чтобы быть правдой, не так ли? Что ж, мы можем создать такую таблицу, используя так называемое наследование в Postgres:
CREATE TABLE countries_archive () INHERITS (countries);
Результирующая countries_archive таблица будет предназначена для хранения всех записей, где deleted_at IS NOT NULL.
Обратите внимание, что в нашем коде Go мы никогда не будем напрямую использовать какую-либо _archive таблицу. Вместо этого мы будем запрашивать исходную таблицу, от которой наследуется *_archive таблица, и Postgres затем волшебным образом автоматически заглянет в *_archive таблицу. Немного ниже я объясню, почему это так; это связано с разбиением на разделы.
Перенос записей в архивную таблицу на (софт) -DELETE
Поскольку две таблицы, countries и countries_archive выглядят совершенно одинаково по схеме, мы можем очень легко INSERT попасть в архив, используя функцию триггера, когда
DELETE происходит с countries таблицей- или когда происходит мягкое удаление, задав для
deleted_at значение, отличное от NULL.
Функция триггера выглядит так:
CREATE OR REPLACE FUNCTION archive_record()
RETURNS TRIGGER AS $$
BEGIN
-- When a soft-delete happens...
IF (TG_OP = 'UPDATE' AND NEW.deleted_at IS NOT NULL) THEN
EXECUTE format('DELETE FROM %I.%I WHERE id = $1', TG_TABLE_SCHEMA, TG_TABLE_NAME) USING OLD.id;
RETURN OLD;
END IF;
-- When a hard-DELETE or a cascaded delete happens
IF (TG_OP = 'DELETE') THEN
-- Set the time when the deletion happens
IF (OLD.deleted_at IS NULL) THEN
OLD.deleted_at := now();
END IF;
EXECUTE format('INSERT INTO %I.%I SELECT $1.*'
, TG_TABLE_SCHEMA, TG_TABLE_NAME || '_archive')
USING OLD;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
Чтобы связать функцию с триггером, мы можем написать:
CREATE TRIGGER soft_delete_countries
AFTER
-- this is what is triggered by GORM
UPDATE OF deleted_at
-- this is what is triggered by a cascaded DELETE or a direct hard-DELETE
OR DELETE
ON countries
FOR EACH ROW
EXECUTE PROCEDURE archive_record();
Выводы
Первоначально функция наследования в postgres была разработана для данных разделения. Когда вы выполняете поиск в разделенных данных с использованием определенного столбца или условия, Postgres может определить, в каком разделе выполнять поиск, и тем самым может повысить производительность вашего запрос.
Мы можем извлечь выгоду из этого повышения производительности, ища только существующие сущности, если не указано иное. Существующие записи - это те, где верно deleted_at IS NULL. (Обратите внимание, что GORM автоматически добавит AND deleted_at IS NULL к каждому запросу, если в структуре модели GORM есть DeletedAt.)
Посмотрим, знает ли уже Postgres, как воспользоваться нашим разделением, запустив EXPLAIN:
EXPLAIN SELECT * FROM countries WHERE deleted_at IS NULL;
+-------------------------------------------------------------------------+
| QUERY PLAN |
|-------------------------------------------------------------------------|
| Append (cost=0.00..21.30 rows=7 width=44) |
| -> Seq Scan on countries (cost=0.00..0.00 rows=1 width=44) |
| Filter: (deleted_at IS NULL) |
| -> Seq Scan on countries_archive (cost=0.00..21.30 rows=6 width=44) |
| Filter: (deleted_at IS NULL) |
+-------------------------------------------------------------------------+
Как мы видим, Postgres по-прежнему выполняет поиск в обеих таблицах, countries и countries_archive. Давайте посмотрим, что произойдет, если мы добавим проверочное ограничение в countries_archive таблицу при создании таблицы:
CREATE TABLE countries_archive (
CHECK (deleted_at IS NOT NULL)
) INHERITS (countries);
Теперь Postgres знает, что он может пропустить countries_archive, когда ожидается, что deleted_at будет NULL:
EXPLAIN SELECT * FROM countries WHERE deleted_at IS NULL;
+----------------------------------------------------------------+
| QUERY PLAN |
|----------------------------------------------------------------|
| Append (cost=0.00..0.00 rows=1 width=44) |
| -> Seq Scan on countries (cost=0.00..0.00 rows=1 width=44) |
| Filter: (deleted_at IS NULL) |
+----------------------------------------------------------------+
Обратите внимание на отсутствие последовательного сканирования таблицы countries_archive в вышеупомянутом EXPLAIN.
Преимущества и риски
Преимущества
- У нас есть регулярные каскадные удаления, и мы можем позволить БД выяснить, в каком порядке удалять объекты.
- В то же время мы архивируем наши данные. Каждое мягкое удаление
- Никаких изменений кода Go не требуется. Нам нужно только настроить таблицу и триггер для каждой таблицы, которая должна быть заархивирована.
- Когда мы полагаем, что больше не хотим такого поведения с триггерами и каскадным мягким удалением, мы можем легко вернуться.
- Все будущие миграции схемы, выполняемые в исходной таблице, также будут применены к
_archive версии этой таблицы. За исключением ограничений, что хорошо.
Риски
- Предположим, вы добавляете новую таблицу, которая ссылается на другую существующую таблицу с внешним ключом, имеющим
ON DELETE CASCADE. Если существующая таблица использует функцию archive_record(), указанную выше, ваша новая таблица будет получать жесткие DELETEs, когда что-то в существующей таблице мягко удаляется. Это не проблема, если вы также используете archive_record() для своей новой зависимой таблицы. Но вы просто должны это запомнить.
Последние мысли
Представленный здесь подход не решает проблему восстановления отдельных строк. С другой стороны, такой подход не усложняет задачу. Это просто остается нерешенным.
В нашем приложении для некоторых полей рабочего элемента не указан внешний ключ. Хорошим примером являются идентификаторы областей. Это означает, что когда область равна DELETEd, связанный рабочий элемент автоматически не становится DELETEd. Есть два сценария, когда область удаляется сама:
- Удаление запрашивается напрямую у пользователя.
- Пользователь запрашивает удаление пространства, а затем область удаляется из-за ограничения внешнего ключа на пространстве.
Обратите внимание, что в первом сценарии запросы пользователя проходят через код контроллера области, а затем через код репозитория области. У нас есть шанс на любом из этих слоев изменить все рабочие элементы, которые в противном случае ссылались бы на несуществующую область. Во втором сценарии все, что связано с областью, происходит и остается на уровне БД, поэтому у нас нет шансов изменить рабочие элементы. Хорошая новость в том, что нам это не обязательно. Каждый рабочий элемент ссылается на пространство и поэтому будет удален в любом случае, когда пространство исчезнет.
То, что применимо к областям, также применимо к итерациям, меткам и столбцам доски.
Как подать заявку в нашу базу данных?
Шаги
- Создайте * _архивированные таблицы для всех таблиц, наследующих исходные таблицы.
- Установите триггер мягкого удаления, используя указанную выше функцию
archive_record().
- Переместите все записи, где
deleted_at IS NOT NULL, в соответствующую _archive таблицу, выполнив жесткую DELETE, которая вызовет функцию archive_record().
Пример
Вот полностью рабочий пример, в котором мы продемонстрировали каскадное мягкое удаление двух таблиц, countries и capitals. Мы показываем, как архивируются записи независимо от метода, который был выбран для удаления.
CREATE TABLE countries (
id int primary key,
name text unique,
deleted_at timestamp
);
CREATE TABLE countries_archive (
CHECK ( deleted_at IS NOT NULL )
) INHERITS(countries);
CREATE TABLE capitals (
id int primary key,
name text,
country_id int references countries(id) on delete cascade,
deleted_at timestamp
);
CREATE TABLE capitals_archive (
CHECK ( deleted_at IS NOT NULL )
) INHERITS(capitals);
CREATE OR REPLACE FUNCTION archive_record()
RETURNS TRIGGER AS $$
BEGIN
IF (TG_OP = 'UPDATE' AND NEW.deleted_at IS NOT NULL) THEN
EXECUTE format('DELETE FROM %I.%I WHERE id = $1', TG_TABLE_SCHEMA, TG_TABLE_NAME) USING OLD.id;
RETURN OLD;
END IF;
IF (TG_OP = 'DELETE') THEN
IF (OLD.deleted_at IS NULL) THEN
OLD.deleted_at := now();
END IF;
EXECUTE format('INSERT INTO %I.%I SELECT $1.*'
, TG_TABLE_SCHEMA, TG_TABLE_NAME || '_archive')
USING OLD;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER soft_delete_countries
AFTER
UPDATE OF deleted_at
OR DELETE
ON countries
FOR EACH ROW
EXECUTE PROCEDURE archive_record();
CREATE TRIGGER soft_delete_capitals
AFTER
UPDATE OF deleted_at
OR DELETE
ON capitals
FOR EACH ROW
EXECUTE PROCEDURE archive_record();
INSERT INTO countries (id, name) VALUES (1, 'France');
INSERT INTO countries (id, name) VALUES (2, 'India');
INSERT INTO capitals VALUES (1, 'Paris', 1);
INSERT INTO capitals VALUES (2, 'Bengaluru', 2);
SELECT 'BEFORE countries' as "info", * FROM ONLY countries;
SELECT 'BEFORE countries_archive' as "info", * FROM countries_archive;
SELECT 'BEFORE capitals' as "info", * FROM ONLY capitals;
SELECT 'BEFORE capitals_archive' as "info", * FROM capitals_archive;
-- Delete one country via hard-DELETE and one via soft-delete
DELETE FROM countries WHERE id = 1;
UPDATE countries SET deleted_at = '2018-12-01' WHERE id = 2;
SELECT 'AFTER countries' as "info", * FROM ONLY countries;
SELECT 'AFTER countries_archive' as "info", * FROM countries_archive;
SELECT 'AFTER capitals' as "info", * FROM ONLY capitals;
SELECT 'AFTER capitals_archive' as "info", * FROM capitals_archive;
person
Konrad Kleine
schedule
29.10.2018
