Работа с системным каталогом

Основные таблицы системного каталога и команды psql для работы с ними.

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

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

Имеем дело с проводками в учетной системе. Каждая проводка имеет одну строку заголовка в таблице 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=# 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=# select * from headers;
 header_id | accounting_date | amount_dr | amount_cr | is_balance
-----------+-----------------+-----------+-----------+------------
         1 | 2017-03-02      |   1000.00 |   1180.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".

Сценарий 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 и необходимости их поддержки. Но с другой стороны, можно не беспокоиться, что прикладной код нарушит согласованность данных.

PLDebugger - отладчик PL/pgSQL

Возникло желание посмотреть как в pgAdmin работает отладчик PL/pgSQL и можно ли его использовать самостоятельно, например в psql.

В итоге всё получилось. Но не всё прошло гладко, поэтому есть смысл остановиться подробнее на каждом шаге:

1. Установить pgAdmin 4.
2. Установить pldebugger.
3. Проверить отладчик в pgAdmin и найти описание использования api.
4. Сделать пример в psql.

Исходные данные:

• ОС Ubuntu 14.04 
• PostgreSQL 9.6.2 (установлен из пакета пользователем root)

1. Установка pgAdmin 4

Установка pgAdmin 4 v1.2 на Ubuntu осложняется тем, что на сайте и в документации нет раздела "Установка".
Для обладателей Windows и MacOS программы установки уже подготовлены в разделе Download. Пользователи Ubuntu могут воспользоваться инструкцией с askubuntu.com. Только вместо несуществующего пакета virtualenv нужно установить python-virtualenv.

2. Установка отладчика pldebugger

Отладчик PL/pgSQL устанавливается отдельно от pgAdmin. Он представляет собой расширение PostgreSQL, которое нужно собрать и установить, а также прописать в параметре shared_preload_libraries библиотеку plugin_debugger.

В файле README.pldebugger есть раздел про установку, ориентированный на сборку PostgreSQL из исходных кодов. Для PostgreSQL, установленного из пакета, можно сделать так:

1. Понадобятся пакеты:
   apt-get install postgresql-server-dev-9.6 libssl-dev libkrb5-dev
2. Расширение устанавливаем из под root, используя PGXS:
   sudo su root
   git clone git://git.postgresql.org/git/pldebugger.git
   cd pldebugger
   export USE_PGXS=1
   make
   make install
3. Не забываем перезапустить PostgreSQL после установки параметра shared_preload_libraries.

3. Проверка отладчика в pgAdmin и описание работы api

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

Что касается описания функций API для использования без pgAdmin. Отдельного описания найти не удалось, но можно воспользоваться следующим:

1. Достаточно подробные комментарии в исходном коде. Например, в файле pldbgapi.c.
2. В переписке разработчиков нашлось такое письмо за 2007 год от Korry Douglas.
3. Никто не отменял самый "честный" способ. Включить журналирование всех команд (log_statement='all'), запустить отладку в pgAdmin и смотреть, что и в каком порядке пишется в журнал сервера.

4. Пример отладки в psql

Вооружившись необходимыми знаниями можно попробовать выполнить отладку в psql, используя расширение pldbgapi.

Понадобятся два сеанса psql: 1 - сеанс управления отладкой (proxy), 2 - сеанс, в котором запускается функция для отладки (target).

Установим отладчик:

 1| => show shared_preload_libraries;

 1|  shared_preload_libraries 
 1| ---------------------------
 1|  "$libdir/plugin_debugger"
 1| (1 row)
 1|

 1| => create extension pldbgapi;

 1| CREATE EXTENSION

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

При помощи отладчика мы хотим убедиться, что текст команды SELECT формируется корректно:

 1| => CREATE FUNCTION get_count (tabname text) RETURNS bigint
 1| AS $$DECLARE
 1|     cmd text;
 1|     retval bigint;
 1| BEGIN
 1|     cmd := 'SELECT COUNT(*) FROM '
 1|            || quote_ident(tabname);
 1|
 1|     EXECUTE cmd INTO retval;
 1|     RETURN retval;
 1| END;
 1| $$ LANGUAGE plpgsql STABLE;

 1| CREATE FUNCTION

Получим идентификатор сеанса отладки и OID функции:

 1| => select pldbg_create_listener() as sessionid,
 1|          'get_count'::regproc::oid as funcoid \gset


 1| => \echo :sessionid :funcoid

 1| 1 115750

Установим точку останова в начале функции (3-й параметр: -1) и ждем вызова функции из любого (4-й параметр: NULL) сеанса:

 1| => select * from pldbg_set_global_breakpoint(:sessionid, :funcoid, -1, NULL);

 1|  pldbg_set_global_breakpoint
 1| -----------------------------
 1|  t
 1| (1 row)
 1|

 1| => select * from pldbg_wait_for_target(:sessionid);

Отладочный сеанс перешел в режим ожидания...

Откроем второй сеанс и вызываем функцию:

 2|          => select get_count('pg_class');

Выполнение доходит до точки останова и теперь второй сеанс переходит в режим ожидания команд отладчика.

Первый сеанс просыпается и переходит в режим управления отладкой:

 1|  pldbg_wait_for_target
 1| -----------------------
 1|                  21413
 1| (1 row)

В режиме управления можно:

• проверить стек вызова (pldbg_get_stack),
• перемещаться по функциям в стеке (pldbg_select_frame),
• смотреть значения параметров и локальных переменных текущей функции в стеке (pldbg_get_variables),
• изменить значения переменных (pldbg_deposit_value),
• установить/удалить точки останова (pldbg_set_breakpoint/pldbg_drop_breakpoint),
• продолжить выполнение до следующей точки (pldbg_continue),
• выполнять пошаговую отладку с заходом внутрь функций (pldbg_step_into),
• или выполняя строку целиком (pldbg_step_over),
• в конечном итоге, прекратить сеанс отладки (pldbg_abort_target).

Посмотрим, например,  значения переменных:

 1| => select name, varClass, value, pg_catalog.format_type(dtype, NULL) as dtype
 1|       from   pldbg_get_variables(:sessionid)
 1|       order by varClass;

 1|   name   | varclass |  value   | dtype 
 1| ---------+----------+----------+--------
 1|  tabname | A        | pg_class | text
 1|  cmd     | L        | NULL     | text
 1|  retval  | L        | NULL     | bigint
 1| (3 rows)

Видим, что параметр функции tabname = 'pg_class', а значения переменных cmd и retval еще не установлены.

Добавим еще одну точку останова на команде EXECUTE (8 строка функции):

 1| => select * from pldbg_set_breakpoint(:sessionid, :funcoid, 8);

 1|  pldbg_set_breakpoint
 1| ----------------------
 1|  t
 1| (1 row)

Продолжим выполнение до новой точки останова:

 1| => select p.targetName,
 1|             (select string_agg(t.n::text||
 1|                        case when t.n=p.linenumber-1 then '*' else '' end,
 1|                        chr(10)) from generate_series(1, 11) as t(n)) as line,
 1|             pldbg_get_source(:sessionid, p.func) AS src
 1| from pldbg_continue(:sessionid) p;

 1|    targetname    | line |                 src                
 1| -----------------+------+-------------------------------------
 1|  get_count(text) | 1   +| DECLARE                            +
 1|                  | 2   +|     cmd text;                      +
 1|                  | 3   +|     retval bigint;                 +
 1|                  | 4   +| BEGIN                              +
 1|                  | 5   +|     cmd := 'SELECT COUNT(*) FROM ' +
 1|                  | 6   +|            || quote_ident(tabname);+
 1|                  | 7   +|                                    +
 1|                  | 8*  +|     EXECUTE cmd INTO retval;       +
 1|                  | 9   +|     RETURN retval;                 +
 1|                  | 10  +| END;                               +
 1|                  | 11   |
 1| (1 row)

Еще раз посмотрим значения локальных переменных:

 1| => select name, varClass, value, pg_catalog.format_type(dtype, NULL) as dtype
 1|       from   pldbg_get_variables(:sessionid)
 1|       order by varClass;

 1|   name   | varclass |             value             | dtype 
 1| ---------+----------+-------------------------------+--------
 1|  tabname | A        | pg_class                      | text
 1|  cmd     | L        | SELECT COUNT(*) FROM pg_class | text
 1|  retval  | L        | NULL                          | bigint
 1| (3 rows)

Видим, что переменной cmd присвоен текст команды SELECT.

Выполним следующую строку функции:

 1| => select s.targetname, s.linenumber-1 as line
 1|       from pldbg_step_over(:sessionid) s;

 1|    targetname    | line
 1| -----------------+------
 1|  get_count(text) |    9
 1| (1 row)

И  еще раз посмотрим значения локальных переменных:

 1| => select name, varClass, value, pg_catalog.format_type(dtype, NULL) as dtype
 1|       from   pldbg_get_variables(:sessionid)
 1|       order by varClass;

 1|   name   | varclass |             value             | dtype 
 1| ---------+----------+-------------------------------+--------
 1|  tabname | A        | pg_class                      | text
 1|  cmd     | L        | SELECT COUNT(*) FROM pg_class | text
 1|  retval  | L        | 321                           | bigint
 1| (3 rows)

Команда SELECT выполнена, в переменную retval записан результат.

Прекратим сеанс отладки:

 1| => select * from pldbg_abort_target(:sessionid);

 1|  pldbg_abort_target
 1| --------------------
 1|  t
 1| (1 row)


 2|          ERROR:  canceling statement due to user request
 2|          CONTEXT:  PL/pgSQL function get_count(text) line 9 at RETURN

Выполнение функции во втором сеансе завершается ошибкой.

Итоги

Отладчик PL/pgSQL работает, его API можно использовать без pgAdmin. 

Осталось дождаться, когда найдется умелец, который возьмет и сделает удобный графический интерфейс, лишенный недостатков, которыми столь "грешит" pgAdmin.

Сумма прописью на SQL

Беглый поиск решений для записи денежной суммы прописью (словами) находит лишь этот вариант.
Причем, он очень похож на более раннюю реализацию для Oracle PL/SQL.

Здесь представлена попытка внести разнообразие.
А чтобы было не так скучно, функция to_text сделана на SQL.