delta revisited. Работа с изменениями первичного ключа

Ранее я описал механизм delta для регистрации изменений в таблицах БД Oracle, позволяющий обрабатывать изменения независимо нескольким клиентам.

Долгое время все решения на базе механизма delta делались для базовых таблиц с неизменными первичными ключами.

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

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

Напомню, как работает этот механизм.

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

номер_изменения between <последний_обработанный_номер_изменения> + 1 and <последний_зарегистрированный_номер_изменения>

и после этого сохраняет последний зарегистрированный номер изменения как последний обработанный.

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

Разберем пример. Есть базовая таблица

create table items (
   id        number(5) primary key,
   name      varchar2(50) not null
);

и таблица изменений для нее

create table at_cdc_items (
    -- service columns
    seqn number not null,
    fixn number,
    when timestamp with time zone default current_timestamp,
    oper varchar2(10),
    -- base table primary key
    id number(5)
);

Вставим строки в таблицу items и понаблюдаем за таблицей изменений:

insert into items values (1, 'Линейка');
insert into items values (2, 'Карандаш');
insert into items values (3, 'Блокнот');
commit;

select * from at_cdc_items order by seqn;

SEQN FIXN WHEN               OPER   ID
---- ---- ------------------ ------ ---
   1      27-JAN-23 06.34.36 change   1
   2      27-JAN-23 06.34.36 change   2
   3      27-JAN-23 06.34.37 change   3

Соединение таблицы изменений с базовой таблицей по первичному ключу (для обработки изменений) дает:

select items.* 
from items join at_cdc_items cdc on items.id = cdc.id
order by cdc.ora_rowscn
;

ID  NAME
--- ------------
  1 Линейка
  2 Карандаш
  3 Блокнот

Пусть данные изменения были переданы во внешнюю систему, где на их основании в таблице remote_items были созданы строки:

select * from remote_items;

ID  NAME
--- ------------
  1 Линейка
  2 Карандаш
  3 Блокнот

После чего обработанные изменения удалены из таблицы изменений.

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

Сделаем два последовательных обновления первичного ключа и понаблюдаем за таблицей изменений:

update items set id = 5 where id = 3;
update items set id = 7 where id = 5;
commit;

select * from at_cdc_items order by seqn;

SEQN FIXN WHEN               OPER   ID
---- ---- ------------------ ------ ---
   4      27-JAN-23 06.37.16 change   5
   5      27-JAN-23 06.37.16 change   7

Соединение таблицы изменений с базовой таблицей по первичному ключу (для выгрузки изменений) дает:

select items.* 
from items join at_cdc_items cdc on items.id = cdc.id
order by cdc.ora_rowscn
;

ID  NAME
--- ------------
  7 Блокнот

Строки с id = 3, которую мы обновили первой командой update, нет в базовой таблице. Более того, ее изменение не зарегистрировано, так как регистрируется новое значение первичного ключа (как видим). Строки с id = 5, которую мы обновили второй командой update, также нет в базовой таблице.

Далее изменение передается во внешнюю систему и таблица изменений очищается. Во внешней системе на основании полученного изменения в таблице remote_items создается новая строка с id = 7:

select * from remote_items;

ID  NAME
--- ------------
  1 Линейка
  2 Карандаш
  3 Блокнот
  7 Блокнот

Теперь мы имеем в таблице remote_items как строку со старым значением первичного ключа, id = 3, так и строку с новым (последним) значением первичного ключа id = 7. Из-за изменения значения первичного ключа в базовой таблице items произошло рассогласование данных между таблицами – а это не то, что нам нужно.

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

Устраним расхождение вручную и попробуем доработать процедуру, чтобы корректно обрабатывать изменение первичного ключа:

delete from remote_items where id = 3;
commit;

Решение может состоять в следующем:

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

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

update items set id = 9 where id = 7;
update items set id = 11 where id = 9;
commit;

select * from items;

ID  NAME
--- ------------
  1 Линейка
  2 Карандаш
 11 Блокнот

select * from at_cdc_items cdc order by seqn;

SEQN FIXN WHEN               OPER   ID  NEW_ID
---- ---- ------------------ ------ --- ------
  25      27-JAN-23 06.57.34 change  7       9
  26      27-JAN-23 06.57.35 change  9      11

Соединение таблицы изменений с базовой таблицей для выгрузки новых и изменившихся строк теперь нужно делать по столбцу new_id. А строки в таблице изменений, где id != new_id, должны порождать операции обновления первичного ключа; назовем эту операцию mutate.

select 'mutate' oper, seqn, id, new_id, null name
from at_cdc_items
where id != new_id
union all
select 'change' oper, seqn, items.id, null, items.name
from at_cdc_items cdc join items on items.id = cdc.new_id
order by seqn, new_id asc nulls last
;

OPER   SEQN  ID NEW_ID NAME
------ ---- --- ------ -----------
mutate   25   7      9
mutate   26   9     11
change   26  11        Тетрадь 

Две операции mutate во внешней системе приводят к двум последовательным командам update, изменяющим значение первичного ключа, сначала с 7 на 9, затем с 9 на 11. Операция change, как и прежде, приводит к обновлению неключевых полей строки. В результате, таблица remote_items будет содержать:

select * from remote_items;

ID  NAME
--- ------------
  1 Линейка
  2 Карандаш
 11 Блокнот

Это правильный результат.

Итак, помимо таких обычных изменений, как вставка новой строки, обновление неключевых полей и удаление строки – для работы с которыми достаточно операций change и delete, – мы рассмотрели изменение ключевых полей базовой таблицы – и добавили операцию mutate. Продемонстрированное решение – только одна из возможных реализаций регистрации и обработки изменений на базе механизма delta.