Слишком много параметров для raise
Слишком много параметров для raise
Команда RAISE предназначена для вывода сообщений и вызова ошибок.
В следующем примере символ % будет заменён на значение v_job_id :
При помощи USING и последующих элементов параметр = выражение можно добавить дополнительную информацию к отчёту об ошибке. Все выражения представляют собой строковые выражения. Возможные ключевые слова для параметра следующие:
Предоставляет детальное сообщение об ошибке. HINT
Предоставляет подсказку по вызванной ошибке. ERRCODE
Предоставляет имя соответствующего объекта, связанного с ошибкой.
Этот пример прерывает транзакцию и устанавливает сообщение об ошибке с подсказкой:
Следующие два примера демонстрируют эквивалентные способы задания SQLSTATE :
У команды RAISE есть и другой синтаксис, в котором в качестве главного аргумента используется имя или код SQLSTATE ошибки. Например:
Предложение USING в этом синтаксисе можно использовать для того, чтобы переопределить стандартное сообщение об ошибке, детальное сообщение, подсказку. Ещё один вариант предыдущего примера:
И заключительный вариант, в котором RAISE не имеет параметров вообще. Эта форма может использоваться только в секции EXCEPTION блока и предназначена для того, чтобы повторно вызвать ошибку, которая сейчас перехвачена и обрабатывается.
Примечание
Если в команде RAISE EXCEPTION не задано ни имя условия, ни код SQLSTATE, по умолчанию выдаётся исключение ERRCODE_RAISE_EXCEPTION ( P0001 ). Если не задан текст сообщения, по умолчанию в качестве этого текста передаётся имя условия или код SQLSTATE.
Примечание
41.8.2. Проверка утверждений
Слишком много параметров для raise
Команда RAISE предназначена для вывода сообщений и вызова ошибок.
В следующем примере символ % будет заменён на значение v_job_id :
При помощи USING и последующих элементов параметр = выражение можно добавить дополнительную информацию к отчёту об ошибке. Все выражения представляют собой строковые выражения. Возможные ключевые слова для параметра следующие:
Предоставляет детальное сообщение об ошибке. HINT
Предоставляет подсказку по вызванной ошибке. ERRCODE
Предоставляет имя соответствующего объекта, связанного с ошибкой.
Этот пример прерывает транзакцию и устанавливает сообщение об ошибке с подсказкой:
Следующие два примера демонстрируют эквивалентные способы задания SQLSTATE :
У команды RAISE есть и другой синтаксис, в котором в качестве главного аргумента используется имя или код SQLSTATE ошибки. Например:
Предложение USING в этом синтаксисе можно использовать для того, чтобы переопределить стандартное сообщение об ошибке, детальное сообщение, подсказку. Ещё один вариант предыдущего примера:
И заключительный вариант, в котором RAISE не имеет параметров вообще. Эта форма может использоваться только в секции EXCEPTION блока и предназначена для того, чтобы повторно вызвать ошибку, которая сейчас перехвачена и обрабатывается.
Примечание
Если в команде RAISE EXCEPTION не задано ни имя условия, ни код SQLSTATE, по умолчанию выдаётся исключение ERRCODE_RAISE_EXCEPTION ( P0001 ). Если не задан текст сообщения, по умолчанию в качестве этого текста передаётся имя условия или код SQLSTATE.
Примечание
40.8.2. Проверка утверждений
Типичные ошибки при работе с PostgreSQL. Часть 2
Мы продолжаем публиковать видео и расшифровки лучших докладов с конференции PGConf.Russia 2019. В первой части доклада Ивана Фролкова речь шла о непоследовательном именовании, о constraints, о том, где лучше сосредоточить логику — в базе или в приложении. В этой части вас ждет разбор обработки ошибок, конкурентного доступа, неотменяемых операций, CTE и JSON.
Расскажу такую историю. Наш клиент говорит: «Медленно работает база, а наше приложение занимается обслуживаем населения. Мы боимся, что нас тут поднимут на вилы». Выяснилось, что у них было очень много процессов в состоянии idle in transaction. Приложение начало транзакцию, ничего не делает, но и транзакцию не завершает. Если вы взаимодействуете с какими-то внешними сервисами, то, в принципе, это нормальная ситуация. Другое дело, что если у вас состояние idle in transaction длится долго (больше минуты уже подозрительно), то это плохо потому, что PostgreSQL очень не любит долгие транзакции: VACUUM не сможет почистить все те строки, которые он мог бы увидеть, и долго висящая транзакция эффективно блокирует VACUUM. Начинают разбухать таблицы, индексы становятся всё менее эффективными.
В данном случае люди не вполне корректно писали запросы и получали декартовы произведения — такие запросы выполнялись по несколько дней. Ну а пользователь, он же нажмет кнопку, подождет результата и, если результата нет, снова нажмет кнопку.
Но это не объясняло, почему у них появляется столько процессов в idle in transaction. А появлялись они вот в какой ситуации: приложение лезет в базу, начинает транзакцию, лезет на какой-то внешний сервис, получает там ошибку, а дальше всё просто осыпается, печатаем в лог stack trace, и на этом успокаиваемся. Соединение остается заброшенным, висит и мешается.
Что с этим делать? Во-первых, надо обрабатывать ошибки всегда. Если к вам прилетела ошибка, пожалуйста, не игнорируйте ее. Хорошо еще, если PostgreSQL потерял соединение: он откатит транзакцию, переживём. На этом я еще остановлюсь. Ну а если есть код, который править совсем нет времени, то у нас еще есть max idle in transaction — можно поставить, и будет просто вышибать неактивные транзакции.
Типичный случай «обработки» ошибок: EXCEPTION WHEN OTHERS THAN NULL. Как-то мы спорили с коллегой о терминологии. Я говорил, что это переводится как «гори оно всё синим пламенем», а он — «пропади оно всё пропадом». Если у нас нечто плохое произошло, то, даже если всё с руганью осыпалось в лог, это всё же лучше, чем полная тишина — как здесь.
Если вы не знаете, что делать с ошибкой, то не надо ее и перехватывать. Очень распространенная практика: перехватили ошибку, записали в лог и побежали дальше, как будто ничего не произошло. Если вы, опять же, занимайтесь денежными операциями, и у вас произошла ошибка, которую вы проигнорировали, результаты могут быть непредсказуемые. В 90-е годы могли в лес, например, вывезти в багажнике. Сейчас времена помягче стали, но тоже мало приятного.
Если делаем операцию на клиенте, то, обычно, возвращаем значение: всё прошло удачно либо неудачно. И каждую ошибку обрабатываем. Я видел, как люди специально писали код plpgsql, где перехватывали ошибку, писали в лог, что, мол, да, была ошибка и довольно грубая, вставляли свой текст сообщения. Но SQLSTATE не возвращали. Это выполняется всегда, поэтому, если они забывали что-то проверить, то у них начинались проблемы.
Все, почему-то боятся исключений — как в plpgsql, так и в других языках. А если не придумывать что-то свое, а пользоваться стандартными возможностями языка, все обычно получается хорошо. Особенно эта проблема часто встречается, когда падает соединение. Оно упало, процесс idle in transaction, база заполняется, падает производительность. Между прочим, такая транзакция может еще оставить блокировки, но это, почему-то, встречается не так часто. Поэтому добавляйте в код обработки ошибки finally и там вычищайте соединение, отдавайте его обратно серверу.
Более того, в случае, если у вас хорошо, правильно поименованы constraint-ы, вы можете уже при обработке ошибки выкинуть исключение не из базы, а из приложения. В spring есть exception translation, в php, соответственно, set_exception_handler. Обратите внимание на те средства, которые вам предоставляет ваш фреймворк, они там неспроста появились.
Итак: не надо перехватывать ошибку, с которым не знаете что делать; именуйте ошибки тщательно и аккуратно; классифицируйте ошибки.
Лично я классифицирую по таким критериям: операцию можно повторить (например, у нас возник deadlock); операцию повторить нельзя, она уже выполнена; операция не может быть выполнена в принципе.
Как ни парадоксально, с точки зрения приложения, ситуации, когда возникнет deadlock, когда потеряно соединение и когда у нас кончились деньги для выплаты, — это ситуации одинаковые: обработчик ошибки попытается через некоторое время выполнить операцию снова.
С другой стороны, что пишут в приложении, в общем-то, не мое дело: я занимаюсь базой. Я лишь призываю аккуратно обрабатывать ошибки, иначе: idle in transaction, залоченные строки, пухнущие базы и так далее.
Большинство разработчиков считает, что они работают с базой одни, и их приложение выполняет операции строго последовательно. И это плюс всем реляционным СУБД потому, что, как ни странно, при этом всё работает, как правило, очень хорошо, даже со стандартным уровнем изоляции READ COMMITTED, а не SERIALIZABLE. В то же время, случаются ситуации, когда теряются обновления: один грузит форму, другой грузит эту же форму, один написал и сохранил, другой сохранил старую — изменения стерли. Первый пришел ругаться: «как же так, я столько написали, и всё потеряно».
Из моего опыта: раз в неделю по пятницам два менеджера проводили выплаты. Они должны
были меняться через раз, но, тем не менее, однажды полезли одновременно и сделали две выплаты на одного человека. Если у вас есть хоть какая-то возможность ошибки конкурентного доступа, она рано или поздно случится. Вопрос когда.
Кроме того, обращаю ваше внимание на ограничения. Я неоднократно видел, как уникальность пытались обеспечить триггерами. Сходу триггерами уникальность в таблице вы не обеспечите. Либо вам тогда нужно будет блокировать всю таблицу, либо делать еще какие-то сложные телодвижения. Вы рано или поздно на этом споткнетесь.
Пару раз натыкался на совершенно кошмарную вещь: из базы вызывается внешний web-сервис. Там проводились какие-то операции, изменяющие внешние сущности. Это плохо тем, что в базе транзакция может откатиться, но операции на удаленном сервисе откачены не будут.
Еще более тонкий момент — deadlock-и. Давайте представим: мы обрабатываем транзакцию, вызываем внешний web-сервис, что-то поменяли, после этого у нас возникнет deadlock, и мы откатываемся, потом пытаемся выполнить операцию еще раз, вызываем еще раз, при хорошем стечении обстоятельств еще раз возникает deadlock, опять откатываемся — так может
происходить много раз (я натыкался на пару сотен повторов). И вот вы обрабатываете эти deadlock-и более-менее корректно, повторяете операции и вдруг обнаруживаете, что уже в течении двух месяцев выплачиваете кому-то двойную сумму.
Я встречался с платежными сервисами, у которых был небогатый API: «выплатить такую-то сумму такому-то пользователю»; функция возвращает результат — выплачено / не выплачено. Во-первых, возникает проблема в случае повтора, во-вторых, непонятно, что делать, если прервалось соединение. Почему-то на эту тему тоже очень мало кто заморачивается.
На слайде пример: такая операция должна выполняться в два этапа: как бы предупреждение — «будем сейчас что-то делать»; сама операция.
Если мы вдруг прервёмся — мало ли, выключили питание — мы сможем повторно выполнить операцию. Если мы сдохли на втором этапе, то, по крайней мире, второй раз мы это не выполним, и это можно будет разобрать вручную. На самом деле подавляющее большинство таких операций нормально отрабатывает первый раз, но эти меры не теоретические измышления. Всё может нормально работать месяцами, и вдруг админ начинает мудрить с сетью, сервис начинает активно мигать — и начались проблемы.
На слайде 4 типа неотменяемых операций. Последний — неидемпотентные операции. Это совсем грустный случай. Я в начале говорил о товарище, который всё делал на триггерах именно чтобы обеспечить идемпотентность своих операций.
На конференции люди будут рассказать о Common Table Expressions, о том, как это хорошо. К сожалению, CTE в PostgreSQL не бесплатны: они требуют под себя work_mem. Если у вас выборка небольшая, то, в общем, ничего страшного. А если вдруг у вас она большая, то у вас начинаются проблемы. Люди очень часто используют CTE в качестве этаких мини-вьюшек — для того, чтобы можно было как-то структурировать приложение. CTE очень востребованные.
Можно сделать временные view, но, к сожалению, каждое занимает строчку в pg_class, и если это очень активно используется, то возможны проблемы с распуханием каталога.
В этом случае можно посоветовать сделать параметризированное view, либо динамически формировать запрос, но, к сожалению, в PostgreSQL изнутри с этим не очень здорово.
О JSON обычно рассказывают в превосходных тонах, но есть тенденция в приложении в JSON пихать вообще все что угодно. В принципе, всё работает неплохо. С другой стороны, из JSON-а данные достаются хоть и быстро, но не так быстро, как из колонок. Еще хуже, если у вас JSON большой, и его вынесло в TOAST. Чтобы JSON оттуда взять, его нужно поднять из TOAST-а.
Если все колонки в JSON-е, по ним даже построен функциональный индекс, то все равно оттуда доставать надо. Еще хуже получается при большом объеме, когда база большая, когда у вас bitmap index scan. Тогда у нас ссылки не на строки, а на целую страницу, и, для того, чтобы понять, что со страницы брать, PostgreSQL сделает Recheck, то есть он поднимает строчку из TOAST и проверяет, есть там это значение или нет, и соответственно уже пропускает или не пропускает. Если с небольшими колонками это работает хорошо, то с JSON это большая проблема. Слишком увлекаться JSON-ами не надо.
— Как проверять, когда со строкой работают несколько пользователей? Какие варианты есть?
— Во-первых, можно перед показом строки в форме вЫчитать значения всех колоночек и убедиться, что они не поменялись. Второй вариант, более удобный: высчитать хэш на всех
колонках, тем более, что колонки там могут быть большие и толстые. А хэш не такой большой.
— Вы говорите, что что надо именовать constraint-ы хорошими именами, чтобы пользователь мог понять, что происходит. Но есть ограничение в 60 символов на имя constraint-а. Этого часто не хватает. Как с этим бороться?
— Думаю, бороться самоограничением. В PostgreSQL это специальный тип длиной 64. В принципе можно перекомпилировать на бОльшую длину, но это не очень хорошо.
— В докладе вы заинтриговали нас тем, что нам надо делать что-то с архивами. Какой механизм вынесения устаревших данных в архив считается самым правильным?
— Как я в самом начале уже говорил, при должном усердии работает всё. Какой способ вам наболее удобен, тем и пользуйтесь.
Timing: 2-я часть доклада начинается с 25:16
— Есть некая процедура, которую вызывают параллельно несколько пользователей. Как ограничить параллельное выполнение этой процедуры, то есть выстроить всех
пользователей в очередь так, чтобы, пока один не закончит выполнение процедуры, следующий не мог начать ее использовать?
— Именно процедура? Или достаточно транзакции?
— Именно процедура, которая вызывается в некоторой транзакции.
— Вы можете поставить блокировку на объект. Были бы сложности, если б у вас было условие, скажем, не больше 3 одновременно. Но и это реализуемо. Я обычно использую транзакционные блокировки, но можно и внетранзакционные.
— Я бы хотела все-таки еще раз вернуться к архивным данным. Вы говорили о
возможности хранения архива так, чтобы из приложения данные были также доступны. Мне приходила в голову мысль просто сделать отдельную архивную базу. Какие еще есть варианты?
— Да, можно сделать архивную базу. Вы можете написать функцию и завернуть ее во вьюшечку. В функции вы можете творить всё, что в голову взбредет: можете в архивную базу ходить, можете поднимать с диска какие-то файлы, можете ходить к внешнему web-сервису, можете всё это комбинировать, можете сами какие-то случайные данные генерить — выбор ограничен только фантазией.
— К вопросу по поводу архивных данных: можно использовать партиции — новые фишки 11-й версии, когда делаем всю таблицу парционированной, а потом просто детачим партицию и оставляем ее как архив. К ней тоже можно осуществлять доступ.
— Конечно, почему бы нет. Уступаю место следующему докладчику.
Пользовательские агрегатные и оконные функции в PostgreSQL и Oracle
В этой статье мы посмотрим, как в двух системах создавать пользовательские агрегатные и оконные (в терминологии Oracle — аналитические) функции. Несмотря на различия в синтаксисе и в целом в подходе к расширяемости, механизм этих функций очень похож. Но и различия тоже имеются.
Надо признать, что собственные агрегатные и оконные функции встречается довольно редко. Оконные функции вообще по каким-то причинам традиционно относят к разряду «продвинутого» SQL и считают сложными для понимания и освоения. Тут бы разобраться с теми функциями, которые уже имеются в СУБД!
Зачем тогда вообще вникать в этот вопрос? Могу назвать несколько причин:
Агрегатные функции
Будем двигаться от простого к сложному, переключаясь между PostgreSQL и Oracle.
Вначале некоторые общие соображения. Любая агрегатная функция вызывается для каждой строки таблицы по очереди и в конечном итоге обрабатывает их все. Между вызовами ей требуется сохранять внутреннее состояние, определяющее контекст ее выполнения. В конце работы она должна вернуть итоговое значение.
Итак, нам потребуется четыре составляющие:
PostgreSQL
Для хранения состояния нужно выбрать подходящий тип данных. Можно взять стандартный, а можно определить свой. Для функции, вычисляющей среднее, нужно отдельно суммировать значения и отдельно подсчитывать их количество. Поэтому создадим свой составной тип с двумя полями:
CREATE TYPE average_state AS (
accum numeric,
qty numeric
);
Теперь определим функцию для обработки очередного значения. В PostgreSQL она называется функцией перехода:
Кроме этого, мы выводим (RAISE NOTICE) параметры функции — это позволит нам увидеть, как выполняется работа. Старый добрый отладочный PRINT, нет ничего тебя лучше.
Следующая функция — возвращение финального значения:
«Финт ушами» с функцией trim нужен исключительно для аккуратности вывода: таким образом мы избавляемся от незначащих нулей, которые иначе будут загромождать экран и мешать восприятию. Примерно вот так:
SELECT 1::numeric / 2::numeric;
?column?
————————
0.50000000000000000000
(1 row)
В реальной жизни эти фокусы, конечно, не нужны.
И, наконец, определяем собственно агрегатную функцию. Для этого используется специальная команда CREATE AGGREGATE:
Можно пробовать. Почти все примеры в этой статье будут использовать простую таблицу с пятью строками: раз, два, три, четыре, пять. Таблицу создаем на лету функцией generate_series, незаменимым помощником генерации тестовых данных:
SELECT average(g.x) FROM generate_series(1,5) AS g(x);
NOTICE: 0(0) + 1
NOTICE: 1(1) + 2
NOTICE: 3(2) + 3
NOTICE: 6(3) + 4
NOTICE: 10(4) + 5
NOTICE: = 15(5)
average
———
3
(1 row)
Результат верный, а вывод функций позволяет проследить ход выполнения:
SELECT average(g.x) FROM generate_series(1,0) AS g(x);
NOTICE: = 0(0)
average
———
Oracle
В Oracle вся расширяемость обеспечивается механизмом Data Cartridge. Говоря по-простому, нам потребуется создать объектный тип, реализующий необходимый для агрегации интерфейс. Контекст естественным образом представляется атрибутами этого объекта.
CREATE OR REPLACE TYPE AverageImpl AS OBJECT(
accum number,
qty number,
STATIC FUNCTION ODCIAggregateInitialize (actx IN OUT AverageImpl)
RETURN number,
MEMBER FUNCTION ODCIAggregateIterate (self IN OUT AverageImpl, val IN number
RETURN number,
MEMBER FUNCTION ODCIAggregateMerge (self IN OUT AverageImpl, ctx2 IN AverageImpl)
RETURN number,
MEMBER FUNCTION ODCIAggregateTerminate (self IN OUT AverageImpl, returnValue OUT number, flags IN number)
RETURN number
);
/
Начальное значение контекста определяется здесь не константой, а отдельной (статической, то есть не привязанной к конкретному экземпляру объекта) функцией ODCIAggregateInitialize.
Функция, вызываемая для каждой строки — это ODCIAggregateIterate.
Результат возвращает функция ODCIAggregateTerminate, и ей, заметьте, передаются некие флаги, с которыми мы разберемся чуть позже.
Интерфейс включает еще одну обязательную функцию: ODCIAggregateMerge. Мы ее определим — куда ж деваться, — но разговор о ней пока отложим.
Теперь создадим тело объекта с реализацией перечисленных методов.
CREATE OR REPLACE TYPE BODY AverageImpl IS
STATIC FUNCTION ODCIAggregateInitialize (actx IN OUT AverageImpl)
RETURN number IS
BEGIN
actx := AverageImpl(0,0);
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateIterate (self IN OUT AverageImpl, val IN number)
RETURN number IS
BEGIN
dbms_output.put_line(self.accum||'(‘||self.qty||’) + ‘||val);
self.accum := self.accum + val;
self.qty := self.qty + 1;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateMerge (self IN OUT AverageImpl, ctx2 IN AverageImpl)
RETURN number IS
BEGIN
dbms_output.put_line(self.accum||'(‘||self.qty||’) & ‘||ctx2.accum||'(‘||ctx2.qty||’)’);
self.accum := self.accum + ctx2.accum;
self.qty := self.qty + ctx2.qty;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateTerminate (self IN OUT AverageImpl, returnValue OUT number, flags IN number)
RETURN number IS
BEGIN
dbms_output.put_line(‘= ‘||self.accum||'(‘||self.qty||’) flags:’||flags);
returnValue := CASE WHEN self.qty > 0 THEN self.accum / self.qty END;
RETURN ODCIConst.Success;
END;
END;
/
Реализация, по большей части, повторяет все то, что мы делали для PostgreSQL, но в немного другом синтаксисе.
Trim-пляски вокруг возвращаемого значения не нужны: Oracle самостоятельно отрезает незначащие нули при выводе значения.
Обратите внимание, что все функции возвращают признак успешности выполнения (значение ODCIConst.Success), а смысловые значения передаются через параметры OUT и IN OUT (которые в PL/SQL никак не связаны с собственно возвращаемым значением, как в PL/pgSQL). В частности, любая функция, в том числе и ODCIAggregateTerminate, может изменять атрибуты своего объекта, ссылка на который передается ей в первом параметре (self).
Определение агрегатной функции выглядит следующим образом:
CREATE OR REPLACE FUNCTION average(val number) RETURN number
AGGREGATE USING AverageImpl;
/
Проверяем. Для генерации значений используем идиоматическую конструкцию с рекурсивным запросом CONNECT BY level:
SELECT average(level) FROM dual CONNECT BY level
Можно обратить внимание на то, что вывод сообщений в PostgreSQL появляется до результата, а в Oracle — после. Это из-за того, что RAISE NOTICE работает асинхронно, а пакет dbms_output буферизует вывод.
Как мы видим, в функцию ODCIAggregateTerminate был передан нулевой флаг. Это означает, что контекст больше не требуется и его — при желании — можно забыть.
И проверка на пустом множестве:
SELECT average(rownum) FROM dual WHERE 1 = 0;
AVERAGE(ROWNUM)
—————
Оконные функции: OVER()
Хорошая новость: написанная нами агрегатная функция может без всяких изменений работать и как оконная (аналитическая).
Оконная функция отличается от агрегатной тем, что не сворачивает выборку в одну (агрегированную) строку, а вычисляется как бы отдельно для каждой строки. Синтаксически вызов оконной функции отличается наличием конструкции OVER с указанием рамки, которая определяет множество строк для обработки. В простейшем случае она так и записывается: OVER(), и это означает, что функция должна обработать все строки. Результат получается такой, как будто мы посчитали обычную агрегатную функцию и записали результат (один и тот же) напротив каждой строки выборки.
Иными словами, рамка статична и охватывает все строки:
PostgreSQL
SELECT g.x, average(g.x) OVER ()
FROM generate_series(1,5) as g(x);
NOTICE: 0(0) + 1
NOTICE: 1(1) + 2
NOTICE: 3(2) + 3
NOTICE: 6(3) + 4
NOTICE: 10(4) + 5
NOTICE: = 15(5)
x | average
—+———
1 | 3
2 | 3
3 | 3
4 | 3
5 | 3
(5 rows)
По выводу NOTICE видно, что все происходит точно так же, как и ранее при вычислении обычной агрегатной функции. Получив результат от функции average_final, PostgreSQL проставляет его в каждой строке.
Oracle
SELECT average(level) OVER() average
FROM dual CONNECT BY level
Неожиданно. Вместо того, чтобы вычислить результат один раз, Oracle вызывает функцию ODCIAggregateTerminate N+1 раз: сначала для каждой строки с флагом 1 (что означает, что контекст еще пригодится) и затем еще один раз в конце. Значение, полученное при последнем вызове, просто игнорируется.
Вывод такой: если в функции ODCIAggregateTerminate используется вычислительно сложная логика, надо подумать о том, чтобы не делать одну и ту же работу несколько раз.
Оконные функции: OVER(PARTITION BY)
Предложение PARTITION BY в определении рамки похоже на обычную агрегатную конструкцию GROUP BY. Оконная функция с указанием PARTITION BY вычисляется отдельно для каждой группы строк, и результат приписывается к каждой строке выборки.
В таком варианте рамка тоже статична, но для каждой группы она разная. Например, если определены две группы строк (с первой по вторую и с третьей по пятую), то рамку можно представить себе так:
PostgreSQL
SELECT g.x/3 part,
g.x,
average(g.x) OVER (PARTITION BY g.x/3)
FROM generate_series(1,5) as g(x);
NOTICE: 0(0) + 1
NOTICE: 1(1) + 2
NOTICE: = 3(2)
NOTICE: 0(0) + 3
NOTICE: 3(1) + 4
NOTICE: 7(2) + 5
NOTICE: = 12(3)
part | x | average
——+—+———
0 | 1 | 1.5
0 | 2 | 1.5
1 | 3 | 4
1 | 4 | 4
1 | 5 | 4
(5 rows)
Вычисление снова происходит последовательно, но теперь при переходе к другой группе строк состояние сбрасывается в начальное значение (initcond).
Oracle
SELECT trunc(level/3) part,
level,
average(level) OVER(PARTITION BY trunc(level/3)) average
FROM dual CONNECT BY level 0(0) + 2
2(1) + 1
= 3(2) flags:1
= 3(2) flags:1
0(0) + 4
4(1) + 5
9(2) + 3
= 12(3) flags:1
= 12(3) flags:1
= 12(3) flags:1
= 12(3) flags:0
Занятно, что Oracle решил переставить строки местами. Это может что-то сказать о деталях реализации, но в любом случае — имеет право.
Оконные функции: OVER(ORDER BY)
Если в определение рамки добавить предложение ORDER BY, указывающее порядок сортировки, функция начнет работать в режиме нарастания (для функции sum мы бы так и сказали — нарастающим итогом).
Для первой строки рамка будет состоять из одной этой строки; для второй — из первой и второй; для третьей — из первой, второй и третьей и так далее. Иными словами, в рамку будут входить строки с первой до текущей.
На самом деле, это можно ровно так и записать: OVER(ORDER BY… ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), но, поскольку это многословие подразумеваются по умолчанию, его обычно опускают.
Итак, рамка перестает быть статичной: ее голова движется вниз, а хвост остается на месте:
PostgreSQL
SELECT g.x, average(g.x) OVER (ORDER BY g.x)
FROM generate_series(1,5) as g(x);
NOTICE: 0(0) + 1
NOTICE: = 1(1)
NOTICE: 1(1) + 2
NOTICE: = 3(2)
NOTICE: 3(2) + 3
NOTICE: = 6(3)
NOTICE: 6(3) + 4
NOTICE: = 10(4)
NOTICE: 10(4) + 5
NOTICE: = 15(5)
x | average
—+———
1 | 1
2 | 1.5
3 | 2
4 | 2.5
5 | 3
(5 rows)
Как видим, строки все так же добавляются к контексту по одной, но теперь функция average_final вызывается после каждого добавления, выдавая промежуточный итог.
Oracle
SELECT level, average(level) OVER(ORDER BY level) average
FROM dual CONNECT BY level
На этот раз обе системы работают одинаково.
Оконные функции: OVER(PARTITION BY ORDER BY)
Предложения PARTITION BY и ORDER BY можно комбинировать. Тогда внутри каждой группы строк функция будет работать в режиме нарастания, а при переходе от группы к группе состояние будет сбрасываться в начальное.
PostgreSQL
Oracle
Оконные функции со скользящей рамкой
Во всех примерах, которые мы посмотрели, рамка либо была статической, либо двигалась только ее голова (при использовании предложения ORDER BY). Это давало нам возможность вычислять состояние последовательно, добавляя к контексту строку за строкой.
Но рамку оконной функции можно задать и таким образом, что ее хвост тоже будет смещаться. В нашем примере это будет соответствовать понятию скользящего среднего. Например, указание OVER(ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) говорит о том, что для каждой строки результата будут усредняться текущее и два предыдущих значений.
Сможет ли вычисляться оконная функция в таком случае? Оказывается, сможет, правда неэффективно. Но, написав еще немного кода, можно улучшить ситуацию.
PostgreSQL
SELECT g.x,
average(g.x) OVER (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
FROM generate_series(1,5) as g(x);
NOTICE: 0(0) + 1
NOTICE: = 1(1)
NOTICE: 1(1) + 2
NOTICE: = 3(2)
NOTICE: 3(2) + 3
NOTICE: = 6(3)
NOTICE: 0(0) + 2
NOTICE: 2(1) + 3
NOTICE: 5(2) + 4
NOTICE: = 9(3)
NOTICE: 0(0) + 3
NOTICE: 3(1) + 4
NOTICE: 7(2) + 5
NOTICE: = 12(3)
x | average
—+———
1 | 1
2 | 1.5
3 | 2
4 | 3
5 | 4
(5 rows)
Вплоть до третьей строки все идет хорошо, потому что хвост фактически не двигается: мы просто добавляем к уже имеющемуся контексту очередное значение. Но, поскольку мы не умеем убирать значение из контекста, для четвертой и пятой строк все приходится пересчитывать полностью, каждый раз возвращаясь к начальному состоянию.
Итак, было бы здорово иметь не только функцию добавления очередного значения, но и функцию удаления значения из состояния. И действительно, такую функцию можно создать:
Чтобы оконная функция смогла ей воспользоваться, нужно пересоздать агрегат следующим образом:
Oracle
Тут ситуация аналогична. Созданный вариант аналитической функции работает, но неэффективно:
SELECT level,
average(level) OVER(ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) average
FROM dual CONNECT BY level 0(0) + 2
2(1) + 3
5(2) + 4
= 9(3) flags:1
0(0) + 3
3(1) + 4
7(2) + 5
= 12(3) flags:1
= 12(3) flags:0
Функция удаления значения из контекста определяется следующим образом:
Пересоздавать саму функцию не нужно. Проверим:
Параллельность
И PostgreSQL, и Oracle (Enterprise Edition) умеют вычислять агрегатные функции в параллельном режиме. При этом каждый из параллельных процессов выполняет свою часть работы, формируя промежуточное состояние. Затем основной процесс-координатор получает эти несколько состояний и должен объединить их в одно итоговое.
Для этого нужна еще одна функция объединения, которую мы сейчас и напишем. В нашем случае она просто складывает и суммы, и количество значений.
PostgreSQL
Функция выглядит следующим образом:
Поскольку мы убираем вывод, то отпадает и необходимость использовать процедурный язык — напишем функцию на чистом SQL:
CREATE TABLE t(n) AS SELECT generate_series(1,1000)::numeric;
С настройками по умолчанию PostgreSQL не построит параллельный план для такой таблицы — слишком она мала, — но его несложно уговорить:
SET parallel_setup_cost=0;
SET min_parallel_table_scan_size=0;
EXPLAIN(costs off) SELECT average(n) FROM t;
QUERY PLAN
——————————————
Finalize Aggregate
-> Gather
Workers Planned: 2
-> Partial Aggregate
-> Parallel Seq Scan on t
В плане запроса видим:
SELECT average(n) FROM t;
NOTICE: 0(0) & 281257(678)
NOTICE: 281257(678) & 127803(226)
NOTICE: 409060(904) & 91440(96)
NOTICE: = 500500(1000)
average
———
500.5
(1 row)
Почему функция average_combine вызывается три раза, а не два? Дело в том, что в PostgreSQL координирующий процесс тоже выполняет часть работы. Поэтому, хотя было запущено два рабочих процесса, реально работа выполнялась в трех. Один из них успел обработать 678 строк, другой 226 и третий — 96 (хотя эти цифры ничего не значат и при другом запуске могут отличаться).
Oracle
Если помните, функцию ODCIAggregateMerge мы уже написали в самом начале, поскольку в Oracle она является обязательной. Документация настаивает, что эта функция необходима не только для параллельной работы, но и для последовательной — хотя мне трудно понять, зачем (и на практике не приходилось сталкиваться с ее выполнением при последовательной обработке).
Все, что остается сделать — объявить функцию безопасной для параллельной работы:
CREATE OR REPLACE FUNCTION average(val number) RETURN number
PARALLEL_ENABLE
AGGREGATE USING AverageImpl;
/
Создаем таблицу:
CREATE TABLE t(n) AS SELECT to_number(level) FROM dual CONNECT BY level
Уговорить Oracle еще проще, чем PostgreSQL — достаточно написать хинт. Вот какой получается план (вывод сильно урезан для простоты):
EXPLAIN PLAN FOR SELECT /*+ PARALLEL(2) */ average(n) FROM t;
SELECT * FROM TABLE(dbms_xplan.display);
———————————
| Id | Operation |
———————————
| 0 | SELECT STATEMENT |
| 1 | SORT AGGREGATE |
| 2 | PX COORDINATOR |
| 3 | PX SEND QC (RANDOM) |
| 4 | SORT AGGREGATE |
| 5 | PX BLOCK ITERATOR |
| 6 | TABLE ACCESS FULL |
———————————
План также содержит:
Документация
Самое время привести ссылки на документацию, в том числе и на агрегатные и оконные функции, уже включенным в СУБД. Там можно найти много интересного.
Пример про округление копеек
И обещанный пример из жизни. Эту функцию я придумал, когда приходилось писать отчеты для бухгалтерии, работающей по РСБУ (правилам российского бухучета).
Самая простая задача, в которой возникает необходимость округления — распределение общих расходов (скажем, 100 рублей) на отделы (скажем, 3 штуки) по какому-то принципу (скажем, поровну):
WITH depts(name) AS (
VALUES (‘A’), (‘B’), (‘C’)
), report(dept,amount) AS (
SELECT name, 100.00 / count(*) OVER() FROM depts
)
SELECT dept, round(amount,2) FROM report;
dept | round
——+——-
A | 33.33
B | 33.33
C | 33.33
(3 rows)
Этот запрос показывает проблему: суммы надо округлять, но при этом теряется копейка. А РСБУ этого не прощает.
Задачу можно решать по-разному, но на мой вкус наиболее элегантный способ — оконная функция, которая работает в нарастающем режиме и берет всю борьбу с копейками на себя:
WITH depts(name) AS (
VALUES (‘A’), (‘B’), (‘C’)
), report(dept,amount) AS (
SELECT name, 100.00 / count(*) OVER() FROM depts
)
SELECT dept, round2(amount) OVER (ORDER BY dept) FROM report;
dept | round2
——+———
A | 33.33
B | 33.34
C | 33.33
(3 rows)
Состояние такой функции включает ошибку округления (r_error) и текущее округленное значение (amount). Функция обработки очередного значения увеличивает ошибку округления, и, если она уже превышает полкопейки, добавляет к округленной сумме копеечку:
Полный код функции приводить не буду: используя уже приведенные примеры написать ее не представляет сложности.
Если вам встречались интересные примеры использования собственных агрегатных или оконных функций — поделитесь ими в комментариях.