Российская СУБД Tantor: отечественные базы данных на PostgreSQL, профессиональная коммерческая система управления БД многопользовательская, разработка на русском языке
Этот сайт использует файлы cookie для улучшения пользовательского опыта. Подробнее...
ОК
Материалы блога Planet PostgreSQL

Понимание изменчивости в функциях PL/pgSQL: практический урок

Язык PL/pgSQL, доступный в качестве расширения по умолчанию в PostgreSQL, предоставляет разработчикам приложений мощные инструменты и гибкость для создания сложных функций корпоративного масштаба в базе данных. С помощью функций и процедур PL/pgSQL разработчики могут выбирать различные категории волатильности, которые обеспечивают различные преимущества в производительности, особенно с точки зрения кэширования результатов и осведомленности о состоянии данных. Для более глубокого погружения обратитесь к официальной документации. Выбор подходящей волатильности для функции имеет решающее значение; как говорится: «С большой силой приходит большая ответственность».IMMUTABLE, STABLE, or VOLATILE

Во время одного из моих проектов по миграции мы столкнулись с неожиданным поведением при отладке кода, преобразованного из Oracle. Ниже приведен воссозданный пример с использованием фиктивных функций для иллюстрации проблемы:
truncate table user_status;
create table user_status(col1 bigint GENERATED BY DEFAULT AS IDENTITY , status text);
 
create or replace function upd_status(text)
returns bigint language plpgsql 
as $$
declare var1 bigint;
begin
with alias1 as (insert into user_status(status) values($1) returning col1)
        select * into var1 from alias1;
return var1;
end;$$;
 
create or replace function lookup_status(text)
returns boolean language plpgsql 
immutable parallel safe
as $$
begin return (select exists (select 1 from user_status where status = $1)); end;$$;
 
create or replace function check_status(text)
returns void language plpgsql 
as $$ 
declare var1 bigint;
begin
var1 := upd_status($1);
if lookup_status($1) then
    raise notice 'Status exists as part of immutable check - %', $1;
else
    raise notice 'Status does not exists - %', $1;
end if;
raise notice 'Actual Result for status % is %', $1,(select exists (select 1 from user_status where status = $1 and col1 = var1));
end;$$;
Если вы наблюдаете за выводом, состояние вставляется в него с помощью вызова, но любой вызов в пределах одного и того же снимка не обнаруживает это изменение данных. Openuser_statuscheck_statuslookup_status
Потребовалось некоторое время, чтобы диагностировать основную причину: он был неправильно помечен как выполнен инженером по преобразованию, в результате чего он возвращал устаревшие результаты, не фиксируя изменения в состоянии данных в одном и том же снимке.lookup_statusIMMUTABLE

Волатильность функции – матрица решений

Выбор изменчивости обеспечивает повышение производительности, но также может привести к проблемам с согласованностью данных, если его тщательно не учитывать, особенно при переносе кода из Oracle или SQL Server. Ниже приведено руководство по выбору подходящей волатильности для разных вариантов использования:
Ниже приведено руководство по выбору подходящей волатильности для разных вариантов использования:
После обновления изменчивости to он заработал как ожидалось, корректно отражая изменения состояния как часть снимка вызывающего объекта:lookup_statusSTABLE
create or replace function lookup_status(text)
returns boolean language plpgsql 
stable parallel safe
as $$
begin return (select exists (select 1 from user_status where status = $1)); end;$$;
Тщательно настраивая соответствующую волатильность, мы можем добиться надежной функциональности и производительности, что особенно важно при работе с процедурным кодом в базах данных.

Заключение

Установка правильного уровня волатильности в функциях PostgreSQL может значительно повлиять на производительность и согласованность данных. При разработке или переносе кода всегда оценивайте, следует ли пометить функции как IMMUTABLE, STABLE или VOLATILE, чтобы избежать непредвиденных результатов.
  1. Используйте функцию VOLATILE для функций с побочными эффектами или изменяющимися значениями.
  2. Выбирайте STABLE, если требуются стабильные результаты в транзакциях.
  3. Ограничьте IMMUTABLE функциями без внешних зависимостей.