1. СТРОГОЕ СРАВНЕНИЕ ЗНАЧЕНИЙ В ЗАПРОСАХ
Есть к примеру запрос, который обновляет некоторые поля данными из исходной таблицы в случае их отличия от данных в таблице назначения:
update dest set name=src.name from src where dest.id=src.id and dest.name<>src.name;
Проблема заключается в том, что одно из полей name в таблицах dest или srcможет быть NULL, а следовательно не может обрабатываться оператором сравнения <>. В таком случае поле в таблице назначения src обновлено не будет.Раньше для решения этой проблемы я пользовался функциeй проверки значения на NULL - coalesce. Мой код выглядел неоптимально:
update dest set name=src.name from src where dest.id=src.id and coalesce(dest.name,'')<>coalesce(src.name,'');
Если обновление происходит для нетекстового поля, то вместо приведения к пустой строке в случае NULL, нужно приводить к 0. Пример:
update dest set numcol=src.numcol from src where dest.id=src.id and coalesce(dest.numcol ,0)<>coalesce(src.numcol ,0);
Постоянно писать проверку на NULL для каждого обновляемого поля утомительно.
Поэтому я открыл для себя оператор IS DISTINCT FROM.
Данный оператор по своей функции схож с оператором <>, и возвращает true, если правая и левая части разные и false, если они одинаковые ( с учетом NULL в этих значениях).
Оператор IS NOT DISTINCT FROM похож на оператор =, но также учитывает с NULL.
Проиллюстрирую работу этих операторов на примерах:
select NULL IS DISTINCT FROM NULL; --Результат false select NULL IS NOT DISTINCT FROM NULL; --Результат true select NULL IS NOT DISTINCT FROM 10; --Результат false select NULL IS NOT DISTINCT FROM 'abc'; --Результат false select 10 IS NOT DISTINCT FROM 10; --Результат true select 'abc' IS DISTINCT FROM 'abc'; --Результат false
Таким образом, оптимизируя код первого запрос, я написал следующее:
update dest set name=src.name from src where dest.id=src.id and dest.name IS DISTINCT FROM src.name;
IS DISTINCT FROM или IS NOT DISTINCT FROM следует использовать вместо <> или = там, где в сравниваемых значениях возможны NULL.
2. ЗАМЕДЛЕНИЕ РАБОТЫ ЗАПРОСА
Иногда при работе некоторых функций возникает взаимоблокировка транзакций (deadlock).
По логам видно, что 2 функции вызвали взаимоблокировку. При этом одна из них может достаточно быстро отрабатывать свою задачу и запустив их в разных окнах PgAdmin можно не получить deadlock. В таких ситуация возникает потребность замедлить работу функции или запроса.
Так я открыл для себя функцию pg_sleep(nsec). Функция замедляет выполнения запроса, на nsec секунд. Таким образом, чтобы замедлить мою исходную тестируемую функцию MyFunc на 10 секунд можно выполнить следующий код:
select *,pg_sleep(10) from MyFunc();
3. РЕАЛИЗАЦИЯ ЗАПРОСОВ В ФУНКЦИЯХ В ЗАВИСИМОСТИ ОТ ЗНАЧЕНИЙ ВХОДНЫХ ПАРАМЕТРОВ ФУНКЦИИ
Представим себе функцию CountOfGoodsByBrandID, которая на вход принимает параметр v_brand_id. Назначение функции - вычислять количество товаров в таблице goods, принадлежащих указанному бренду-параметру.
Однако если функция принимает в качестве v_brand_id=0, то необходимо выдать общее количество товаров.
Напишем код функции с учетом этого нюанса:
create or replace function public.CountOfGoodsByBrandID(v_brand_id int default 0) RETURNS int security definer as $$ declare result int; BEGIN if v_brand_id<>0 then select count(1) into result from goods where brand_id=v_brand_id; else select count(1) into result from goods; end if; return result; END; $$ language plpgsql;
Для поддержки различной логики в зависимости от значения v_brand_id приходится реализовывать громоздкую
конструкцию:
if v_brand_id<>0 then select count(1) into result from goods where brand_id=v_brand_id; else select count(1) into result from goods; end if;
помимо длинной реализации проблема заключается в потенциальной возможности усложнения кода за счет появления еще одного входного параметра, который как может иметь какое-либо значение, или же можеть быть NULL. Добавим в функцию входной параметр v_is_domestic (значение 1 - товар отечественный, 0 - импортный, NULL - выборка всех товаров). Тогда реализация логики выбора запроса будет следующей:
if (v_brand_id<>0 and v_is_domestic is not null) then select count(1) into result from goods where brand_id=v_brand_id and is_domestic=v_is_domestic; ELSIF (v_brand_id<>0 and v_is_domestic is null) then select count(1) into result from goods where brand_id=v_brand_id; ELSIF (v_brand_id=0 and v_is_domestic is not null) then select count(1) into result from goods where is_domestic=v_is_domestic; ELSIF (v_brand_id=0 and v_is_domestic is null) then select count(1) into result from goods; end if;
Функция усложняется, количество кода растет. Данную логику можно упростить, используя всего один запрос, при выполнении, обрабатывающий значения входных параметров.
1)
select count(1) into result from goods where ((brand_id=v_brand_id and v_brand_id<>0) or (v_brand_id=0)) and ((is_domestic=v_is_domestic and v_is_domestic is not null) or (v_is_domestic is null));
;
Количество кода существенно сократилось. Вся логика обработки параметров функции перешла в тело запроса.
В качестве альтернативы данному запросу можно написать запрос с использованием оператора CASE:
2)
select count(1) into result from goods where
case when (v_brand_id=0 and v_is_domestic is null) then 1=1 when (v_brand_id<>0 and v_is_domestic is null) then brand_id=v_brand_id when (v_brand_id=0 and v_is_domestic is not null) then is_domestic=v_is_domestic else brand_id=v_brand_id and is_domestic=v_is_domestic end;
Он длинее, чем предыдущий, и более понятен. Однако для профессионального программирования функции с поддержкой различных значений параметров я использую реализацию 1 (см. выше).
Таким образом, наша функция после оптимизации логики будет иметь следующий вид:
drop function if exists public.CountOfGoodsByBrandID(int,int); create or replace function public.CountOfGoodsByBrandID(v_brand_id int default 0,v_is_domestic int default NULL) RETURNS int security definer as $$ declare result int; BEGIN select count(1) into result from goods where ((brand_id=v_brand_id and v_brand_id<>0) or (v_brand_id=0)) and ((is_domestic=v_is_domestic and v_is_domestic is not null) or (v_is_domestic is null)); return result; END; $$ language plpgsql;
4. КОНКАТЕНАЦИЯ ЗНАЧЕНИЙ ПАРАМЕТРОВ В ДИНАМИЧЕСКОМ SQL
В своих функциях я часто использую динамически формируемые запросы. Значения в текст таких запросов добавляются с помощью оператора конкатенации (||)
Пример:
EXECUTE 'SELECT id,name,brand_id from public.goods where brand_id=' || v_brand_id::text || ' and is_domestic=' || v_is_domestic::text || ';'
Код запроса здесь соседствует с параметрами и при увеличении количества параметров или текста кода запроса возрастает сложность поддержки. Хотелось бы функцию, позволяющую отделить текста запроса от подставляемых в него значений, как например sprintf в PHP. И такая замечательная функция есть (format) , вот реализация формирования динамического sql c ее помощью:
EXECUTE format('SELECT id,name,brand_id from public.goods where brand_id=%s and is_domestic=%s;',v_brand_id::text,v_is_domestic::text);
Подробнее о функции http://www.postgresql.org/docs/9.1/static/functions-string.html
Благодаря format, поддержка и понимание кода упрощается.
5. ВЫПОЛНЕНИЕ ФУНКЦИЙ ПОД ПРАВАМИ СОЗДАТЕЛЯ
Иногда приходится модифицировать код sql или pl/pgsql -функций и выкладывать их на рабочем сервере, для того, чтобы пользователи сайта могли запускать данные функции. Конечно пользователи запускают функции не напрямую, а делая запросы к сайту, взаимодействуя с интерфейсом сайта.
Сайт подключен к БД под учеткой с ограниченными правами. Это делается для того, чтобы злоумышленники, найдя дыру в безопасности сайта, не смогли провести атаку типа sql-injection.
Функции же создаются и обновляются под учеткой с админскими правами на сервере Postgresql.
Когда учетка сайта обращается к данной функции - возникает ошибка доступа. Даже если владельцем функции назначена учетка сайта, то все равно возникает проблема, т.к. функция при выполнении может использовать таблицы и другие объекты, доступ к которым для учетки сайта закрыт.
Поэтому, чтобы избежать таких ошибок с правами учетки, я все функции создаю и обновляю с директивой SECURITY DEFINER.
Данная директива говорит о том, что права на доступ к объектам БД функция при работе берет не от запустившей ее учетки, а от создавшей ее учетки.
CREATE FUNCTION see_goods() RETURNS SETOF goods AS $$ SELECT * FROM goods $$ LANGUAGE SQL SECURITY DEFINER;
Таким образом, запустить функцию может ограниченная в правах учетка, а выполняется функция с правами админа. Если код в функции некритичен и
подделка запроса внутри нее невозможна, то SECURITY DEFINER - вполне безопасная и удобная директива для развертывания функций.
