В любой системе, где есть остаток, счётчик или итог, рано или поздно встаёт один и тот же выбор. Либо считать величину заново при каждом обращении к ней, либо хранить готовой и подправлять при каждом изменении исходных данных. Первое медленно и всегда верно, второе быстро и верно ровно до первой пропущенной правки. Выбор этот делают все: складские остатки, счётчики просмотров, баланс лицевого счёта, любые предрассчитанные итоги.
Я сделал его в 1999 году, выбрал хранить, и получил вместе со скоростью проблему на двадцать+ лет вперёд, но тогда я об этом ещё не знал))).
Проблема выглядела так. Хранимые остатки по счетам время от времени переставали сходиться с операциями, которые их формируют. Иногда на копейки, иногда на миллионы. Я написал утилиту, которая находила такие счета и правила цифры. Потом встроил её в запуск приложения. Потом добавил кнопку в интерфейс, потому что остатки успевали скривиться и среди дня. Костыль на костыле, зато быстро. Причину я нашёл только сейчас, когда сел писать систему заново: в новой архитектуре каждый случай приходится описывать явно и покрывать тестом, и на этом всё и вскрылось.
Причина оказалась не в триггерах и не в производительности. Она в том, что одно бизнес‑правило было записано в коде шесть раз в разных местах, а в седьмом его забыли написать.
Три слова, без которых дальше непонятно
Валютная позиция. Сколько у банка валюты сверх обязательств. Купил доллары, позиция выросла, продал, упала. Пока она не ноль, банк зарабатывает или теряет на каждом движении курса, поэтому знать её надо сегодня, а не в отчёте за месяц.
Корсчёт. Счёт банка в другом банке, через который реально ходят деньги. Их у нас было полтора десятка. Остаток по каждому нужен для платёжной позиции: хватит ли к вечеру денег на всё, что мы обещали заплатить.
Подтверждённая операция. Платёж бывает ожидаемым и состоявшимся. Ожидаемый завели заранее, чтобы видеть его в плане, но по счёту он ещё не прошёл. Состоявшийся прошёл. В остатке считаются только состоявшиеся, и в базе это признак confirmed. Дальше вся статья будет про него.
Задача и кто её решал
Шёл 1999 год. Я работал в казначействе банка дилером, позже возглавил его. Программистом не был и не собирался.
До системы позиции у нас не было вовсе: цифры жили на бумаге. Мне нужно было составлять платёжную позицию и считать входящий остаток по каждому корсчёту. Заказать такое было не у кого и не на что, поэтому я стал писать сам, на Visual Basic 6 поверх Oracle, который стоял у меня под столом. Работали в системе все, кто был в казне.
Версия первая: считаем каждый раз
Хранимых остатков в ней не было. Есть таблица движений SUM_MOVEMENT: счёт, дата валютирования, сумма, направление плюс или минус, признак подтверждения. Открываешь счёт, приложение собирает запрос строкой и складывает всё, что по счёту прошло:
QueryText = "SELECT Sum(SUM_MOVEMENT.SUMMA*SUM_MOVEMENT.DIRECTION) FROM SUM_MOVEMENT" _ & " where acc =" & AccountsRST.Fields("ISN").Value _ & " and value_date<='" & Format$(val_date, "dd/mm/yy") & "'" _ & " and confirmed=-1" Set balanceRST = DataEnvironment1.Connection1.Execute(QueryText)
Так это выглядело в 1999 году: конкатенация, дата строкой в формате dd/mm/yy, никаких параметров. Условие confirmed=-1 и есть то самое правило про подтверждённые операции, записанное здесь первый раз.
Сделки от дилинга попадали в таблицу сами, а платежи заводили руками, поштучно: агрегатов платежей тогда не существовало. Работало как из пушки.
Почему это перестало работать
Прошло три года, и к 2002 году накопились десятки тысяч строк в таблице операций, каждый остаток стал суммой тысяч операций, и главное, что хвост этот растёт каждый день и не кончится никогда.
К моменту, когда я сел переделывать, любое обновление формы занимало до полуминуты. Ничего при этом не ломалось: ни ошибок, ни отказов, просто работать стало невозможно.
Версия вторая: храним и правим дельтой
Я завёл таблицу ACCOUNT_BALANCE, где лежит готовый остаток на счёт и дату, а поддерживать её поручил триггерам на таблице движений. Вставили операцию, остаток вырос. Удалили, уменьшился. Исправили задним числом, разница разошлась по всем последующим дням этого счёта:
FOR BALANCES_TO_REFRESH IN MORE_BALANCES LOOP NEW_BALANCE := BALANCES_TO_REFRESH.CLOSING_BALANCE - (OLD_OPER_DIRECTION * OLD_OPER_SUMMA) + (NEW_OPER_DIRECTION * NEW_OPER_SUMMA); update ACCOUNT_BALANCE set CLOSING_BALANCE = NEW_BALANCE ... END LOOP;
Остаток здесь не считается заново. Он берётся какой был и правится на разницу: вычли старое значение операции, прибавили новое. Это и есть дельта, и вся история дальше про её последствия.
Формы залетали, отклик стал почти мгновенным, систему заодно переехали с машины под столом на настоящий сервер.
Почему триггеры, а не пересчёт в коде программы: хотелось, чтобы всё делал сервер, и тогда казалось, что за этим будущее.
Карта системы
Дальше пойдут названия, поэтому вот что где живёт. Система оказалась двухслойной.
Что | Где | Что делает |
|---|---|---|
| таблица Oracle | все движения по счетам |
| таблица Oracle | хранимый остаток на счёт и дату |
Триггеры вставки, изменения, удаления | Oracle | правят остаток дельтой при любой правке движения |
| процедуры Oracle | сама арифметика по хвосту дат |
| функция Oracle | сверяет хранимое с честным пересчётом, из приложения не вызывается |
| VB6, | достраивает недостающие строки остатков на восемь дней вперёд |
| VB6, | сверяет, перезаписывает неверные остатки и шлёт отчёт письмом |
| VB6, | то же самое для оборотов, на неё же повешена кнопка «Исправить остатки» |
Одну и ту же величину трогают пять разных кусков кода в двух разных слоях, и ни один из них не главный.
Симптом и три костыля
Довольно скоро хранимые остатки начали расходиться с операциями. Замечал я это по невероятным цифрам: открываешь счёт, а там сумма, которой быть не может.
Первый костыль я написал в базе, и это была не часть программы, а инструмент для себя. Функция пробегает по счетам и датам и сравнивает хранимое значение с честным пересчётом:
if BALANCE <> OPERATIONS_BALANCE then tmpRETURN := tmpRETURN || ';' || 'ACC - ' || Current_account.acc || ' DATE - ' || Current_account_AT_DATE.VALUE_DATE || ' BALANCE - ' || balance || ' really - ' || OPERATIONS_BALANCE; end if;
Если расхождений нет, она возвращает 'OKI'. Тип возврата тут говорящий: строка. Для формы такое не годится, зато отлично годится, чтобы набрать в редакторе запросов select PRIMARY_BALANCE_CHECKING from dual и посмотреть глазами. Так я и делал: увидел странную цифру, открыл клиент к базе, дёрнул руками, получил список, поправил. Ловилось примерно раз в месяц.
Потом я переписал ту же самую сверку на VB6, чтобы она работала без меня, и это второй костыль. Процедура CORRECT_balance в форме сама считает сумму по подтверждённым движениям, сама сравнивает с хранимым остатком, сама перезаписывает и отправляет отчёт письмом. Первую версию она не вызывает: имени той функции нет ни в одном исходнике и даже в собранном exe.
Функция в базе с этого дня стала мёртвым кодом. Лежит там до сих пор, никем не вызванная, и я про неё попросту забыл: когда сейчас разбирал старый код, сам не смог вспомнить, откуда она бралась.
Коррекция в приложении работает при первом за день запуске. В служебной таблице лежит метка TURN_CHECK с датой последнего прогона: если она не сегодняшняя, значит сегодня ещё никто не проверял, и проверяем мы. Дальше подряд идут коррекция оборотов, коррекция остатков на сегодня и на вчера, поиск дублей. А в конце вот это:
If err_count <> 0 Then err_count = 0 checkDate = WeekDayAdd(checkDate, -1, "RUB") GoTo reCheck End If
Если хоть что‑то нашлось и починилось, дата сдвигается на предыдущий рабочий день и всё повторяется. Коррекция уходит вглубь истории, пока не перестанет находить. Утром, у первого вошедшего, молча.
Третий костыль, кнопка в интерфейсе, появился потому, что остатки успевали скривиться и в течение дня, а до следующего утра ждать никто не собирался. Подписана она «Исправить остатки», и вот её обработчик целиком:
Private Sub CmdCorrectBalances_Click() Call CORRECT_turnover(Me.DTPicker1.Value) End Sub
Кнопка про остатки вызывает коррекцию оборотов. Остатки она не трогает вообще. Двадцать лет на неё жали, и никто, включая меня, этого не заметил.
Все эти годы я строил себе всё более удобные способы приводить неверную цифру к верной и ни разу не подошёл к вопросу, почему она становится неверной.
Что нашлось в триггере
Систему я сейчас пишу заново, и остатки в ней устроены так, что каждый случай надо описать отдельно и покрыть тестом. Пока я раскладывал поведение по случаям, пришлось лезть в старый код и разбирать дословно, что там происходило при каждом сочетании правок. Вот триггер на изменение движения:
if :new.acc <> :old.acc and :old.acc <> 0 then BALANCE_REFRESH_ON_CHANGE(:old.acc, :old.VALUE_DATE, 0, 0, :old.summa, :old.direction); BALANCE_REFRESH_ON_INSERT(:new.acc, :old.VALUE_DATE, :old.summa, :old.direction); elsif :new.VALUE_DATE <> :old.VALUE_DATE then BALANCE_REFRESH_ON_CHANGE(:old.acc, :old.VALUE_DATE, 0, 0, :OLD.summa, :OLD.direction); BALANCE_REFRESH_ON_INSERT(:old.acc, :new.VALUE_DATE, :new.summa, :new.direction); elsif :new.CONFIRMED <> :old.CONFIRMED then if :new.CONFIRMED = 0 then BALANCE_REFRESH_ON_CHANGE(:new.acc, :new.VALUE_DATE, 0, 0, :new.summa, :new.direction); else BALANCE_REFRESH_ON_INSERT(:new.acc, :new.VALUE_DATE, :new.summa, :new.direction); end if; else BALANCE_REFRESH_ON_CHANGE(:old.acc, :old.VALUE_DATE, :new.summa, :new.direction, :old.summa, :old.direction); end if;
Четыре ветки, за одно срабатывание выполняется ровно одна. А теперь соседние триггеры на той же таблице.
Вставка:
if :new.confirmed = -1 then BALANCE_REFRESH_ON_INSERT(:new.acc, :new.VALUE_DATE, :new.summa, :new.direction); end if;
Удаление:
if :old.confirmed <> 0 then BALANCE_REFRESH_ON_CHANGE(:old.acc, :old.VALUE_DATE, 0, 0, :old.summa, :old.direction); end if;
Оба помнят про подтверждение, а триггер изменения не помнит про него нигде, кроме единственной ветки, где само подтверждение и меняется.
Отсюда пять дыр, и первая попадается на самой обычной работе.
Правка суммы у неподтверждённого платежа. Отработает последняя ветка и применит к остатку разницу между новой и старой суммой. Самой операции в остатке нет, а разница от неё появляется. Платёж ещё даже не прошёл.
Смена счёта у неподтверждённой операции. Первая ветка снимет сумму со старого счёта, где её никогда не было, и положит на новый, где её быть не должно. Портятся сразу два счёта.
Смена счёта вместе с чем‑нибудь ещё. Во втором вызове стоят :old.summa и :old.VALUE_DATE, то есть старая сумма на старую дату. Если вместе со счётом поправили сумму, новое значение не попадёт в остатки никогда.
Снятие подтверждения вместе с правкой суммы. Снимется :new.summa, а лежала в остатке :old.summa. Уходит не то, что клали.
Условие :old.acc <> 0. Операция, которой счёт назначили впервые, в первую ветку не попадает и уезжает в последнюю, где дельта применяется к счёту с номером ноль. На реальный счёт не попадает ничего.
Есть и шестое место, в процедуре добавления остатка:
INSERT_balanse(account, to_date(sysdate, 'dd/mm/yy'), 0, 0);
to_date применён к тому, что уже является датой: значение сперва превращается в строку по формату сессии, потом разбирается обратно по маске с двузначным годом, так что с разных рабочих мест могло лечь на разные даты. Утверждать не берусь, той системы давно нет, но проверить логами стоило.
Корень
Правило тут ровно одно, и оно из первого раздела: в остаток попадают только подтверждённые операции. Одна строчка, которую я знал наизусть и которую нигде не записал как правило.
Записана она в коде шесть раз, каждый раз заново и своими словами. В запросе приложения это and confirmed=-1 прямо в строке. В триггере вставки if :new.confirmed = -1. В триггере удаления if :old.confirmed <> 0. Дальше две вьюхи: CONFIRMED_SUM_MOVEMENT, которой пользуется коррекция в приложении, и ALL_CONF_SUM_MOVEMENT, которой пользуется сверка в базе. Два разных имени для одного и того же понятия, потому что во второй раз я, судя по всему, просто не вспомнил, что первая уже есть.
А в триггере изменения копии просто нет. Единого места, где правило записано один раз, не существует, поэтому пропуск в одной из копий не ловится ничем: сравнивать не с чем.
Дальше работает вторая половина корня, та самая дельта. Остаток правится на разницу вместо пересчёта, поэтому пропущенная или лишняя правка уже не исправится следующей операцией и не всплывёт при перезагрузке. Она остаётся в цифре навсегда, и через месяц в остатке сидит сумма всех прошлых ошибок, каждая из которых по отдельности ничем себя не проявила.
И третья составляющая: ломается в базе, а чинится в клиенте. Триггер портит остаток, а исправляет его процедура на VB6 при старте приложения. Эти два куска кода про существование друг друга не знают вообще.
Почему я не находил этого двадцать лет
Первое: костыль лечил следствие. Остаток становился верным, вопрос закрывался, до причины дело не доходило ни разу.
Второе, и это самое обидное: отчёт о лечении вообще‑то был. Процедура коррекции копила строку и отправляла её мне письмом:
If BALANCE <> Balance_1 Then body = body & AccountsRST.Fields("ISN").Value & " " & BALANCE & " " & Balance_1 & vbCrLf End If ... If body <> "" Then Call SendEmailUsingOutlook(..., "Результаты коррекции остатков", body)
Счёт, сколько стало, сколько было. Данные для разгадки приходили мне двадцать лет подряд. Только приходили они письмами, а письмо нельзя ни сгруппировать за месяц, ни сопоставить с тем, какие операции правили накануне. Плюс уходило оно с одной конкретной учётной записи, так что у остальных пользователей коррекция проходила совсем тихо. Ну и Beep на каждом расхождении, чтобы было слышно.
Третье: порча происходит без единой ошибки. Форма запись сохранила, триггер отработал, приложение довольно. Всплывает это через недели, когда правку уже никто не помнит.
Четвёртое: триггер не наблюдается. У него нет вызова, аргументов и возврата, он срабатывает сам внутри чужой транзакции. Написать журнал изнутри тоже не выйдет: commit в триггере Oracle запрещает, а обходной путь через автономную транзакцию даёт журнал, который не откатывается вместе с транзакцией и потому рассказывает про корректировки, которых в данных не случилось.
Пятое: проверять было негде. База хоть и моего приложения, но боевая, на ней держались платежи и процессы казны. Заводить на такой тестовые случаи ради перебора комбинаций я не мог, поэтому тестировал как умел и все развилки перебрать не смог.
Шестое: код никто никогда не читал. Один автор, ни ревью, ни второй пары глаз, посоветоваться не с кем.
Возражения
«Бизнес‑логика в триггерах, сам виноват». Спорить не буду, но решение принимал не инженер. Дилеру с формой и базой триггер выглядит самым очевидным механизмом из существующих, а в 2002 году «пусть всё делает сервер» звучало как правильное направление.
«Покрой тестами». Абсолютно верно, и именно тесты в итоге всё и вскрыли. Только написать их я смог через двадцать лет и в другой системе. Тест на триггер это тест на базу целиком, а база тогда была одна и боевая, второго контура не существовало.
«Не храни, считай честно». С этого и начиналось, и упёрлось в полминуты на клик.
Что делаю иначе
Систему я сейчас пишу заново, хранимый остаток в ней остался: скорость никуда не делась из требований. Изменились три вещи.
Правило про подтверждённые операции записано один раз, в одном месте, и все, кому оно нужно, спрашивают там. Шести независимых копий больше нет, поэтому и разойтись им негде.
Логика переехала из триггеров в сервисы приложения. Сервис можно позвать напрямую, передать ему нужный случай и посмотреть результат, не поднимая всю базу и не трогая боевые данные.
Сверка перестала быть костылём и стала частью системы. Она по‑прежнему находит расхождение и по‑прежнему может его выправить, но каждое своё вмешательство пишет в таблицу: счёт, дата, сколько было, сколько стало. Такой журнал можно сгруппировать за месяц и увидеть, что кривые счета всегда те же самые, где накануне правили неподтверждённые платежи. Двадцать лет назад мне не хватало ровно этого. Журнал у меня был, не хватало журнала, который можно посчитать.
Главный итог
Если бы я не полез переписывать, я бы не узнал этого никогда.
Двадцать лет система работала, костыли справлялись, вопрос не стоял. Разбираться меня заставила новая архитектура, а совсем не совесть и не любознательность: там каждый случай надо описать явно и повесить на него тест. Пока перебирал сочетания правок и раскладывал их по тестам, вылезли ровно те развилки, которые старый триггер молча пропускал. Ответ дали тесты, написать которые в 2002 году было негде.
Так что если у вас в системе живёт хранимый итог и вы двадцать лет чините его утилитой, самый быстрый способ найти причину это начать переписывать. Дело не в качестве новой версии. Просто старую при этом наконец приходится прочитать целиком.
В легаси всё это работает до сих пор: и триггеры, и коррекция при первом входе, и кнопка. Работает же.
Ну и берегите себя.
