Изначально, в своей программе планировал версионирование сделать когда нибудь потом, какой то срочной необходимости не было. Помню, было желание где-то использовать тип данных jsonb, как только додумался до простой и лаконичной реализации версионирования на стороне SQL, не сделать не смог. Всего лишь одна таблица версий с 5 колонками и одна триггерная функция в 3 строчки кода.
Для описания реализации одной таблицы версий мало, поэтому придется описать еще несколько таблиц для примера.
Практически во всех базах данных, за редким исключением, есть таблица users – пользователи. Историю изменений — версий пользователя полезно хранить, например, для возможности откатится на старую версию, силами самого пользователя.
Пример таблицы пользователей:
Последние два поля на картинке, нужны для таблицы версий, также их можно назвать «автор версии» и «дата версии», но, при желании, можно обойтись без них.
Таблица версий:
Триггерная функция для сохранения версий:
Первые два поля заполняются из сохраняемой записи OLD.changestamp и OLD.userid.
В таблице версий, могут храниться не только записи таблицы users, третье поле MD5 хеш имени версионируемой таблицы, преобразованный в uuid.
В примерах ранее описана очень простая структура, но, как правило, у различных справочных данных могут быть дополнительные таблицы, с отношением «один ко многим».
Например, таблица «Группы пользователей».
И вторая таблица «Пользователи групп», состав группы — пользователи входящие в группу.
Для того, чтобы не усложнять простой механизм версионирования можно сделать небольшое дублирование данных в таблице групп, добавить jsonb поле, повторяющее структуру таблицы «Пользователи групп».
Для упрощения работы с задублированными данными, можно сделать дополнительную триггерную функцию, при INSERT или UPDATE, заполняющее таблицу «Пользователи групп» из jsonb поля.
Описанное выше дублирование, нужно только, когда требуется часто и максимально быстро получить данные из таблицы. Например, если часто делается запрос к таблице «Пользователи групп» для определения входит ли пользователь в группу Администраторы. В остальных случаях, данные можно получать запросом прямо из поля jsonb, и не использовать дублирующую таблицу.
Полный код примера по ссылке
Комментарии (15)
Barafu_Albino_Cheetah
16.09.2018 02:27Если старые версии данных нужны не часто, можно просто вести журнал изменений в БД, а при необходимости отматывать его назад «в уме».
PloAl Автор
16.09.2018 11:17+1Перед написанием статьи, искал в google на предмет возможного совмещения колес, педалей, рамы и руля.
Ничего похожего не нашел, решил поделиться.
arturgspb
16.09.2018 12:24Осторожно, сейчас набегут любители orm и напишут, что "так неправильно и что вы будете делать, когда захотите сменить бд?")))))
В действительности мы у себя в работе такие темы используем часто для гарантированного логирования изменений любых апдейтах. Работает отлично!
win32nipuh
16.09.2018 20:27Логгирование — это немножко другая история, это односторонняя запись в лог, а то, что описано в статье, насколько я понял — должно давать возможность разным юзерам работать одновременно с таблицей, что собственно и так реализовано в постгресе.
AigizK
16.09.2018 17:24+1Еще есть подход с Event Store. В простом варианте это некий файл, куда все изменения дописываются в конец. А сами таблицы можно потом заполнять на основе этого файла. Тогда вы сможете реализовать не только прокрутку назад, а скажем создать еще одну таблицу на основе существующих событий.
kirill_petrov
17.09.2018 12:031. В разных схемах могут быть таблицы с одинаковыми именами, кроме TG_TABLE_NAME, нужно еще хранить TG_TABLE_SCHEMA.
2. В коде триггера используется функция row_to_json, а тип поля jsonb, получаем ненужное преобразование из record -> json, а можно использовать функцию to_jsonb, которая сразу преобразует record -> jsonb. (jsonb может менять порядок ключей, поэтому json != jsonb)
3. Вместо row based триггера можно использовать statement триггер, там overhead-а может быть меньше, т.к. триггер будет запускаться 1 раз за statement, а не столько сколько строк изменилось.
4. Возможно правильнее использовать CLOCK_TIMESTAMP(), который будет показывать текущее время, а не зафиксированное в начале транзакции CURRENT_TIMESTAMP.
5.
Перед написанием статьи, искал в google на предмет возможного совмещения колес, педалей, рамы и руля.
Ничего похожего не нашел, решил поделиться.
pgconf.ru/2018/100615 с 25-го слайда начинается описание логирования, где история хранится в авто-партицированных таблицах.PloAl Автор
17.09.2018 12:141 — 4 Вполне возможно.
5. По моей ссылке с полным примером, видно что PostgreSQL использовался 9.6.9, а «автопартицированные таблицы» появились только в 10 версии. Не знаю насколько хорошо, индексируются слайды pdf, может быть плохо искал.
PloAl Автор
17.09.2018 13:341. Цель статьи в заголовке, описание простого примера.
2. Возможно
3. Записи историю которых предполагается сохранять, изменяются как правило по одной. В статье нет призывов сохранять версии абсолютно всех записей, базы данных.
4. В пунктах 2, 3 описаны примеры как избежать возможного overhead-a. А здесь похоже наоборот, почему?
5. По вашей ссылке, похожа только таблица версий, но она значительно сложнее.
На лавры изобретателя «версионирования записей» не претендую. В самом начале статьи описан вариант с XML, но там было много кода и несколько дополнительных запросов. Таблица можно сказать взята оттуда.
nikolayv81
18.09.2018 08:41Это скорее лог чем версионность, версионность ИМХО это когда из таблицы можно достать запись из истории/текущую зная дату на которую нужна запись. Как в хранилищах, когда есть бизнес версионность и техническая одновременно (4 поля), дпугое дело что для oltp это скорее не нужно.
BubaVV
Когда-то сталкивался с такой задачей и пытался применить pgxn.org/dist/table_version, но как-то оно не пошло
PloAl Автор
По ссылке некое дополнение, там инструкция по установке больше, логики описанной в статье.
blind_oracle
Там немного иначе — сохраняются снапшоты всей таблицы на определенный момент времени.