Всем привет. Я — Любавин Сергей, эксперт Bercut. За плечами — более двух десятилетий разработки ПО для различных СУБД, главным образом таких как Oracle и PostgreSQL, с которой и начиналась моя карьера в IT. Иногда я пишу статьи на Хабр. А иногда — книги. Как технические («Разработка игр для сотовых телефонов на J2ME», «Турбо Дельфи для новичков и не только» и другие), так и художественные. Например, рассказ «Терминатор против Электроника».
В этой статье, чтобы было интереснее, я решил объединить два жанра и сделать техническую статью в виде фантастического рассказа. Тут будут и мультивселенная, и путешествия во времени. Вся фантастика оформлена в виде цитат, чтобы те, кто хочет просто факты, могли не терять время. Все особенности работы с СУБД в статье — чистая правда. Все имена, события и названия проектов вымышлены и любой сходство с реальными — случайно. Скриншотов много, и почти все настоящие — только один не из практики.
В последнее время перед российскими компаниями остро стоит задача импортозамещения ПО, что зачастую означает разработку системы под СУБД PostgreSQL. При том что разработчики в основном всю жизнь работали с Oracle, им нужно перестроиться и начать писать код для PostgreSQL. Но нужно не просто заменить одну СУБД на другую. Нужно, как минимум, сделать не хуже, чем было раньше, а желательно — даже лучше. При этом, хотя две данные СУБД и очень похожи друг на друга, но каждая имеет свои особенности. Если разрабатывать для PostgreSQL так, как будто это Oracle, или так, как будто это «может быть любая СУБД, ведь они все одинаковы», то судьба проекта будет печальной. Как в плане сроков разработки и тестирования, так и в плане дальнейшей надежности функционирования и нагрузки.
В интернете есть много статей на тему «как перевести старую систему с Oracle на PostgreSQL». Как мигрировать схему данных, сами данные и логику. Не буду повторять это, просто сравню две СУБД на небольшом вымышленном учебном примере. Я расскажу про то, как организовать процесс написания и проверки кода системы для PostgreSQL, чтобы на выходе получился продукт даже более качественный, чем он мог быть даже без смены СУБД.
Итак, фантастический рассказ «Проект Карбэк»:
Оглавление:
Глава 3. Создание первой функции
Глава 4. Простейший модульный тест
Глава 7. Автотест оптимальности структуры таблиц
Глава 9. Анализ причины падения теста
Глава 1. Кратко о проекте
Вселенная Oracle. Наше время.
Я очнулся после бессонной ночи и приступил к работе. В голове до сих пор звучала речь со вчерашней планерки. Начинал ее, как всегда, наш шеф — Иннокентий, более известный как Кэш. Фамилия его слишком знаменита, чтобы произносить ее вслух. Впрочем, шепотом можно: Бэк. Что вы знаете о семье Бэков? Наверное то, что у Кэша есть домашнее животное, не совсем обычное — бык? Или может, что сам Кэш хоть и не обладает огромной памятью, но зато помнит абсолютно все, что действительно важно сейчас, а работает он феноменально быстро?
— Добрый день, уважаемые дамы и господа. Сегодня стартуем работу над новым проектом. Вести его будет мой брат Карл. Передаю ему слово…
— Я Карл Бэк. Как Кэш, только Карл…
В команде проекта написание кода со стороны СУБД будут вести 5 разработчиков, каждому нужно написать по 10 несложных функций
и создать несколько десятков таблиц. Потом за дело возьмутся тестировщики — функциональные, нагрузочные, интеграционные.
Нам требуется создать в микросервисной архитектуре некое приложение. Внешняя система будет вызывать хранимые в БД функции. Управление транзакциями тоже на стороне внешней системы. Предполагаются большие объемы данных и высокая нагрузка. Как всегда, в качестве СУБД будет использоваться, вероятно, единственно возможная в таких случаях система — Oracle. Какая конкретно версия будет стоять у заказчика, нам не важно — пишем универсальный код.
Вселенная PostgreSQL. Наше время.
Я проснулся после бессонной ночи и приступил к работе. Проснулся? Как такое возможно, если ночь бессонная?..
В голове до сих пор звучала речь со вчерашней планерки. Начинал ее, как всегда, наш шеф — Иннокентий, более известный как Кеша. А фамилию его вам не скажу. Угадайте сами.
— Добрый день, уважаемые дамы и господа. Сегодня стартуем работу над новым проектом. Вести его будет мой брат Кар. Передаю ему слово…
В команде данного проекта … Или я все это уже рассказывал?
Нам требуется создать в микросервисной архитектуре некое приложение. Внешняя система будет вызывать хранимые в БД функции. Управление транзакциями тоже на стороне внешней системы. Предполагаются большие объемы данных и высокая нагрузка. Как всегда, в качестве СУБД будет использоваться, вероятно, единственно возможная в таких случаях система — PostgreSQL. Какая конкретно версия будет стоять у заказчика и какой форк — нам не важно (но важно заказчику): мы пишем универсальный код.
Глава 2. Схема данных
Вселенная Oracle. Наше время.
Прежде всего мне необходимо создать таблицу, в которой будут храниться истории изменения параметров автомобилей. Поля car_id — идентификатор автомобиля, num_history — номер истории, ct_id — некий признак, который заполняется только со второй истории, stime и etime — период, на который актуальна история.
Я запустил среду разработки, которой пользуюсь — PL/SQL Developer — и подключился к своей базе данных. Кстати, существует множество сред разработки для Oracle. От самой простой, в качестве которой можно использовать обычный Блокнот в сочетании с утилитой командной строки sqlplus, до достаточно совершенных типа Oracle SQL Developer (бесплатная), SQL Navigator, TOAD, PL/SQL Developer, Plyxon и так далее.
Я создал новое SQL Window и написал там следующий текст:
create table car_history( car_id number,
ct_id number,
num_history number,
stime date,
etime dat ); Тут же все просто: у нас в БД, по сути, есть всего три встроенных типа данных — число (не важно, целое или нет), дата (не важно, со временем или без) и строка (здесь нужно понимать, какую максимальную длину строки закладывать). О чем‑то большем с точки зрения эффективности хранения и обработки даже думать нет смысла.
Число
Да, можно задать у number точность и масштаб, но это не про эффективность. Это ограничение целостности, нужно оно или нет определяется предметной областью.
Число типа number, независимо от того, заданы или нет точность и масштаб, в таблице занимает столько байт, сколько требуется для хранения его значения. Условно говоря, значение 10 — это 1 байт, значение 23 000 — это два байта.
Но, конечно, если заданы точность и/или масштаб, то каждое сохранение/изменение значения несет небольшую дополнительную нагрузку на CPU, так как системе приходится проверять, удовлетворяет число условиям или нет. Впрочем, эта нагрузка незначительна: заметить ее можно, только когда выполняются массовые операции с десятками столбцов и сотнями тысяч строк.
Дата
Что касается даты, есть еще тип timestamp — с часовым поясом или без, с точностью до миллионной доли секунды. Но точности в 1 секунду мне в данном проекте более чем достаточно, а часовой пояс в данной задаче не нужен — потому просто date.
Ожидается, что в таблице будут миллионы автомобилей, по каждому из них может быть от одной до нескольких строк. На каждую конкретную дату со временем (определяется по stime и etime) по каждому автомобилю будет ровно одна действующая историческая строка.
Написав текст, я выполнил его.
Вышла ошибка и слово dat подсветилось красным.
Закрыв окно, я заметил, что курсор автоматически перешел на последнюю строку. Оказывается, я написал dat вместо date. Дописал недостающую букву и снова выполнил текст. Таблица успешно создалась.
Потом я создал несколько индексов и ограничений. Каких конкретно — догадайтесь сами, тут все очевидно.
Исправленную команду
create table car_history( car_id number,
ct_id number,
num_history number,
stime date,
etime date ); я поместил в репозиторий инсталлятора моей системы «Карлбэк».
Инсталлятор запускается заказчиком и создает скриптами sqlplus все требуемые объекты: базу данных (если она еще не создана), схему, таблицы, индексы, функции и так далее Впрочем, часть БД — это не весь инсталлятор системы, а только его часть.
Вселенная PostgreSQL. Наше время.
Прежде всего мне необходимо создать таблицу, в которой будут храниться истории изменения параметров автомобилей. Поля car_id — идентификатор автомобиля, num_history — номер истории, ct_id — некий признак, который заполняется только со второй истории, stime и etime — период, на который актуальна история.
Я запустил среду разработки, которой пользуюсь, — DBeaver — подключился к своей базе данных. Кстати, существует множество сред разработки для PostgreSQL. От самой простой, в качестве которой можно использовать обычный Блокнот в сочетании с утилитой командной строки psql, до достаточно совершенных типа pgAdmin4 (бесплатная), DBeaver, Beekeeper Studio и так далее.
Я создал новый редактор SQL и хотел было написать текст команды создания таблицы. Стоп, сходу качественную команду я не напишу. Нужно понимать, сколько чего будет в таблице, какой смысл закладывается в каждый из столбцов, какие возможны значения. И исходя из этого подбирать правильные типы данных, чтобы, с одной стороны, система гарантированно всегда безошибочно работала, с другой — чтобы не было перерасхода дискового пространства и низкой скорости доступа к таблице.
Ожидается, что в таблице будут миллионы автомобилей, по каждому из них может быть от одной до нескольких строк. На каждую конкретную дату со временем (определяется по stime и etime) по каждому автомобилю будет ровно одна действующая историческая строка.
Число
Раз автомобилей много, а идентификатор автомобиля — это car_id, то тип должен быть bigint.
ct_id — это какая‑то характеристика автомобиля. Согласно проекту, предусмотрено всего два различных значения, поэтому тут будет достаточно smallint. Если вдруг в далеком светлом будущем вместо двух возможных значений станет возможны пятьсот, размерности поля все равно хватит.
Можно было, конечно, применить тип boolean, но тогда в будущем будут сложности с расширением списка значений. Заводить пользовательский также тип не будем, так как это усложнит работу с таблицей для внешней системы. Если хочется, чтобы туда не смогли записать ничего, кроме 0 и 1, можно создать check для столбца, который будет проверять корректность значения.
num_history — это номер истории конкретного автомобиля. Тут будет никак не больше 1 истории в день. Редко какой автомобиль используется более 20 лет. Но даже если найдется такой, то лет через 50 он все равно гарантированно пойдет на свалку. А если даже и нет, то реально у него будет, может, одна история в месяц или в год, а не в день — тут мы тоже преувеличили. Тогда получается максимум 50*365 = 18 250 историй на автомобиль, что вполне помещается в 2 байта (максимальное значение типа smallint = 32 767).
Дата
Дата может быть со временем (с часовым поясом или без) или без, а может быть время без даты с часовым поясом или без.
stime и etime — даты со временем, пусть это будет timestamp. Часовой пояс не интересует.
Выполнив такие размышления, я написал текст команды создания таблицы:
create table car_history( car_id bigint,
ct_id smallint,
num_history smallint,
stime timestamp,
etime timestam );Написав текст, я выполнил его.
Сразу видно, что в последней (пятой) строке опечатка. Вместо timestamp, я написал timestam. Это слово подсветилось красным. Дописал недостающую букву и выполнил текст. Таблица успешно создалась.
Потом я создал несколько индексов и ограничений, каких конкретно — догадайтесь сами, тут все очевидно.
Все ли я хорошо сделал в этой команде? Отнюдь нет. Помните, я рассказывал, что в PostgreSQL важно, в каком порядке идут столбцы в таблице? Потому что выполняется выравнивание полей в записи. Если сначала будут идти короткие поля, а потом длинные или сначала поля переменной длины, а потом поля постоянной длины, то, весьма вероятно, на каждой строке таблицы мы будем терять при хранении несколько байт впустую.
Сначала должны идти поля фиксированной длины (они должны быть отсортированы по убыванию длины), а уже затем поля переменной длины. Иначе при чтении каждой строки таблицы также будем терять время на вычисление смещения от начала строки до значения нужного поля таблицы.
Переписал команду в оптимальном виде и поместил ее в репозиторий инсталлятора моей системы «Карбэк»:
create table car_history( car_id bigint,
stime timestamp,
etime timestamp,
ct_id smallint,
num_history smallint );Инсталлятор запускается заказчиком и создает скриптами psql все требуемые объекты: базу данных (если еще не создана), схему, таблицы, индексы, функции и так далее Впрочем, часть БД — это не весь инсталлятор нашей системы, а только его часть.
Глава 3. Создание первой функции
Вселенная Oracle. Наше время.
Я сходу написал функцию нарезки истории на языке PL/SQL для таблицы car_history. Однако при этом промахивался мимо клавиш, путал буквы и допускал много разных ошибок всех видов, не замечая этого. Сказывалась бессонная ночь.
create or replace function update_car_history( i_car_id in number,
i_type in numbr,
i_target_date in date := sysdate ) return varchar2
is
v_result varchar2(200);
v_rec car_history%rowtype;
v_ct_map_tab t_api_chr_tab;
v_num_history number;
begin
v_rec.car_id := i_car_id;
v_rec.stime := i_target_date;
v_rec.etime := to_date('31.12.2999','dd.mm.yyyy');
select max(ch.num_history)
into v_rec.num_history
from car_history ch
where ch.car_id = i_car_id
and ch.stime <= i_target_date
and ch.etime > i_target_date;
if ( v_rec.num_history is null ) then
v_rec.num_history := 1;
else
v_num_history := v_num_history + 1;
update cars_history ch
set ch.end_time = v_target_date
where ch.car_id = i_car_id
and ch.stime <= i_target_date
and ch.etime > i_target_date;
v_ct_map_tab := get_ct_map;
if ( i_type member of v_ct_map_tab ) then
v_rec.ct_id := 1;
else
v_rec.ct_id := 0;
end if;
end if;
insert into car_history(car_id, ct_id, num_history, stime, etime)
values(v_rec.car_id, v_rec.ct_id, v_rec.num_history,v_rec.stime, v_rec.etime);
return 'ok';
end update_car_history;
/Сделал это я в SQL‑window и, нажав в кнопку Run, увидел в строке статуса сообщение
Решил, что функция успешно скомпилирована. Хотя на самом деле она была успешно создана, а про результаты компиляции мне никто не сообщал.
Хотел уже было закоммитить написанный код в репозиторий нашего инсталлятора и перейти к разработке второй функции, но что я за разработчик, если совсем ничего не проверю. Нужно хотя бы один раз как‑то вызвать эту функцию.
Что может быть проще вызова функции:
declare
v_result varchar2(2000);
begin
v_result := update_car_history( i_car_id => 1,
i_type => 2,
i_target_date => sysdate );
rollback;
dbms_output.put_line(v_result);
end;Вызываю и… не вызывается
Оказывается, функция невалидна. Как я сразу не заметил? Ведь стоило после создания открыть список невалидных объектов, нажав всего 1 кнопку.
Или хотя бы просто открыть функцию в дереве объектов в БД.
Но лучше все же список невалидных объектов, поскольку могут быть и другие невалидные объекты (которые стали невалидными из‑за их зависимости от моей функции. Это возможно, если она не только что созданная).
Чтобы устранить проблему, открываю текст функции в БД.
Все понятно: опять неправильно написал название типа. Исправить numbr на number — секундное дело. Правлю, нажимаю «Compile». Сейчас все будет в лучшем виде… Не тут‑то было.
Нет такого типа. Создаю
create or replace type t_api_chr_tab force is table of varchar2(32767);Компилирую функцию.
Опечатка в названии таблицы — и, оказывается, я забыл создать не только тип, но и другую функцию, которую использую. Конечно, забыл, ведь это не моя функция и не мой тип, а Алексея Первого — другого нашего разработчика (не путать с Петром Первым!). Функция и тип были включены в состав нашего инсталлятора, но я же не запускал его (это зря!) на своей БД.
Заменяю cars_history на car_history. Создаю функцию Алексея:
create or replace function get_ct_map return t_api_chr_tab
is
v_tab t_api_chr_tab := t_api_chr_tab();
begin
v_tab.extend(4);
v_tab(1) := 1;
v_tab(2) := 5;
v_tab(3) := 8;
v_tab(4) := 11;
return v_tab;
end get_ct_map;
/Перекомпилирую свою. Теперь‑то же точно все получится?
Снова неудача. Неправильно написал название параметра. Заменяю v_target_date на i_target_date. Жму «Compile».
Если будет опять ошибка, то мне сегодня вообще стоило взять отгул.
Ну вот, так и знал… Придумал какое‑то несуществующее название столбца в таблице car_history. Заменяю end_time на etime.
И вуаля! Ошибок больше нет.
Компилятор найдет все синтаксические и семантические ошибки, кроме тех, что есть в динамическом SQL. Но его же используют очень редко. Кроме того, компилятор сообщит о конструкциях, кажущихся ему сомнительными.
Ошибки правят все, а предупреждения многие разработчики, увы, игнорируют. «Что с того, есть какая‑то неиспользуемая переменная или неиспользуемое значение, кому это мешает?». Но в программе все должно быть идеально. Если переменная есть, она должна использоваться. Если значение рассчитывается, оно тоже должно где‑то использоваться. Иначе это пустая трата ресурсов. Это может и незаметно, если просто два числа перемножили. А если зря вызвали тяжелую функцию, которая выполняет множество запросов?
Может, в выделенной строке опечатка и значение присваивается не туда, куда нужно? Так и есть. Я хотел изменить номер истории в новой записи, но поменял его в ненужной мне переменной v_num_history, а в таблицу сохраняю значение v_rec.hum_history. Компилятор меня предупредил — я проверил и обнаружил логическую ошибку.
Если переменная правда не нужна, то ее и быть не должно. Чем меньше в коде мусора и воды, тем лучше. В идеале не должно быть не только ни одной ошибки компиляции, но и ни одного предупреждения.
В итоге у меня получился такой код:
create or replace function update_car_history( i_car_id in number,
i_type in number,
i_target_date in date := sysdate ) return varchar2
is
v_rec car_history%rowtype;
v_ct_map_tab t_api_chr_tab;
begin
v_rec.car_id := i_car_id;
v_rec.stime := i_target_date;
v_rec.etime := to_date('31.12.2999','dd.mm.yyyy');
select max(ch.num_history)
into v_rec.num_history
from car_history ch
where ch.car_id = i_car_id
and ch.stime <= i_target_date
and ch.etime > i_target_date;
if ( v_rec.num_history is null ) then
v_rec.num_history := 1;
else
v_rec.num_history := v_rec.num_history + 1;
update car_history ch
set ch.etime = i_target_date
where ch.car_id = i_car_id
and ch.stime <= i_target_date
and ch.etime > i_target_date;
v_ct_map_tab := get_ct_map;
if ( i_type member of v_ct_map_tab ) then
v_rec.ct_id := 1;
else
v_rec.ct_id := 0;
end if;
end if;
insert into car_history(car_id, ct_id, num_history, stime, etime)
values(v_rec.car_id, v_rec.ct_id, v_rec.num_history,v_rec.stime, v_rec.etime);
return 'ok';
end update_car_history;
/
Функция успешно вызывается.
Вселенная PostgreSQL. Наше время.
Я сходу написал функцию нарезки истории на языке PL/pgSQL для таблицы car_history. Однако при этом промахивался мимо клавиш, путал буквы и допускал много разных ошибок всех видов, не замечая этого. Сказывалась бессонная ночь.
create or replace function update_car_history( i_car_id in bigint,
i_type in integer,
i_target_date in timestam default current_timestamp::timestamp ) returns varchar
language plpgsql
security definer
set search_path from current
as $function$
declare
v_result varchar;
v_rec car_history;
v_ct_map_tab integer[];
v_num_history integer;
begin
v_rec.car_id := i_car_id;
v_rec.stime := i_target_date;
v_rec.etime := to_date('31.12.2999','dd.mm.yyyy');
select max(ch.num_history)
into v_rec.num_history
from car_history ch
where ch.car_id = i_car_id
and ch.stime <= i_target_date
and ch.etime > i_target_date;
if ( v_rec.num_history is null ) then
v_rec.num_history := 1;
else
v_ct_map_tab := get_ct_map();
v_num_history := v_num_history + 1;
update cars_history ch
set ch.end_time = v_target_date
where ch.car_id = i_car_id
and ch.stime <= i_target_date
and ch.etime > i_target_date;
if ( i_type = any( v_ct_map_tab ) ) then
v_rec.ct_id := 1;
else
v_rec.ct_id := 0;
end if;
end if;
insert into car_history(car_id,ct_id,num_history,stime,etime)
values(v_rec.car_id,v_rec.ct_id,v_rec.num_history,v_rec.stime,v_rec.etime);
return 'ok';
end$function$;Сделал я это в редакторе SQL и, нажав кнопку «Run», увидел сообщение об ошибке. Функция не создалась.
Как здорово, что компилятор предупредит меня обо всех синтаксических и семантических ошибках, подумал я. Как же я ошибался, но не будем забегать вперед…
Да, я в очередной раз не ввел последнюю букву в слове timestamp. Дописал ее, нажал «Run» заново и, о чудо, функция успешно создана.
После бессонной ночи память подводила меня. Нажимая кнопку «Run» в среде разработки, я думал, что функция будет скомпилирована в БД. Но нет. Она лишь была создана, то есть взят ее исходный код и записан в виде строки в системную таблицу, и не более того. Никакой компиляции — то есть полного перевода с языка программирования в машинные коды — нет. Да, прежде чем сохранить, система выполнила минимальный набор проверок. Что язык функции ей известен. Что функция начинается с открывающей кавычки и заканчивается закрывающей. При этом в качестве кавычки можно использовать любой набор символов, который не является зарезервированным словом и не встречается в тексте функции. Что типы данных, которые перечислены в параметрах функции, известны системе. Нет совсем уж грубых синтаксических ошибок. И на этом, в общем‑то, все. Надеяться на то, СУБД сообщит мне об ошибке в названии таблицы или столбца, в параметрах или названии вызываемой мной функции, или в чем‑то еще, было излишним. Абсолютно никаких гарантий. Об этом я не помнил.
Хотел уже было закоммитить написанный код в репозиторий нашего инсталлятора и перейти к разработке второй функции, но что я за разработчик, если совсем ничего не проверю. Нужно хотя бы один раз как‑то вызвать эту функцию.
Что может быть проще вызова функции:
do $$
declare
v_result varchar;
begin
v_result := update_car_history( i_car_id => 1::bigint,
i_type => 2,
i_target_date => current_timestamp::timestamp );
raise notice '%', v_result;
end $$;
Вызываю и… функция успешно выполнена. В консоли видно, что она вернула ok, как и требовалось.
Глава 4. Простейший ручной модульный тест
Вселенная Oracle. Час Ч.
Так, скрипт запустил, функция вызвалась. Смотрю в консоли: вернула она ok, как требовалось или нет? Вернула. Но сделала ли то, что требовалось?
Функция должна была сохранить известные мне данные в таблицу car_history. Посмотрим, что реально содержится в таблице. Пишу запрос
select *
from car_history ch
where ch.car_id=1
Вижу, что все сделано в лучшем виде.
Да разве могло быть иначе? Я же Эксперт с большой буквы «Э», в разработке собаку съел. Да что собаку, кого‑то побольше. Целого быка. Тсс, Кэш услышит…
Коммичу функцию и перехожу к разработке следующей.
Вселенная PostgreSQL. Час Ч.
Так, скрипт запустил, функция вызвалась. Смотрю в консоли, вернула она ok, как требовалось или нет? Вернула. Но сделала ли она то, что требовалось? Она должна была сохранить известные мне данные в таблицу car_history. Посмотрим, что реально содержится в таблице. Пишу запрос
select *
from car_history ch
where ch.car_id=1
Вижу, что все сделано в лучшем виде.
Ура! Я написал функцию — пусть и небольшую, зато почти сразу ни одной синтаксической, семантической ошибки. Да там даже и логических нет! И не нужно мне никакое ревью от других разработчиков. А время на тестирование тратить не стоит. Но пусть, конечно, попробуют — хуже не будет. Я же Эксперт с большой буквы «Э», в разработке слона съел. Карликового. По частям. Можно громко, пусть это слышит Кеша.
Глава 5. Последствия
Вселенная Oracle.
2 в 4-й степени дней спустя
Сегодня завершилась разработка системы «Карлбэк». Мы передали ее на тестирование и приступили к новому проекту — «Камбэк». Группа тестирования «Карлбэк» состоит из десяти тестировщиков, каждому нужно проверить по 30 кейсов.
2 в 4-й степени недель спустя
Сегодня мы подводим итоги проекта. В целом он был успешен. Да, разработчики не очень качественно проверили свои функции, но все равно ошибок было немного и тестирование прошло в запланированные сроки. Инсталлятор системы был передан заказчику.
2 в 4-й степени лет спустя
«Карлбэк» отлично работает уже полтора десятилетия, даже больше. Никто уже не помнит, кто, как и зачем его создавал, разрабатывал, тестировал, но проект получился достаточно удачным.
Вселенная PostgreSQL.
Сегодня завершилась разработка системы «Карбэк». Мы передали ее на тестирование и приступили к новому проекту — «Хетчбэк». Группа тестирования «Карбэк» состоит из десяти тестировщиков, каждому нужно проверить по 30 кейсов.
Я уже хотел было начать изучать технический проект «Хетчбэк», но неожиданно прилетела заявка‑блокер от нашего тестировщика Алексея Второго.
В багтерекере я увидел:
Это что, шутка? Да, сегодня, возможно, первое апреля 2034 года, но заявка совсем не шуточная.
Начинаю разбирать ошибку. Вызываю свою функцию на пустой таблице. Вызов успешен. Вызываю второй раз. Ого, теперь ошибка.
Почему не такая, как указано в заявке? Ну да, точно эта функция get_ct_map есть на БД тестировщика, а я забыл установить ее на свою БД. Я же не запускал полноценно наш инсталлятор на ней (почему?). Создаю функцию вручную.
create or replace function get_ct_map(result out integer[] )
language plpgsql
security definer
set search_path from current
as $function$
begin
result := '{1,5,8,11}';
return;
end$function$;Я же все верно делаю ‑зачем мне тратить время на полноценный запуск инсталлятора? Забегая вперед скажу, что нет. Нужен полноценно установленный патч нашей системы. Кто его знает, какие еще объекты поменялись в БД. Вручную накатывать их по одному можно очень долго, и в итоге забыть что‑то накатить. Тогда результаты тестов будут нерелевантные.
Вызываю свою функцию снова.
Теперь ошибка точно как в заявке. Оказывается, моя первая функция сообщает о неизвестной таблице при нарезке второй истории, то есть при втором вызове. При том первый вызов был успешен. Ладно бы логическая ошибка, но семантическая после успешной компиляции? Как такое возможно?
Наверняка там что‑то несложное, просто опечатка в названии, я моментально исправлю, проверю, отпишу решение и вернусь к новому проекту.
Пока я так размышлял, прилетело еще 9 заявок — от каждого из тестировщиков. Тестирование полностью остановилось: не работает ни одна из написанных нами функций. Вернее, работают все, но только до тех пор, пока они идут в ту ветку кода, в которую они шли при тестовом вызове разработчиком. А все прочие ветки — не работают.
Снимаем всю группу разработки с других задач и начинаем править. Но на каждую исправленную заявку приходит две новых.
Никогда еще Штирлиц не был так близок к провалу.
2 в 4-й степени недель спустя
Сегодня мы наконец‑то отгрузили заказчику инсталлятор нашей системы. Разработка и тестирование шли днем и ночью, включая выходные. Но, несмотря на все усилия, сроки по проекту «сгорели»(были нарушены), а качество продукта так и не достигло приемлемого уровня — даже с учетом значительной задержки отгрузки.
2 в 4-й степени лет спустя
Вот уже почти два десятилетия мы безуспешно пытаемся добиться нормального функционирования проекта «Карбэк». Такого провального проекта не знала история. Чиним одно, ломается другое. Ничего не трогаем — все равно ломается через какое‑то время…
Но, к счастью, сегодня у меня наконец появилось наше долгожданное изобретение — карманный скрипт для путешествий во времени, позволяющий перемещаться в прошлое или будущее. Об его устройстве я обязательно когда‑нибудь расскажу. А сейчас у меня есть очень срочное дело — надо успеть до ухода на пенсию. Свою летопись на этом прерываю. Спасибо всем, кто был со мной все эти годы…
Глава 6. Линтер
Вселенная PostgreSQL. Две минуты до Часа Ч.
Со звоном в воздухе появилась надпись: «Голограмма от „Два грамма“». Да, у меня всегда запущен мессенджер легкой телесвязи «Два грамма». Красным горела надпись: «Пользователь изменил дату и время 3 секунды назад».
Возникший в комнате силуэт старика казался очень знакомым, но я никак не мог вспомнить, где раньше его видел. Может, это Флеш из семьи Бэков?
Глухой голос быстро проговорил: «Стой! Ты — это я из будущего. Чтобы у нас было много денег… Не важно. Времени мало, используй линтер». Неожиданно голограмма растворилась в воздухе, замигала надпись: «Пользователь был удален или еще не создан».
«Мошенник, не иначе», — подумал я. Как может быть не важно много денег? Он бы еще про безопасный счет рассказал.
Из его речи следовало, что это запись из прошлого. Получается, что я сам когда‑то оставил себе напоминание в мессенджере. Но я никогда не пользуюсь для таких целей мессенджером. Тем более, я бы не стал использовать образ Флеша для голограммы.
Или звонивший просто перепутал, кто из прошлого, а кто из будущего? Но тогда он — из будущего, а это невозможно. Тем более я точно не мог совершить такую логическую ошибку.
С ошибками у меня вообще все в порядке. Особенно с логическими, не говоря уже о прочих. Написанный мной код работает с первого раза. Почти. И почти правильно. Иногда.
Однако идея про линтер показалась интересной.
Вызываю. Функция завершилась успешно. Но…
То, что функция успешно вызвана и одна ее ветка отработала, совсем не значит, что в ней нет хотя бы синтаксических или семантических ошибок, не говоря уже о логических. «Любая, даже самая простая программа содержит как минимум одну ошибку», — в одной из прошлых своих статей я писал об этом.
Но можно же если не по‑настоящему скомпилировать функцию, то хотя бы проверить ее на синтаксические и семантические ошибки? Можно. Для этого в среде разработки DBeaver, в которой я работаю, в редакторе функции есть кнопка «Проверка». Открываю свою функцию в редакторе, нажимаю эту кнопку.
Открывается новый SQL‑редактор, вижу в нем сгенерированный средой разработки код запроса. Запрос пытается автоматически выполниться, но не тут‑то было. Функции plpgsql_check_function, которую использует запрос, нет в БД.
На скриншоте видно, что у функции, которую DBeaver хотел вызвать, есть большое количество необязательных параметров, позволяющих менять набор выполняемых проверок. Кроме проверки на синтаксические, семантические и даже некоторые логические ошибки, можно проверять также код на наличие проблем производительности, на уязвимости и так далее Кроме того, проверяется даже динамический sql (хоть и не в полном объеме). То есть наши возможности по проверке написанного кода даже лучше, чем были бы в Oracle. Это позволяет изначально повышать качество кода.
Функция не существует. Потому что требуется специальное расширение plpgsql_check для PostgreSQL. Также оно описано здесь. Но почему его не устанавливают по умолчанию?
А потому что оно нужно не на продуктиве, не на нагрузочном стенде, не в БД тестирования, а только на БД разработки. Для PostgreSQL существует большое количество расширений, кому‑то нужно одно, кому‑то — другое. Если автоматически и сразу устанавливать даже то, что не нужно, это будет плохо сказываться на качестве работы СУБД. Будет расходовать больше памяти и CPU. Потому, если нам необходимо расширение, устанавливаем сами.
Ок, открываю psql под суперпользователем, подключаюсь к своей БД, выполняю команду
create extension plpgsql_check;
Тем самым я установил в свою БД линтер для языка PL/pgSQL. Но я установил его на свою существующую БД, а если я или кто‑то другой запустит накатку первоначального патча разрабатываемой нами системы, то она удалит существующую БД и создаст вместо нее новую. Придется устанавливать расширение вручную заново. Это неудобно. А зачем это делать вручную? Добавляю команду установки расширения прямо в скрипт накатки патча. Патч состоит из множества файлов, часть из них — это скрипты для установки для ОС linux, часть — для windows. Но нас интересует не операционная система, а скрипты для PostgreSQL, которые выполняет утилита psql.
Только не нужно устанавливать расширение при накатке на БД заказчика! А потому завожу отдельный скрипт install_debug.sql для установки патча и расширений на БД разработки.
Теперь нажимаю в среде разработки кнопку «Проверка» и получаю описание найденных ошибок в моей функции.
Так было до создания вызываемой из моей функции get_ct_map, созданной Алексеем Первым. Но теперь, после запуска инсталлятора нашей системы, такая функция есть, потому список выглядит по‑другому. Вот так:
Оказывается, я неверно написал название таблицы. Заменяю cars_history на car_history, сохраняю исправленную функцию.
Теперь ошибка в названии переменной — заменяю v_target_date на i_target_date.
А это еще что за ошибка в строке set? В PostgreSQL немного отличный от Oracle синтаксис апдейта, так как апдейтить можно только одну таблицу, тогда как внутри апдейта может быть задействовано несколько (это конструкция update таблица from список таблиц), то использование алиаса в разделе set не допускается, чтобы никого не путать. Убираю ch.
Если будет еще хоть одна ошибка, то…
Снова неудача. Неправильно вспомнил название столбца. Вместо end_time должен быть etime.
Ура, ошибок больше нет. Но есть предупреждение. Ошибки правят все, но о предупреждениях многие забывают. Впрочем, я не из таких. Проверяю, может я где‑то забыл использовать переменную v_result. Нет. Она правда не нужна. Удаляю.
Ура, победа! Нет больше ни ошибок, ни предупреждений.
Хотя, может я поторопился? Откачу пока последнюю правку, пусть одно предупреждение пока будет.
Ладно, ошибки найдены сейчас. Но что будет потом? Допустим, на другой фазе проекта, разработчик Игорь Неизвестный поменяет список или названия полей в таблице, или даже название таблицы. А я об этом даже не узнаю.
Или, скажем, поменяет список или тип параметров или тип возвращаемого значения в функции, которую вызывает моя функция. Кто тогда найдет возникшую из‑за этого семантическую ошибку в моей функции, которую никто даже не правил в этой фазе?
Тестировщики. Если ретест запланирован. Или регрессионное тестирование, если оно использует именно сломавшуюся ветку кода моей функции или проверяет все ветки функции. То есть нужно 100% покрытие кода функции регрессом. Но что‑то подсказывает мне, что 100% покрытия кода регрессионными автотестами нет и не будет. Ведь важно, в первую очередь, покрытие логики бизнес‑процессов, а не кода системы.
Необходим скрипт, который будет проверять все функции системы на синтаксические/семантические ошибки. И запускать его надо перед каждым коммитом в репозиторий любых изменений любого объекта системы. Или после каждой накатки патча «Карбэк» на БД разработки. Получим некий аналог того, как в Oracle при изменении любого объекта, все зависящие от него процедуры, функции, пакеты и типы становятся невалидными и требуют перекомпиляции. А при перекомпиляции ошибки, если они есть, будут найдены СУБД.
Пишу запрос, получающий список всех функций системы, кроме триггерных (prorettype=2279), и вызывающей линтер для каждой из них. Но пишу его слегка не так, как предлагается по ссылке выше с описанием расширения.
select *
from ( select p.oid,
p.proname,
(select array_agg(p) from plpgsql_check_function(p.oid) p) err
from pg_catalog.pg_namespace n
join pg_catalog.pg_proc p ON pronamespace = n.oid
join pg_catalog.pg_language l ON p.prolang = l.oid
where l.lanname = 'plpgsql'
and p.prorettype <> 2279 ) t
where t.err is not null;Потому что в моем варианте я могу запустить запрос без последней строки и увидеть список всех функций системы, которые он проверил. Должен же я убедиться, что проверяю все необходимое.
Запускаю полностью и вижу только список функций с ошибками или предупреждениями.
Все предупреждения сейчас относятся только к моей функции.
Отлично, теперь, выполнив запрос, я увижу, есть ли ошибки во всех функциях в моей БД. А если я не запущу его? Или кто‑то другой внесет изменения в объекты БД и забудет выполнить проверку?
Необходим скрипт для автоматического выполнения. Ок, сохраняю запрос в файл check.sql, добавляю его вызов в скрипт install_debug.sql для установки патча и расширений на БД разработки.
Запускаю инсталлятор нашей системы и вижу в логе сообщение
Теперь точно можно исправить единственное предупреждение. Или оно не было единственным? Почему‑то меня не покидало ощущение, что предупреждений было как минимум два … А может, стоило в вызов plpgsql_check_function передать all_warnings => true и увидеть большой список предупреждений? Хорошая мысль.
Глава 7. Автотест оптимальности структуры таблиц
Конечно, здорово, что я помню, в каком порядке лучше размещать столбцы в таблице в PostgreSQL. Но могу когда‑то и забыть, или таблицу создаст другой разработчик. А пустая трата N Гигабайт дискового пространства на каждую большую таблицу, которых может быть много, никому не нужна.
Потому есть смысл помимо кода процедур автоматически проверять и правильность порядка столбцов. Для этого нам нужен запрос, возвращающий полный список таблиц и столбцов, порядок следования которых не оптимален. В том порядке, который должен быть.
Названия только таблиц можно взять из information_schema.columns, номер столбца в таблице из pg_attribute, а полный список объектов есть в pg_class:
SELECT cd.relname "таблица",
a1.attname "столбец",
t.typname "тип данных",
t.typalign "тип выравнивания",
t.typlen "длина данных, байт",
a1.attnum "номер по порядку"
FROM pg_class cd
JOIN pg_attribute a1 ON (a1.attrelid = cd.oid)
JOIN pg_type t ON (t.oid = a1.atttypid)
WHERE a1.attnum >= 0
and cd.relname in ( select a.relname
from( SELECT c.relname, a.attname, t.typname, t.typalign, t.typlen, a.attnum,
lag(c.relname,1,c.relname) over(order by c.relname, t.typlen DESC, a.attnum) prev_rel,
lag(a.attnum,1,0) over(order by c.relname, t.typlen DESC, a.attnum) prev_att_num
FROM pg_class c
JOIN pg_attribute a ON (a.attrelid = c.oid)
JOIN pg_type t ON (t.oid = a.atttypid)
WHERE c.relname in (select c.table_name
from information_schema.columns c
where c.table_schema = current_schema())
AND a.attnum >= 0 ) a
where a.relname = a.prev_rel
and a.attnum < a.prev_att_num )
ORDER BY cd.relname, t.typlen DESC, a1.attnum
Хоть скрипт create table я уже переписал в нужном виде, но сама таблица на моей БД создана старой неоптимальной версией. Из результатов запроса видно, что столбцы stime и etime должны быть на 2-й и 3й позиции, а фактически они на 4-й и 5-й. Тогда как ct_id и num_history должны быть на 4й и 5й, а фактически на 2-й и 3-й.
Помещаю запрос также в файл check.sql. Теперь, установив патч нашей системы в режиме для БД разработки, я сразу буду знать не только об ошибках в функциях, но и о неоптимальных таблицах.
Глава 8. Модульные автотесты
Синтаксис и семантика — это важно, но также важна и логика функций. Да, я провел один ручной модульный тест: вызвал функцию и убедился, что она сделала именно то, что нужно. Вручную проверил результаты. Но я мог что‑то упустить.
А могу потом, когда буду дорабатывать эту же функцию, сломать логику — и не заметить. Или просто забуду проверить. Даже если не забуду, каждая такая проверка — затраты моего времени. Или это буду не я. Или при доработке не этой функции, а любого объекта в БД изменится поведение моей функции. Например, при изменении вызываемой из нее функции, а может используемой таблицы или типа.
Поэтому, во‑первых, модульный тест должен быть автоматическим, во‑вторых, он не должен быть единственным. Чем больше логики функции будут проверять модульные тесты, тем лучше. И так для всех функций системы. Конечно, модульные тесты не так важны, как функциональные, которые проверяют не отдельные функции, а работу системы в целом, согласно бизнес‑кейсам. Но функциональные напишут тестировщики, а я ‑ только модульные. Помните, я об этом рассказывал?
Что должен сделать модульный тест? Прежде всего — подготовить данные для проверки.
Мой первый кейс — это вызов функции, когда в таблице car_history ничего нет по вызываемому автомобилю. В итоге должна быть создана первая история с правильно заполненными идентификатором, номером истории, датами начала действия и окончания, а поле ct_id должно быть пустым.
Чищу таблицу, готовлю параметры для вызова моей функции и вызываю ее:
begin
truncate table car_history; -- до первого теста таблица пустая
v_target_date := current_timestamp::timestamp;
-- тест 1 - нарезка первой истории
v_result := update_car_history( i_car_id => v_car_id,
i_type => 2,
i_target_date => v_target_date );теперь определимся с ожидаемым результатом. В результате должна быть одна строка.
-- ожидаем одну историю
v_1st_history := row(v_car_id, -- car_id
null::smallint, -- ct_id
1::smallint, -- num_history
v_target_date, -- stime
v_etime) -- etime
::car_history;
v_tab_data := v_tab_data || v_1st_history;
посмотрим, что фактически есть в таблице
-- фактический результат
select array_agg(ch.*)
into v_fact_data
from car_history ch;
и выполним проверки
perform assert_equals(v_result, 'ok', '1. функция должна вернуть успех');
perform assert_equals(array_length(v_tab_data,1), 1, '1. в таблице должна быть 1 строка');
perform assert_equals(v_fact_data, v_tab_data,'1. поля таблицы должны совпадать');
Вторая проверка избыточна, но с ней более очевидно.
Итого мой первый тест выглядит так:
do $$
declare
v_result varchar;
v_target_date timestamp;
v_etime timestamp := to_date('31.12.2999','dd.mm.yyyy');
v_car_id bigint := 1;
v_tab_data car_history[]; -- ожидаемый результат
v_1st_history car_history; -- ожидаемый результат - первая история
v_fact_data car_history[]; -- фактический результат
begin
truncate table car_history; -- до первого теста таблица пустая
v_target_date := current_timestamp::timestamp;
-- тест 1 - нарезка первой истории
v_result := update_car_history( i_car_id => v_car_id,
i_type => 2,
i_target_date => v_target_date );
-- ожидаем одну историю
v_1st_history := row(v_car_id, -- car_id
null::smallint, -- ct_id
1::smallint, -- num_history
v_target_date, -- stime
v_etime) -- etime
::car_history;
v_tab_data := v_tab_data || v_1st_history;
-- фактический результат
select array_agg(ch.*)
into v_fact_data
from car_history ch;
-- проверки
perform assert_equals(v_result, 'ok', '1. функция должна вернуть успех');
perform assert_equals(array_length(v_tab_data,1), 1, '1. в таблице должна быть 1 строка');
perform assert_equals(v_fact_data, v_tab_data,'1. поля таблицы должны совпадать');
end $$;Вы спросите, что такое assert_equals? А я вам скажу — это функция Алексея Первого, она выглядит так:
-- Функция для проверки равенства значений
create or replace function assert_equals(actual anyelement, expected anyelement, message text)
returns void as $$
begin
if ( actual <> expected
or actual is null and expected is not null
or expected is null and actual is not null ) then
raise exception 'Assertion failed: % (actual: %, expected: %)', message, actual, expected;
end if;
end $$ language plpgsql;Тест успешно выполнен
Одного теста мне мало, потому сразу же пишу второй тест, проверяющий результаты второго вызова моей функции.
Выполняю второй вызов
-- тест 2 - нарезка второй истории
v_result := update_car_history( i_car_id => v_car_id,
i_type => 2,
i_target_date => v_target_date ); формирую ожидаемый результат
-- ожидаем две истории
v_1st_history.etime := v_target_date;
v_2nd_history := row(v_car_id, -- car_id
0::smallint, -- ct_id
2::smallint, -- num_history
v_target_date, -- stime
v_etime) -- etime
::car_history;
v_tab_data := null;
v_tab_data := v_tab_data || v_1st_history || v_2nd_history;получаю фактический
-- фактический результат
select array_agg(ch.*)
into v_fact_data
from car_history ch; выполняю проверки
perform assert_equals(v_result, 'ok', '2. функция должна вернуть успех');
perform assert_equals(array_length(v_tab_data,1), 2, '2. в таблице должны быть 2 строки');
perform assert_equals(v_fact_data, v_tab_data,'2. поля таблицы должны совпадать');Полный текст набора из двух тестов:
do $$
declare
v_result varchar;
v_target_date timestamp;
v_etime timestamp := to_date('31.12.2999','dd.mm.yyyy');
v_car_id bigint := 1;
v_tab_data car_history[]; -- ожидаемый результат
v_1st_history car_history; -- ожидаемый результат - первая история
v_2nd_history car_history; -- ожидаемый результат - вторая история
v_fact_data car_history[]; -- фактический результат
begin
truncate table car_history; -- до первого теста таблица пустая
v_target_date := current_timestamp::timestamp;
-- тест 1 - нарезка первой истории
v_result := update_car_history( i_car_id => v_car_id,
i_type => 2,
i_target_date => v_target_date );
-- ожидаем одну историю
v_1st_history := row(v_car_id, -- car_id
null::smallint, -- ct_id
1::smallint, -- num_history
v_target_date, -- stime
v_etime) -- etime
::car_history;
v_tab_data := v_tab_data || v_1st_history;
-- фактический результат
select array_agg(ch.*)
into v_fact_data
from car_history ch;
-- проверки
perform assert_equals(v_result, 'ok', '1. функция должна вернуть успех');
perform assert_equals(array_length(v_tab_data,1), 1, '1. в таблице должна быть 1 строка');
perform assert_equals(v_fact_data, v_tab_data,'1. поля таблицы должны совпадать');
-- тест 2 - нарезка второй истории
v_result := update_car_history( i_car_id => v_car_id,
i_type => 2,
i_target_date => v_target_date );
-- ожидаем две истории
v_1st_history.etime := v_target_date;
v_2nd_history := row(v_car_id, -- car_id
0::smallint, -- ct_id
2::smallint, -- num_history
v_target_date, -- stime
v_etime) -- etime
::car_history;
v_tab_data := null;
v_tab_data := v_tab_data || v_1st_history || v_2nd_history;
-- фактический результат
select array_agg(ch.*)
into v_fact_data
from car_history ch;
-- проверки
perform assert_equals(v_result, 'ok', '2. функция должна вернуть успех');
perform assert_equals(array_length(v_tab_data,1), 2, '2. в таблице должны быть 2 строки');
perform assert_equals(v_fact_data, v_tab_data,'2. поля таблицы должны совпадать');
end $$;Запускаю — и, как вы думаете, каков результат?
Глава 9. Анализа причины падения теста
Тест падает
SQL Error [P0001]: ERROR: Assertion failed: 2. поля таблицы должны совпадать (actual: {"(1,,1,\"2025-11-12 14:21:19.483611\",\"2025-11-12 14:21:19.483611\")","(1,0,1,\"2025-11-12 14:21:19.483611\",\"2999-12-31 00:00:00\")"}, expected: {"(1,,1,\"2025-11-12 14:21:19.483611\",\"2025-11-12 14:21:19.483611\")","(1,0,2,\"2025-11-12 14:21:19.483611\",\"2999-12-31 00:00:00\")"})
Где: PL/pgSQL function assert_equals(anyelement,anyelement,text) line 6 at RAISE
SQL statement "SELECT assert_equals(v_fact_data, v_tab_data,'2. поля таблицы должны совпадать')"
PL/pgSQL function inline_code_block line 56 at PERFORM
Как разобраться, что закодировал PostgreSQL в этом наборе скобок всех видов, кавычек, слешей и прочего непонятного текста? Об этом рассказывали здесь.
Красным я выделил, что с чем сравнивается и где есть отличия. Получается, что процедура во второй истории ошибочно проставила номер истории 1. Помните, я предлагал догадаться самим, какие нужны констреинты и индексы? Слегка приоткрою завесу тайны: таблице нужен первичный ключ (car_id, num_history), но у меня в БД его пока нет. Если б был, функция бы упала, а так она успешно выполняется, но работает некорректно. Но что в том случае, что в этом, модульный автотест ошибку находит.
Как не разбирать этот сложный текст, а посмотреть в таблице, что реально получилось? Выполняю запрос
select *
from car_history ch
order by ch.car_id,
ch.stime,
ch.etime,
ch.num_historyПусто. А почему? Потому что PostgreSQL, в отличие от Oracle, как только получил исключение, сразу откатывает текущую транзакцию. Я же не отключал включенное по умолчанию автоматическое управление транзакциями. А если бы и отключил, то закоммитить все равно не возможно. Вызвал бы commit, а система все равно выполнила бы rollback. Можно долго рассказывать, что Oracle откатывает только завершившийся с ошибкой оператор (insert/update/delete). А PostgreSQL откатывает только изменение той строки таблицы, на которой произошла ошибка, что некорректно. В результате, пока не выполнишь rollback никакая работа невозможна. И хотя с этим можно бороться через точки восстановления, они замедляют работу системы.
Но сейчас это все мне не важно. Чтобы увидеть в таблице, что получилось в результате выполнения тестов, нужно закомментировать команду, которая сгенерировала исключение. А именно — вот эту строку
perform assert_equals(v_fact_data, v_tab_data,'2. поля таблицы должны совпадать');Выполняю набор тестов заново. Теперь запрос.
Вот оно, у двух историй одинаковый num_history. Теперь нужно понять, где в моей процедуре ошибка вычисления num_history?
Глава 10. Отладка
Прежде всего, нам нужны данные в таблице, которые были на момент перед проявлением ошибки, то есть вторым вызовом функции. Чтобы их получить, в наборе тестов перед строкой
-- тест 2 - нарезка второй историиСтавлю команду
return; Запускаю тест. Теперь запрос.
Ок, исходные данные есть, что дальше?
А дальше есть два пути: либо отлаживать функцию, пошагово выполняя ее, либо просто выводить отладочную информацию из функции в консоль. Что нужно для отладки?
Если отладка ведется через DBeaver, то — установленные плагин к нему. У меня он есть. Если бы я пользовался стандартной для PostgreSQL средой pgAdmin4, то никакие плагины мне бы не понадобились.
Требуется специальное расширение для PostgreSQL. Выполняю под суперпользователем команду
create extension pldbgapi;Также добавляю ее же в скрипт install_debug.sql инсталлятора нашей системы (режим установки для БД разработки), чтобы никогда больше на этом проекте не запускать команду вручную.
Открываю свою функцию в редакторе функций, двойным щелчком слева ставлю точку останова на интересной мне строке. Чуть раньше того места, где вычисляется num_history.
Теперь нажимаю кнопку «debug», а в ее меню Debug Configuration. В открывшемся окне выбираю свою функцию и прописываю нужные мне значения параметров:
Нажимаю «Отладка» и оказываюсь внутри функции на нужной строке. Процесс очень похож на дебаг в Oracle SQL Developer, не так ли?
Теперь кнопки Step Into или Step Over. Дохожу до строки
v_num_history := v_num_history + 1;выполняю ее тоже. И что же я вижу в списке переменных и параметров функции?
Переменная v_num_history как была NULL, так NULL и осталась. А действительно ли я хотел работать с этой переменной? Просматриваю код функции выше, вижу, что из текущей истории я заполняю переменную v_rec.num_history. Теперь смотрю код ниже и вижу, что в таблицу вставляется все тот же v_rec.num_history. В v_num_history значение не присваивается и не используется ни выше, ни ниже. А значит, v_num_history не нужен, его упоминание — ошибка. Заменяю
v_num_history := v_num_history + 1;на
v_rec.num_history := v_rec.num_history + 1;Удаляю переменную v_num_history. Выполняю набор тестов полностью. Тесты успешно проходят. Кстати, после указанных двух тестов, я написал еще несколько новых.
Это была отладка в текущей сессии. Но возможна и отладка удаленной сессии — когда неизвестно, кто и с какими параметрами вызывает функцию и какие данные в таблицах на момент вызова, но точно известно, какую функцию нужно пошагово выполнить.
Процесс отладки может быть достаточно долгим, потому в ряде случаев стоит ее избегать.
Как добиться того же результата без отладки, путем вывода отладочной информации в консоль? Нужно добавить в код функции команду raise notice.
Выполняю набор тестов и вижу в консоли
v_ct_map_tab={1,5,8,11} , v_rec.num_history=2Эти отладочные сообщения могут быть полезны и в дальнейшем, потому не стоит их совсем убирать из функции после того, как проблема решена. Лучше просто закомментировать.
Завожу в инсталляторе директорию unit, складываю в нее все написанные модульные тесты для своей функции в файл update_car_history.sql, добавляю вызов update_car_history.sql в install_debug.sql.
Коммичу в репозиторий разработанную функцию и набор тестов. Перехожу к разработке следующей. У каждой функции будет свой набор модульных тестов.
Тем временем, разработанный мной код уходит на ревью к другому нашем опытному разработчику, Алексею Первому.
Эпилог
При установке на БД разработки наша система, работающая на PostgreSQL, автоматически:
— проверяет оптимальность расположения столбцов во всех своих таблицах;
— ищет синтаксические, семантические (в том числе частично в динамическом SQL, если таковой используется) и частично логические ошибки, а также проблемы производительности и уязвимости — для этого устанавливается расширение plpgsql_check;
— проверяет корректность работы всех функций системы набором модульных тестов;
— устанавливает расширение pldbgapi для возможности отладки.
Далее, если накатка проводилась на БД автотестирования, запускается набор автоматических функциональных, интеграционных тестов. Если это БД нагрузочного тестирования, выполняются нагрузочные тесты.
При разработке не забываем о ревью кода другими опытными разработчиками.
На выходе имеем высококачественный продукт. Даже если кто‑то из разработчиков, участвовавших в проекте, привык писать код для других СУБД.
При установке на БД заказчика ни одно из описанных выше действий не выполняется.
В этой статье я рассмотрел далеко не все особенности и отличия СУБД PostgreSQL от Oracle, для рассмотрения всех не хватило бы книги на 1000 страниц. Эта статья — только начало.
2 в 4-й степени дней спустя
Сегодня завершилась разработка системы «Карбэк». Мы передали ее на тестирование и приступили к новому проекту — «Хетчбэк». Группа тестирования «Карбэк» состоит из десяти тестировщиков, каждому нужно проверить по 30 кейсов.
2 в 4-й степени недель спустя
Сегодня мы подводим итоги проекта. Проект оказался крайне успешным. На тестировании не было найдено ни одной критичной ошибки со стороны логики БД, а некритичных было крайне мало. Тестирование завершилось даже раньше запланированных сроков.
2 в 4-й степени лет спустя
Проект «Карбэк» отлично работает уже полтора десятилетия, даже больше. Уже мало кто помнит, кто, как и зачем его проектировал, разрабатывал и тестировал. Но для всех он до сих пор является образцом того, как нужно разрабатывать качественный код.