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

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