Использование "курсоров" для подкачки в PostgreSQL. Курсор postgresql
Как использовать курсор в функциях на PL/pgSQL? | Info-Comp.ru
И снова SQL! А если быть точнее PL/pgSQL, сегодня поговорим именно об этом расширение языка SQL, а конкретней о том, как использовать курсор при написании функции в СУБД PostgreSQL. И о том, для чего вообще нужны курсоры, и когда их лучше использовать.
Надеюсь, Вы не забыли все те примеры и уроки, которые мы рассматривали ранее, так как для прочтения этой статьи необходимы минимальные знания SQL, для того чтобы Вы вспомнили, вот эти материалы: Как написать функцию на PL/pgSQL, Написание табличной функции на PL/pgSQL - функция, которая возвращает таблицу в последней, кстати, уже затрагивалась тема курсоров, но не подробно, поэтому сегодня мы поговорим о курсорах уже подробней.
Что такое курсор в SQL?
Курсор в SQL – это временная выборка записей в процессе выполнения функции, над которой могут выполняться необходимые Вам действия, данная выборка является указателем на область памяти.
Курсоры могут быть очень полезны, например, если Вам в функции необходимо выполнять определенные действия с каким то набором строк, при этом до начала выполнения функции Вы даже не знаете, сколько строк будет при обработке той или иной записи. Если проще курсор - это просто запрос, который запускается в процессе выполнения функции.
Например, у Вас есть определенный запрос (набор записей), над каждой строкой которого необходимо выполнять какие то хитрые действия, которые нужно запомнить для операции над следующей строкой этого же запроса.
О том, что курсоры могут быть полезны, мы поговорили, но когда их лучше использовать? А использовать их лучше всего только тогда, когда у Вас нет другого выхода! Потому что курсор является очень ресурсоемким решением. В принципе если Вы будете выполнять операции над небольшим количеством записей, то это приемлемо, а если необходимо обработать большой объем данных, то Вы можете очень долго ждать, пока будет выполняться Ваша функция, а как Вы знаете быстрота в нашем деле чуть ли не главный фактор.
Пример использования курсора в функции на PL/pgSQL
О теории мы поговорили пора переходить к практике, и для начала общий синтаксис курсора в функции.
Теперь давайте приведем рабочий пример использования курсора, т.е. напишем функцию, в которой мы будем использовать курсор.
Примечание! Данный пример не из жизни, он смоделирован мной, поэтому у Вас такой ситуации может и не возникнуть.
Для начала давайте определим, с какими данными мы будем работать, допустим, у нас есть таблица, в которой хранятся записи о сотрудниках, которые постоянно находятся в командировках, или просто у них есть служебный транспорт, да это и не важно, важно то, что им выделяют деньги на расходы, а они отчитываются по этим расходам.
Пример таблицы с несколькими записями:
| id_per | id_user | rashod | summa | pr |
| 1 | 1 | 100 | 100 | 0 |
| 1 | 2 | 100 | 110 | 0 |
| 2 | 1 | 100 | 90 | 0 |
| 2 | 2 | 100 | 90 | 0 |
| 3 | 1 | 110 | 100 | 0 |
| 3 | 2 | 100 | 100 | 10 |
где,
- id_per – период по которому идет отчет;
- id_user – идентификатор сотрудника;
- rashod – сумма расходов за этот период;
- summa – сумма, которая выделялась на расходы;
- pr – возможная премия, на погашение задолженности в прошлом периоде.
Стоит следующая задача, нам необходимо определить тот период у сотрудников, в котором они расходовали средств больше, чем им выдали, и потом не возместили. При условии, что в следующем месяце им могут возместить этот расход (колонка pr), а могут и не возместить.
Т.е. в нашем примере у сотрудника с id_user = 1, этот период будет с id_per = 2, а у сотрудника с id_user = 2, этот период будет с id_per = 3. Другими словами, во втором периоде они оба перерасходовали выданные им средства, но сотруднику с id_user = 2 в следующем месяце их возместили, а с id_user = 1 нет, поэтому первый период возникновения перерасхода (причем не погашенного) у сотрудника с id_user = 1 будет именно период с id_per = 2.
В общем как то так:). Но для нас главное научиться использовать курсор в функциях, и наша функция будет выглядеть вот так.
Схема в базе PostgreSQL называется test и таблица тоже называется test, а функцию я назвал test.my_fun(numeric). Numeric – это как Вы помните тип входящего параметра.
CREATE OR REPLACE FUNCTION test.my_fun(numeric) RETURNS numeric AS $BODY$ DECLARE _id_user ALIAS FOR $1; --объявляем курсор crs_my CURSOR FOR select id_per, rashod, summa from test.test where id_user = _id_user order by id_per; --объявляем нужные нам переменные _id_per numeric; _rashod numeric; _summa numeric; _pr numeric; _var numeric; _rezult numeric; BEGIN _pr:=0; OPEN crs_my;--открываем курсор LOOP --начинаем цикл по курсору --извлекаем данные из строки и записываем их в переменные FETCH crs_my INTO _id_per, _rashod, _summa; --если такого периода и не возникнет, то мы выходим IF NOT FOUND THEN EXIT;END IF; --ищем сумму возмещения, если она была select into _pr pr from test.test where id_user=_id_user and id_per = _id_per+1; _var = _rashod - _summa; if _var > 0 then _var = _var - _pr; End if; _rezult=_id_per; --если _var даже после возмещения больше нуля, то выходим и возвращаем период EXIT when _var > 0; END LOOP;--заканчиваем цикл по курсору CLOSE crs_my; --закрываем курсор RETURN _rezult;--возвращаем результат END; $BODY$ LANGUAGE 'plpgsql' VOLATILEИспользовать функцию можно вот так:
SELECT test.my_fun(1)Результат, как Вы помните, будет 2.
Если хотите запустить по всем записям, то используйте вот такой запрос:
SELECT test.my_fun(id_user) FROM test.testВот такой простой пример! Надеюсь, курсоры Вам как-то помогут в ваших функциях, но снова напомню, злоупотреблять курсорами не следует, а использовать их нужно только по необходимости. Удачи!
Похожие статьи:
info-comp.ru
postgresql - Правильное использование курсоров для очень больших наборов результатов в Postgres
Краткая версия моего вопроса:
Если я держу ссылку курсора на астрономически огромный результат, заданный в моем клиентском коде, было бы смешно (т.е. полностью победить точку курсоров) выдать "FETCH ALL FROM cursorname" в качестве моей следующей команды? Или это будет медленно передавать данные мне, когда я их использую (по крайней мере, в принципе, полагая, что у меня есть хорошо написанный водитель, сидящий между мной и Postgres)?
Подробнее
Если я правильно понимаю вещи, то курсоры Postgres ДЕЙСТВИТЕЛЬНО для решения следующей проблемы [даже если их можно использовать (злоупотреблять?) для других вещей, например, возвращать несколько разных наборов результатов из одной функции]:
Примечание. Текущая реализация RETURN NEXT и RETURN QUERY сохраняет весь набор результатов перед возвратом из функции, поскольку обсуждалось выше. Это означает, что если функция PL/pgSQL создает очень большой набор результатов, производительность может быть низкой: данные будут записаны на диск, чтобы избежать исчерпания памяти, но сама функция не будет до тех пор, пока не будет сформирован весь результирующий набор.
(ref: https://www.postgresql.org/docs/9.6/static/plpgsql-control-structures.html)
Но (опять же, если я правильно понимаю), когда вы пишете функцию, которая возвращает курсор, тогда весь запрос НЕ буферизуется в память (и диск), прежде чем пользователь функции может начать потреблять что угодно, но вместо этого результаты могут потребляться по частям. (Существует больше накладных настроек и с помощью курсора, но это стоит того, чтобы избежать массивного распределения буфера для очень больших наборов результатов.)
(ref: https://www.postgresql.org/docs/9.6/static/plpgsql-cursors.html#AEN66551)
Я хотел бы понять, как это относится к SELECTS и FETCHES через провод к серверу Postgres.
Во всех случаях я говорю о потреблении результатов от клиентского кода, который связывается с Postgres в сокете за кулисами (на самом деле, используя библиотеку Npgsql в моем случае).
Q1: Что делать, если я пытаюсь выполнить "SELECT * FROM AstronomicallyLargeTable" в качестве моей единственной команды по проводке Postgres? Будет ли это выделять всю память для всего выбора, а затем начать отправлять данные мне? Или он (эффективно) генерирует свой собственный курсор и немного потоит данные назад (без огромного дополнительного выделения буфера на сервере)?
Q2: Что делать, если у меня уже есть ссылка курсора на астрономически большой результирующий набор (скажем, потому что я уже совершил одно путешествие в оба конца и вернул ссылку на курсор из какой-то функции), а затем я выполняю "FETCH ALL FROM cursorname" через провод к Postgres? Это глупо, потому что он будет выделять ВСЕ память для всех результатов на сервере Postgres, прежде чем отправлять что-нибудь мне обратно? Или "FETCH ALL FROM cursorname" фактически работает так, как мне бы хотелось, потоковым потокам данных медленно, когда я их потребляю, без какого-либо массового выделения буфера на сервере Postgres?
EDIT: дальнейшее уточнение
Я спрашиваю о случае, когда я знаю, что мой уровень доступа к данным передает данные с сервера мне по одной строке за раз (так что на нем не было большого количества буферов на стороне клиента, сколько бы длинных потоков данных не было) и где я также знаю, что мое собственное приложение потребляет данные по одной строке за раз, а затем отбрасывает его (так что на нем нет буферов на стороне клиента). Я определенно НЕ хочу извлекать все эти строки в память на стороне клиента, а затем делать с ними что-то. Я вижу, что это будет абсолютно глупо!
Итак, я думаю, что все проблемы (для только что описанного варианта использования) - это то, как долго PostgreSQL будет принимать потоки и сколько буфера памяти он выделил бы для FETCH ALL. IF (и это большой "IF"...) PostgreSQL не выделяет огромный буфер всех строк перед запуском, и если он передает строки обратно в Npgsql по одному за раз, начиная быстро, то я верю (но, пожалуйста, скажите мне, почему/если я ошибаюсь), что все еще есть явный вариант использования для FETCH ALL FROM cursorname!
qaru.site
Иллюстрированный самоучитель по PostgreSQL › Нетривиальные возможности › Транзакции и курсоры [страница - 186] | Самоучители по программированию
Транзакции и курсоры
Использование курсоров
Курсор SQL в PostgreSQL представляет собой доступный только для чтения указатель на итоговый набор выполненной команды SELECT. Курсоры часто используются в приложениях, хранящих информацию о состоянии подключения к серверу PostgreSQL. Создание курсора и работа со ссылкой на полученный итоговый набор позволяет приложению организовать более эффективную выборку разных записей итогового набора без повторного выполнения запроса с другими значениями LIMIT и OFFSET.
В прикладных интерфейсах (API) курсоры часто используются для объединения нескольких запросов с последующим их отслеживанием и управлением ими через ссылку на курсор на уровне приложения. Тем самым предотвращается необходимость хранения всех результатов в памяти приложения.
Курсоры часто обладают абстрактным представлением в прикладных интерфейсах (пример – класс PgCursor в libpq++), хотя приложение может напрямую создавать курсоры и работать с ними при помощи стандартных команд SQL. В этом подразделе описаны обобщенные принципы работы с курсорами в SQL, продемонстрированные на примере клиента psql. В PostgreSQL существуют четыре команды, предназначенные для работы с курсорами: DECLARE, FETCH, MOVE и CLOSE.
Команда DECLARE определяет и одновременно открывает курсор, после чего заполняет его информацией по результатам итогового набора выполненного запроса. Команда FETCH позволяет получить записи из открытого курсора. Команда MOVE перемещает "текущую" позицию курсора в итоговом наборе, а команда CLOSE закрывает курсор.
ПримечаниеЕсли вас интересует тема использования курсоров в конкретном интерфейсе API, обращайтесь к документации на API.
Объявление курсора
Команда SQL DECLARE создает курсор и выполняет его. Этот процесс также называется открытием курсора. Курсор может быть объявлен только в существующем транзакционном блоке, поэтому перед объявлением курсора должна быть выполнена команда BEGIN. Синтаксис команды DECLARE:
DECLARE курсор [ BINARY ] [ INSENSITIVE ] [ SCROLL ] CURSOR FOR запрос [ FOR { READ ONLY | UPDATE [ OF none [….]]}]- DECLARE курсор. Имя создаваемого курсора.
- [ BINARY ]. Ключевое слово BINARY означает, что выходные данные должны возвращаться в двоичном формате вместо стандартного ASCII-кода. Иногда переключение на двоичный формат повышает эффективность курсора, но это относится лишь к пользовательским приложениям, поскольку стандартные клиенты (такие, как psql) работают только с текстовым выводом.
- [ INSENSITIVE ] [ SCROLL ]. Ключевые слова INSENSITIVE и SCROLL существуют для совместимости со стандартом SQL, но они описывают поведение PostgreSQL по умолчанию, поэтому их присутствие не обязательно. Ключевое слово SQL INSENSITIVE обеспечивает независимость данных, возвращенных курсором, от других курсоров или подключении. Поскольку PostgreSQL требует, чтобы курсоры определялись в транзакционных блоках, это требование заведомо выполняется. Ключевое слово SQL SCROLL указывает, что курсор поддерживает одновременную выборку нескольких записей. Этот режим поддерживается в PostgreSQL по умолчанию, даже если ключевое слово SCROLL не указано.
- CURSOR FOR запрос. Запрос, после выполнения которого итоговый набор становится доступным через курсор.
- FOR { READ ONLY | UPDATE [ OF поле [….] ] }. В PostgreSQL 7.1.x поддерживаются курсоры, доступные только для чтения (READ ONLY), поэтому секция FOR оказывается лишней.
В листинге 7.42 мы создаем транзакцию командой BEGIN и открываем курсор с именем all_books, ассоциированный с командой SELECT * FROM books.
Листинг 7.42. Объявление курсора.
booktown=# BEGIN; BEGIN booktown=# DECLARE all_books CURSOR booktown-# FOR SELECT * FROM books; SELECTСообщение SELECT в конце листинга 7.42 говорит о том, что команда была выполнена успешно, а записи, полученные в результате запроса, стали доступными для курсора all_books.
samoychiteli.ru
postgresql - Использование "курсоров" для подкачки в PostgreSQL
Курсоры являются разумным выбором для подкачки в небольших приложениях интрасети, которые работают с большими наборами данных, но вы должны быть готовы отбросить их после таймаута. Пользователи любят побродить, пойти на обед, отправиться в отпуск на две недели и т.д. И оставить свои приложения. Если это веб-приложение, там даже вопрос о том, что такое "работа" и как определить, находится ли пользователь по-прежнему.
Они не подходят для крупномасштабных приложений с высоким количеством клиентов и клиентов, которые приходят и уходят почти в случайном порядке, например, в веб-приложениях или веб-API. Я бы не рекомендовал использовать курсоры в вашем приложении, если у вас нет достаточно небольшого количества клиентов и очень высоких ставок запросов... в этом случае отправка небольших партий строк будет очень неэффективной, и вы должны подумать о том, чтобы разрешить запросы диапазонов и т.д. /p >
Курсоры имеют несколько затрат. Если курсором не является WITH HOLD, вы должны сохранить транзакцию открытой. Открытая транзакция может помешать autovacuum правильно выполнять свою работу, вызывая раздувание таблиц и другие проблемы. Если курсор объявлен WITH HOLD, и транзакция не открыта, вы должны оплатить стоимость материализации и хранения потенциально большого набора результатов - по крайней мере, я думаю, что как работают курсоры. Альтернатива так же плоха, что и транзакция неявно открыта до тех пор, пока курсор не будет уничтожен и не будет очищаться от строк.
Кроме того, если вы используете курсоры, вы не можете передавать соединения обратно в пул соединений. Для каждого клиента вам потребуется одно соединение. Это означает, что больше ресурсов на бэкэнд используется только для поддержания состояния сеанса и устанавливает очень реальный верхний предел количества клиентов, с которыми вы можете справиться с помощью подхода на основе курсора.
Кроме того, сложность и накладные расходы на управление установкой с учетом состояния, основанной на курсорах, по сравнению с подходом с объединением соединений без состояния с ограничением и смещением. Вам нужно, чтобы ваше приложение заканчивало курсоры после таймаута или вы сталкивались с потенциально неограниченным использованием ресурсов на сервере, и вам нужно отслеживать, какие соединения имеют какие курсоры, для которых установлены наборы результатов, для которых пользователи...
В целом, несмотря на то, что он может быть весьма неэффективным, LIMIT и OFFSET могут быть лучшим решением. Часто бывает лучше искать первичный ключ, а не использовать OFFSET, хотя.
Кстати, вы смотрели документацию для курсоров в PL/pgSQL. Вы хотите нормальные курсоры уровня SQL для этого задания.
У курсоров требуется, чтобы соединение с базой данных оставалось открытым?
Да.
Выполняют ли курсоры внутри транзакции, блокируя ресурсы, пока они "закрыты"?
Да, если они не являются WITH HOLD, и в этом случае они потребляют другие ресурсы базы данных.
Есть ли еще какие-то "gotchas", о которых я не знаю?
Да, как объясняется выше.
qaru.site
