PGHist

Описание

Устройство и принцип работы

Основные функции и представление

-- Создаем таблицу
create table example(
  id int primary key,
  name varchar(20),
  number numeric(10,2),
  date date
);

-- Включаем ведение истории
call pghist.hist_enable('example');

-- Изменяем данные 
insert into example values (1, 'Пример', 10, current_date);
update example set number=20, date=date-1;

-- Получаем лог изменений по строкам
select * from example_hist;

-- Получаем изменения по полям
select * from example_changes();

-- Получаем таблицу в прошлом
select * from example_at_timestamp(now()-interval '10 second');

-- drop table example cascade;

• Оптимизация хранения - хранятся только старые значения измененных полей, для операции insert хранится ссылка на SQL выражение. Всегда сохраняется только первичный ключ. Структура хранения Транзакция-Выражение-Строка соответствует работе СУБД и минимизирует избыточность.
• Универсальность - возможно получение лога изменений, списка измененных данных, таблицы по состоянию на момент времени. Возможно соединение (union all) изменений по нескольким таблицам в одну выборку
• Описания - для каждой таблицы и ее столбцов возможно определить SQL-выражения для описания измененных строк и значений полей. По умолчанию описания формируются для ссылочных полей и строк таблицы
• Наследование - при наследовании таблиц (inherits) наследуется и их история. Хранимые процедуры имеют параметр cascade, который позволяет получить данные с наследованием или без него
• Транзакция и SQL выражения - изменения имеют ссылку на SQL выражение, которое ссылается на транзакцию. Можно получить все изменения, выполненные в рамках одной транзакции или строки, изменные в одном выражении
• Индексы - для таблицы истории строится индекс по полю первичного ключа, для таблицы по состоянию на момент времени - стандарные индексы по столбцам
• Передаваемое условие - при получении списка изменений можно указать условие с параметром или без него
• Автокорректировка - при выполнении DDL-операций над таблицей (alter table, create index и т.д.) срабатывает триггер, которые корректирует ведение истории. При удалении таблицы удаляется и ее история
• Расширенные имена - поддерживаются расширенные (в двойных кавычках) наименования таблиц и столбцов

• Большие проекты - применение в больших проектах не рекомендуется, т.к. из-за универсальности и избыточности возникает значительное потребление ресурсов. Как правило, в больших проектах участвуют SQL-разработчики и ДБА, которые реализуют сохранение только небходимых данных
• Изменение первичного ключа - не поддерживается изменение значений столбцов первичного ключа (при попытке возникнет исключение)
• Пересоздание представления - при удалении или изменении типа столбца представление [schema].[table]_hist удаляется и создается снова (PostgreSQL поддерживает только добавление столбцов в представление), рекомендуется создать хранимую функцию-обертку с динамическим запросом
• Радикальные изменения таблицы - при радикальных изменениях таблицы (например, переназначение первичного ключа на другое поле) возможна некорректная корректировка таблицы истории, рекомендуется удалить и создать историю (pghist.hist_disable и pghist.hist_enable)