ПРО Постгрес: Отложенные ограничения целостности

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

Имеем дело с проводками в учетной системе. Каждая проводка имеет одну строку заголовка в таблице headers и несколько строк с дебетуемыми и кредитуемыми счетами в таблице lines.

Очевидное требование к данным, чтобы сумма всех дебетуемых и кредитуемых счетов у одной проводки была одинаковой. Также не должно быть строк в headers без соответствующих строк в lines.

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

Решить такую задачу на уровне базы данных можно следующим образом.

Понадобится строчный триггер на lines, который автоматически поддерживает сумму всех дебетуемых и кредитуемых счетов в дополнительных столбцах в headers: amount_dr, amount_cr.

Триггер гарантирует, что после каждой команды insert/update/delete в lines, в таблице headers обновляются итоговые суммы по дебету и кредиту.

Теперь напрашивается сделать отложенное (deferred) ограничение check в headers с условием amount_dr = amount_cr.

И всё. Пока транзакция не завершена, это условие не будет проверяться, но commit зафиксирует только состояние равенства.

Однако PostgreSQL разрешает сделать отложенными только ограничения PRIMARY KEY, UNIQUE, FOREIGN KEY, EXCLUDE. А ограничения CHECK и NOT NULL нет. В этом есть своя логика, но сейчас она мешает.

Поскольку нам нужно именно отложенное ограничение, то воспользуемся FOREIGN KEY вместо CHECK.
Как? Придется добавить в headers еще один фиктивный столбец, в который тот же триггер на lines будет записывать true или false, в зависимости от того, являются ли текущие итоги по дебету и кредиту сбалансированными. И создать внешний ключ на этот столбец, который ссылается на фиктивную таблицу с одним столбцом и одной строкой со значение true.

И этот внешний ключ будет отложенным.

Впрочем, вот рабочий протитип:

create table dummy (
   pk boolean primary key
);
insert into dummy values (true);

create table headers (
   header_id int primary key,
   accounting_date date default now()::date,
   amount_dr numeric(20,2) not null default 0,
   amount_cr numeric(20,2) not null default 0,
   is_balance boolean not null default false,
   foreign key (is_balance) references dummy (pk)
      deferrable initially deferred
);

create table lines (
   header_id int,
   line_id int,
   account text not null,
   amount_dr numeric(20,2) not null,
   amount_cr numeric(20,2) not null,
   primary key (header_id, line_id),
   foreign key (header_id) references headers (header_id) on delete cascade,
   /* счет или дебетуется, или кредитуется */
   check ((amount_dr <> 0 and amount_cr = 0)
      or (amount_dr = 0 and amount_cr <> 0))
);

create function check_balance() returns trigger
as $$
<<local>>
declare
   change_dr lines.amount_dr%type;
   change_cr lines.amount_cr%type;
   header_id lines.header_id%type;
begin
   if TG_OP = 'DELETE' then
      change_dr := -1 * old.amount_dr;
      change_cr := -1 * old.amount_cr;
      local.header_id := old.header_id;
   elsif TG_OP = 'UPDATE' then
      change_dr := new.amount_dr - old.amount_dr;
      change_cr := new.amount_cr - old.amount_cr;
      local.header_id := new.header_id;
   elsif TG_OP = 'INSERT' then
      change_dr := new.amount_dr;
      change_cr := new.amount_cr;
      local.header_id := new.header_id;
   end if;

   update headers h
   set    amount_dr = amount_dr + change_dr,
          amount_cr = amount_cr + change_cr,
          is_balance = (amount_dr + change_dr = amount_cr + change_cr) and
                       (amount_dr + change_dr) <> 0
   where h.header_id = local.header_id;

   return new;
end;
$$ language plpgsql;

create trigger line_change
   after insert or delete or update of amount_dr, amount_cr
   on lines
   for each row
   execute procedure check_balance();

Проверяем.

Сценарий 1. Нельзя зафиксировать заголовок проводки без строк:

demo=# begin;
BEGIN
demo=# insert into headers (header_id) values (1);
INSERT 0 1
demo=# select * from headers;
header_id | accounting_date | amount_dr | amount_cr | is_balance
-----------+-----------------+-----------+-----------+------------
1 | 2017-03-02 | 0.00 | 0.00 | f
(1 row)

demo=# commit;
ERROR: insert or update on table "headers" violates foreign key constraint "headers_is_balance_fkey"
DETAIL: Key (is_balance)=(f) is not present in table "dummy".

Сценарий 2. Если проводка не сбалансирована:

demo=# commit;
ERROR: insert or update on table "headers" violates foreign key constraint "headers_is_balance_fkey"
DETAIL: Key (is_balance)=(f) is not present in table "dummy".

Сценарий 3. Сбалансированная проводка

demo=# begin;
BEGIN
demo=# insert into headers (header_id) values (1);
INSERT 0 1
demo=# insert into lines values (1,1,'10', 1000, 0);
INSERT 0 1
demo=# insert into lines values (1,2,'60', 0, 1180);
INSERT 0 1
demo=# insert into lines values (1,3,'19', 180, 0);
INSERT 0 1
demo=# commit;
COMMIT

проверим, что всё нормально:

demo=# select * from headers;
header_id | accounting_date | amount_dr | amount_cr | is_balance
-----------+-----------------+-----------+-----------+------------
         1 | 2017-03-02      |   1180.00 |   1180.00 | t
(1 row)

demo=# select * from lines;
header_id | line_id | account | amount_dr | amount_cr
-----------+---------+---------+-----------+-----------
         1 |       1 | 10      |   1000.00 |      0.00
         1 |       2 | 60      |      0.00 |   1180.00
         1 |       3 | 19      |    180.00 |      0.00
(3 rows)

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

ПРО Постгрес

вторник, 28 февраля 2017 г.

Отложенные ограничения целостности

Комментариев нет:

вторник, 28 февраля 2017 г.

Отложенные ограничения целостности

Комментариев нет:

вторник, 28 февраля 2017 г.