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

Задача довольно распространенная.

Суть задачи:

Есть "Приложение", которое периодически опрашивает БД на предмет сделанных в ней изменений ("Приложение" делает Разработчик, то есть я).

Есть БД - произвольная, та что уже есть у Заказчика. Единственное что можно сделать в этой БД - добавить некое поле для отслеживания изменений (или отдельная таблица - журнал аудита).
Сейчас так и сделано - у каждой записи есть метка времени ее изменения, реализовано нечто наподобие примера аудита из документации .

Изменения в БД вносятся любыми способами, любыми клиентами (это от Разработчика "Приложения" никак не зависит) - назовем из "Внешние пользователи".

"Приложение" должно с определенным периодом времени обратиться в БД, получить набор записей (SELECT), измененных после предыдущего такого же обращения, и выполнить с ними некие действия (в БД ничего не пишется). И так далее - каждая новая итерация должна получать изменения в БД, произошедшие с момента предыдущей итерации.


Суть проблемы:

В журнал изменений (специально созданная таблица аудита) попадают метки времени и тут всё более менее корректно.
С одним нюансом.
Если фиксировать now(), как в примере , то сохраняется время 1го оператора транзакции "внешнего пользователя" для всех записей этой транзакции.
Второй вариант - фиксировать фактическое время изменения - clock_timestamp().

Но ни тот ни другой вариант не спасает от уровня изоляции транзакций, из-за которого SELECT "Приложения" видит только закоммитченные транзакции.
Соответственно, когда проходит коммит на транзакцию "внешнего пользователя", записи, измененные в ней, попадают в "прошлое".
Так как для них сохраняется метка времени, которую SELECT "Приложения" уже проскочил (обработал до более поздней метки - времени своего открытия).

1.
Судя по документации, в PG нет уровня “read uncommitted” (точнее есть, но работает как “read committed”). Да и не нравится мне такой вариант.

2.
Возможности фиксировать время коммита транзакции для всех записей, измененных в ней, насколько я понимаю, тоже нет.

3.
Делать SELECT "Приложения" с "дельтой времени назад" - это костыль. Причем не дающий 100% гарантии обработки всех транзакций и приводящий к ненужной повторной обработке части уже обработанных записей.


Вопрос: как быть? Посоветуйте правильный вариант с аудитом изменений гарантированно отслеживающий все транзакции.

Maxim Boguk,

спасибо за идею,

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

Хотелось бы найти решение, при котором приложение не вносило бы никаких изменений в БД и в таблицу аудита в частности.

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

Это выход. Но не самый красивый, учитывая, что придется постоянно копировать весь журнал аудита. Можно, наверное и не весь, а, скажем за последние сутки - но это "ненаучный" подход. Заранее-то неизвестно, насколько затянется чужая транзакция и какой "зазор" надо сделать, чтобы гарантированно ее отследить.

Может в PG есть другой способ аудита. Например, возможность подключиться к очереди транзакций (как в Oracle) или как-то еще?
Ведь работать по штампам времени - получается, что плохое решение. А задача аудита весьма распространена.
Должно быть какое-то красивое и правильное решение.

qwwq,

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

К тому же в полноценной системе репликации тут нужды нет.

Но за ссылку в правильное (как мне пока кажется) место - огромное спасибо!

Исходники пока не смотрел. Посмотреть было бы интересно, но что такое "пгку"?



Изучив информацию по Вашей ссылке, предварительно делаю следующий вывод.
Поправьте, если я ошибаюсь.

1. В таблицу аудита фиксируем не время изменения, а XID транзакции (txid_current() в триггере).

2. Приложению достаточно знать XID начальной транзакции, с которой начинать отслеживание изменений (назовем ее TranA).

3. Далее в каждой итерации запроса изменений:
а) открываем транзакцию;
б) определяем минимальное значение из перечня незакоммитченных транзакций на момент текущего снапшота;
txid_snapshot_xmin(txid_current_snapshot())
в) если таких транзакций нет (вернулся NULL), берем вместо него XID своей транзакции txid_current(), - это TranB;
г) вытаскиваем из таблицы аудита все записи, у которых зафиксированный для них XID транзакции между TranA и TranB;
д) обрабатываем;
е) продвигаем для последующей выборки - TranA = TranB;
ж) закрываем транзакцию.

Есть ли тут подвох?

XID - инкремент 4 байта.
txid - 8 байт, обещано, что повториться не должен, так как это XID + некий счётчик "эпохи".
Но я пока плохо понимаю, что произойдет, когда количество транзакций превысит 2^32.
По идее, в этом случае XID предыдущей транзакции окажется меньше текущей, то есть теоретически такую ситуацию отследить можно.

Maxim Boguk,

спасибо!

Но пока, если такое возможно, постараюсь обойтись без установки доп.расширений.
Не всегда это будет возможно.

Нужно свое решение. Максимально простое.

По совету qwwq почитал про функционал получения информации о транзакциях сервера в форме во внешнем представлении .

Исходя из этого (если я правильно понял прочитанное), наметился, как мне кажется лаконичный способ решения моей проблемы.
Алгоритм я описал в сообщении выше.

Просьба покритиковать.

Огромное спасибо всем, кто откликнулся!

Прошу прощения, что сразу не ответил.

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

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

Далее, если проект перейдет в нормальную стадию некой коммерческой разработки, при которой в PostgreSQL можно будет доустановить PgQ, то однозначно буду использовать именно PgQ.

Спасибо!