Доклад на тему Курсоры PostgreSQL. Курсоры доклад Аксенов. Курсоры в sql и plpgsql
 Скачать 38.37 Kb.
|
|
Федеральное государственное образовательное бюджетное учреждение Высшего профессионального образования Российский Государственный Университет имени А. Н. Косыгина Кафедра «Прикладная математика и информатика» Доклад на тему «Курсоры в SQL и PL/pgSQL» Докладчик: Студент группы МПМ-18, Аксенов А.О. Москва, 2022г. 9 Курсоры клиентское приложение PostgreSQL драйвер разбор трансформация привязка ← значения параметров планирование выполнение получение результата подготовка результат результат привязка PostgreSQL Не всегда клиенту бывает удобно получить все результаты сразу. Данных может оказаться много, но не все они могут быть нужны. Для этого расширенный режим предусматривает курсоры. Протокол позволяет открыть курсор для какого-либо оператора, а затем получать результирующие данные построчно по мере необходимости. Курсор можно рассматривать как окно, в которое видна только часть строк из результирующего множества. При получении строки данных окно сдвигается. Иными словами, курсоры позволяют работать с реляционными данными (множествами) итеративно, строка за строкой. Открытый курсор представлен на сервере так называемым порталом. Это слово встречается в документации; в первом приближении можно считать «курсор» и «портал» синонимами. Запрос, используемый в курсоре, неявно подготавливается (то есть сохраняется его дерево разбора и, возможно, план выполнения). https://postgrespro.ru/docs/postgresql/12/sql-declare https://postgrespro.ru/docs/postgresql/12/sql-fetch 9 Курсоры клиентское приложение PostgreSQL драйвер разбор трансформация привязка ← значения параметров планирование выполнение получение результата подготовка результат результат привязка PostgreSQL Не всегда клиенту бывает удобно получить все результаты сразу. Данных может оказаться много, но не все они могут быть нужны. Для этого расширенный режим предусматривает курсоры. Протокол позволяет открыть курсор для какого-либо оператора, а затем получать результирующие данные построчно по мере необходимости. Курсор можно рассматривать как окно, в которое видна только часть строк из результирующего множества. При получении строки данных окно сдвигается. Иными словами, курсоры позволяют работать с реляционными данными (множествами) итеративно, строка за строкой. Открытый курсор представлен на сервере так называемым порталом. Это слово встречается в документации; в первом приближении можно считать «курсор» и «портал» синонимами. Запрос, используемый в курсоре, неявно подготавливается (то есть сохраняется его дерево разбора и, возможно, план выполнения). https://postgrespro.ru/docs/postgresql/12/sql-declare https://postgrespro.ru/docs/postgresql/12/sql-fetch 9 Курсоры клиентское приложение PostgreSQL драйвер разбор трансформация привязка ← значения параметров планирование выполнение получение результата подготовка результат результат привязка PostgreSQL Не всегда клиенту бывает удобно получить все результаты сразу. Данных может оказаться много, но не все они могут быть нужны. Для этого расширенный режим предусматривает курсоры. Протокол позволяет открыть курсор для какого-либо оператора, а затем получать результирующие данные построчно по мере необходимости. Курсор можно рассматривать как окно, в которое видна только часть строк из результирующего множества. При получении строки данных окно сдвигается. Иными словами, курсоры позволяют работать с реляционными данными (множествами) итеративно, строка за строкой. Открытый курсор представлен на сервере так называемым порталом. Это слово встречается в документации; в первом приближении можно считать «курсор» и «портал» синонимами. Запрос, используемый в курсоре, неявно подготавливается (то есть сохраняется его дерево разбора и, возможно, план выполнения). https://postgrespro.ru/docs/postgresql/12/sql-declare https://postgrespro.ru/docs/postgresql/12/sql-fetch 9 Курсоры клиентское приложение PostgreSQL драйвер разбор трансформация привязка ← значения параметров планирование выполнение получение результата подготовка результат результат привязка PostgreSQL Не всегда клиенту бывает удобно получить все результаты сразу. Данных может оказаться много, но не все они могут быть нужны. Для этого расширенный режим предусматривает курсоры. Протокол позволяет открыть курсор для какого-либо оператора, а затем получать результирующие данные построчно по мере необходимости. Курсор можно рассматривать как окно, в которое видна только часть строк из результирующего множества. При получении строки данных окно сдвигается. Иными словами, курсоры позволяют работать с реляционными данными (множествами) итеративно, строка за строкой. Открытый курсор представлен на сервере так называемым порталом. Это слово встречается в документации; в первом приближении можно считать «курсор» и «портал» синонимами. Запрос, используемый в курсоре, неявно подготавливается (то есть сохраняется его дерево разбора и, возможно, план выполнения). https://postgrespro.ru/docs/postgresql/12/sql-declare https://postgrespro.ru/docs/postgresql/12/sql-fetch 9 Курсоры клиентское приложение PostgreSQL драйвер разбор трансформация привязка ← значения параметров планирование выполнение получение результата подготовка результат результат привязка PostgreSQL Не всегда клиенту бывает удобно получить все результаты сразу. Данных может оказаться много, но не все они могут быть нужны. Для этого расширенный режим предусматривает курсоры. Протокол позволяет открыть курсор для какого-либо оператора, а затем получать результирующие данные построчно по мере необходимости. Курсор можно рассматривать как окно, в которое видна только часть строк из результирующего множества. При получении строки данных окно сдвигается. Иными словами, курсоры позволяют работать с реляционными данными (множествами) итеративно, строка за строкой. Открытый курсор представлен на сервере так называемым порталом. Это слово встречается в документации; в первом приближении можно считать «курсор» и «портал» синонимами. Запрос, используемый в курсоре, неявно подготавливается (то есть сохраняется его дерево разбора и, возможно, план выполнения). https://postgrespro.ru/docs/postgresql/12/sql-declare https://postgrespro.ru/docs/postgresql/12/sql-fetch 9 Курсоры клиентское приложение PostgreSQL драйвер разбор трансформация привязка ← значения параметров планирование выполнение получение результата подготовка результат результат привязка PostgreSQL Не всегда клиенту бывает удобно получить все результаты сразу. Данных может оказаться много, но не все они могут быть нужны. Для этого расширенный режим предусматривает курсоры. Протокол позволяет открыть курсор для какого-либо оператора, а затем получать результирующие данные построчно по мере необходимости. Курсор можно рассматривать как окно, в которое видна только часть строк из результирующего множества. При получении строки данных окно сдвигается. Иными словами, курсоры позволяют работать с реляционными данными (множествами) итеративно, строка за строкой. Открытый курсор представлен на сервере так называемым порталом. Это слово встречается в документации; в первом приближении можно считать «курсор» и «портал» синонимами. Запрос, используемый в курсоре, неявно подготавливается (то есть сохраняется его дерево разбора и, возможно, план выполнения). https://postgrespro.ru/docs/postgresql/12/sql-declare https://postgrespro.ru/docs/postgresql/12/sql-fetch 9 Курсоры клиентское приложение PostgreSQL драйвер разбор трансформация привязка ← значения параметров планирование выполнение получение результата подготовка результат результат привязка PostgreSQL Не всегда клиенту бывает удобно получить все результаты сразу. Данных может оказаться много, но не все они могут быть нужны. Для этого расширенный режим предусматривает курсоры. Протокол позволяет открыть курсор для какого-либо оператора, а затем получать результирующие данные построчно по мере необходимости. Курсор можно рассматривать как окно, в которое видна только часть строк из результирующего множества. При получении строки данных окно сдвигается. Иными словами, курсоры позволяют работать с реляционными данными (множествами) итеративно, строка за строкой. Открытый курсор представлен на сервере так называемым порталом. Это слово встречается в документации; в первом приближении можно считать «курсор» и «портал» синонимами. Запрос, используемый в курсоре, неявно подготавливается (то есть сохраняется его дерево разбора и, возможно, план выполнения). https://postgrespro.ru/docs/postgresql/12/sql-declare https://postgrespro.ru/docs/postgresql/12/sql-fetch Не всегда клиенту бывает удобно получить все результаты сразу. Данных может оказаться много, но не все они могут быть нужны. Для этого расширенный режим предусматривает курсоры. Протокол позволяет открыть курсор для какого-либо оператора, а затем получать результирующие данные построчно по мере необходимости. Курсор можно рассматривать как окно, в которое видна только часть строк из результирующего множества. При получении строки данных окно сдвигается. Иными словами, курсоры позволяют работать с реляционными данными (множествами) итеративно, строка за строкой. Открытый курсор представлен на сервере так называемым порталом. Это слово встречается в документации; в первом приближении можно считать «курсор» и «портал» синонимами. Запрос, используемый в курсоре, неявно подготавливается (то есть сохраняется его дерево разбора и, возможно, план выполнения). Не всегда клиенту бывает удобно получить все результаты сразу. Данных может оказаться много, но не все они могут быть нужны. Для этого расширенный режим предусматривает курсоры. Протокол позволяет открыть курсор для какого-либо оператора, а затем получать результирующие данные построчно по мере необходимости. Курсор можно рассматривать как окно, в которое видна только часть строк из результирующего множества. При получении строки данных окно сдвигается. Иными словами, курсоры позволяют работать с реляционными данными (множествами) итеративно, строка за строкой. Открытый курсор представлен на сервере так называемым порталом. Это слово встречается в документации; в первом приближении можно считать «курсор» и «портал» синонимами. Запрос, используемый в курсоре, неявно подготавливается (то есть сохраняется его дерево разбора и, возможно, план выполнения). Курсоры Не всегда клиенту бывает удобно получить все результаты сразу. Данных может оказаться много, но не все они могут быть нужны. Для этого расширенный режим предусматривает курсоры. Протокол позволяет открыть курсор для какого-либо оператора, а затем получать результирующие данные построчно по мере необходимости. Курсор можно рассматривать как окно, в которое видна только часть строк из результирующего множества. При получении строки данных окно сдвигается. Иными словами, курсоры позволяют работать с реляционными данными (множествами) итеративно, строка за строкой. Открытый курсор представлен на сервере так называемым порталом. Это слово встречается в документации; в первом приближении можно считать «курсор» и «портал» синонимами. Запрос, используемый в курсоре, неявно подготавливается. Обычная команда SELECT получает сразу все строки: => SELECT * FROM t ORDER BY id; id | s ----+---— 1 | foo 2 | bar 3 | baz 4 | xyz (4 rows) Курсор позволяет получать данные построчно. => BEGIN; BEGIN => DECLARE c CURSOR FOR SELECT * FROM t ORDER BY id; DECLARE CURSOR => FETCH c; id | s ----+---— 1 | foo (1 row) Размер выборки можно указывать: => FETCH 2 c; id | s ----+---— 2 | bar 3 | baz (2 rows) Размер выборки играет большое значение, когда строк очень много: обрабатывать большой объем данных построчно очень неэффективно. => FETCH 2 c; id | s ----+---— 4 | xyz (1 row) => FETCH 2 c; id | s ----+-— (0 rows) Если в процессе чтения мы дойдем до конца таблицы FETCH просто перестанет возвращать строки. В обычных языках программирования всегда есть возможность проверить это условие. => CLOSE c; CLOSE CURSOR Однако курсоры закрываются автоматически по завершению транзакции, так что можно не закрывать их явно. (Исключение составляют курсоры, открытые с указанием WITH HOLD.) => COMMIT; COMMIT Причины использования. Почему вообще может возникнуть необходимость в курсорах? Декларативный SQL в первую очередь предназначен для работы с множествами строк — в этом его сила и преимущество. PL/pgSQL, как процедурный язык, вынужден работать со строками по одной за раз, используя явные циклы. Этого как раз можно добиться, используя курсоры. Например, полная выборка может занимать слишком много места, так что приходится обрабатывать результаты по частям. Или требуется выборка неизвестного заранее размера — то есть в процессе выборки нужно вовремя остановиться. Или есть необходимость предоставить управление выборкой клиенту. (Однако еще раз отметим, что, хотя необходимость такой построчной обработки может возникать, во многих случаях ее можно заменить чистым SQL, и код в итоге окажется проще и будет быстрее работать.) Почему вообще может возникнуть необходимость в курсорах? Декларативный SQL в первую очередь предназначен для работы с множествами строк — в этом его сила и преимущество. PL/pgSQL, как процедурный язык, вынужден работать со строками по одной за раз, используя явные циклы. Этого как раз можно добиться, используя курсоры. Например, полная выборка может занимать слишком много места, так что приходится обрабатывать результаты по частям. Или требуется выборка неизвестного заранее размера — то есть в процессе выборки нужно вовремя остановиться. Или есть необходимость предоставить управление выборкой клиенту. (Однако еще раз отметим, что, хотя необходимость такой построчной обработки может возникать, во многих случаях ее можно заменить чистым SQL, и код в итоге окажется проще и будет быстрее работать.) Декларативный SQL в первую очередь предназначен для работы с множествами строк — в этом его сила и преимущество. PL/pgSQL, как процедурный язык, вынужден работать со строками по одной за раз, используя явные циклы. Этого как раз можно добиться, используя курсоры. Например, полная выборка может занимать слишком много места, так что приходится обрабатывать результаты по частям. Или требуется выборка неизвестного заранее размера — то есть в процессе выборки нужно вовремя остановиться. Или есть необходимость предоставить управление выборкой клиенту. Объявление и открытие. В SQL курсор объявлялся и открывался одновременно командой DECLARE. В PL/pgSQL это два отдельных шага. Кроме того, для доступа к курсорам используются курсорные переменные, имеющие тип refcursor и, фактически, содержащие имя курсора (причем если не указывать это имя явно, PL/pgSQL сам позаботится о его уникальности). Курсорную переменную можно объявить, не связывая ее с конкретным запросом. Тогда при открытии курсора нужно будет указать запрос. Другой вариант — уже при объявлении переменной указать запрос, возможно с параметрами. Тогда при открытии курсора указываются только фактические параметры. Оба способа равноценны; какой использовать — дело вкуса. И в том, и в другом варианте запрос может иметь неявные параметры — переменные PL/pgSQL. Запрос, открытый с помощью курсора, автоматически подготавливается. Объявление и открытие Создадим таблицу: => CREATE TABLE t(id integer, s text); CREATE TABLE => INSERT INTO t VALUES (1, 'Раз'), (2, 'Два'), (3, 'Три'); INSERT 0 3 Несвязанная переменная: => DO $$ DECLARE — объявление переменной cur refcursor; BEGIN — связывание с запросом и открытие курсора OPEN cur FOR SELECT * FROM t; END; $$; DO Связанная переменная: запрос указывается уже при объявлении. При этом переменная cur имеет тот же тип refcursor. => DO $$ DECLARE — объявление и связывание переменной cur CURSOR FOR SELECT * FROM t; BEGIN — открытие курсора OPEN cur; END; $$; DO При использовании связанной переменной курсор можно объявить с параметрами. При этом устраняется неоднозначность имён что видно в следующих примерах. => DO $$ DECLARE — объявление и связывание переменной cur CURSOR(id integer) FOR SELECT * FROM t WHERE t.id = cur.id; BEGIN — открытие курсора с указанием фактических параметров OPEN cur(1); END; $$; DO Переменные PL/pgSQL также являются (неявными) параметрами курсора. => DO $$ «local» DECLARE id integer := 3; — объявление и связывание переменной cur CURSOR FOR SELECT * FROM t WHERE t.id = local.id; BEGIN id := 1; — открытие курсора (значение id берется на этот момент) OPEN cur; END; $$; DO В качестве запроса можно использовать не только команду SELECT, но и любую другую, возвращающую результат (например, INSERT, UPDATE, DELETE с фразой RETURNING) Операции с курсором Выборка данных из курсора возможна в PL/pgSQL только построчно. Для этого служит команда FETCH INTO. Если запрос достаточно простой (одна таблица, без группировок и сортировок), то в процессе работы с курсором можно обращаться к текущей строке, например, в командах UPDATE или DELETE. Процедурная обработка данных подразумевает работу с циклами. Можно организовать перебор и обработку всех строк, возвращаемых курсором, с помощью управляющих команд. Но, поскольку часто нужен именно такой цикл, в PL/pgSQL есть специальная команда FOR. Этот вариант работает с курсором. Есть еще один вариант цикла FOR, в котором не требуется даже объявления курсора — в команде указывается сам запрос. Курсор можно закрыть явно командой CLOSE, но он в любом случае будет закрыт при окончании транзакции (в SQL курсор может оставаться открытым и после завершения транзакции, если указать фразу WITH HOLD). Чтение текущей строки из курсора выполняется командой FETCH. Если нужно только переместиться на следующую строку, можно воспользоваться другой командой — MOVE. => DO $$ DECLARE cur refcursor; rec record; — можно использовать и несколько скалярных переменных BEGIN OPEN cur FOR SELECT * FROM t ORDER BY id; MOVE cur; FETCH cur INTO rec; RAISE NOTICE '%', rec; CLOSE cur; END; $$; NOTICE: (2,Два) DO Обычно выборка происходит в цикле, который можно организовать так: => DO $$ DECLARE cur refcursor; rec record; BEGIN OPEN cur FOR SELECT * FROM t; LOOP FETCH cur INTO rec; EXIT WHEN NOT FOUND; — FOUND: выбрана ли очередная строка? RAISE NOTICE '%', rec; END LOOP; CLOSE cur; END; $$; NOTICE: (1,Раз) NOTICE: (2,Два) NOTICE: (3,Три) DO Чтобы не писать много команд, используется цикл FOR по курсору, который делает ровно то же самое: => DO $$ DECLARE cur CURSOR FOR SELECT * FROM t; — переменная цикла не объявляется BEGIN FOR rec IN cur LOOP — cur должна быть связана с запросом RAISE NOTICE '%', rec; END LOOP; END; $$; NOTICE: (1,Раз) NOTICE: (2,Два) NOTICE: (3,Три) DO Можно обойтись без явной работы с курсором, если цикл — это все, что требуется. Скобки вокруг запроса не обязательны, но удобны. => DO $$ DECLARE rec record; — надо объявить явно BEGIN FOR rec IN (SELECT * FROM t) LOOP RAISE NOTICE '%', rec; END LOOP; END; $$; NOTICE: (1,Раз) NOTICE: (2,Два) NOTICE: (3,Три) DO Как и для любого цикла, можно указать метку, что может оказаться полезным во вложенных циклах: => DO $$ DECLARE rec_outer record; rec_inner record; BEGIN «outer» FOR rec_outer IN (SELECT * FROM t ORDER BY id) LOOP «inner» FOR rec_inner IN (SELECT * FROM t ORDER BY id) LOOP EXIT outer WHEN rec_inner.id = 3; RAISE NOTICE '%, %', rec_outer, rec_inner; END LOOP INNER; END LOOP outer; END; $$; NOTICE: (1,Раз), (1,Раз) NOTICE: (1,Раз), (2,Два) DO После выполнения цикла переменная FOUND позволяет узнать, была ли обработана хотя бы одна строка: => DO $$ DECLARE rec record; BEGIN FOR rec IN (SELECT * FROM t WHERE false) LOOP RAISE NOTICE '%', rec; END LOOP; RAISE NOTICE 'Была ли как минимум одна итерация? %', FOUND; END; $$; NOTICE: Была ли как минимум одна итерация? f DO На текущую строку курсора, связанного с простым запросом (по одной таблице, без группировок и сортировок) можно сослаться с помощью предложения CURRENT OF. Типичный пример — обработка пакета заданий в цикле; для этого по таблице заданий открывается курсор и статус заданий изменяется по мере их выполнения. => DO $$ DECLARE cur refcursor; rec record; BEGIN OPEN cur FOR SELECT * FROM t FOR UPDATE; — строки блокируются по мере обработки LOOP FETCH cur INTO rec; EXIT WHEN NOT FOUND; UPDATE t SET s = s || ' (обработано)' WHERE CURRENT OF cur; END LOOP; CLOSE cur; END; $$; DO => SELECT * FROM t; id | s ----+----------------— 1 | Раз (обработано) 2 | Два (обработано) 3 | Три (обработано) (3 rows) CURRENT OF не работает с циклом FOR по запросу, поскольку этот цикл не использует курсор явным образом. Аналогичный результат можно получить, явно указав в команде UPDATE или DELETE уникальный ключ таблицы (WHERE id = rec.id). Но CURRENT OF работает быстрее и не требует наличия индекса. Следует заметить, что в большом числе случаев вместо использования циклов можно выполнить задачу одним оператором SQL — и это будет проще и еще быстрее. Часто циклы используют просто потому, что это более привычный, «процедурный» стиль программирования. Но для базы данных этот стиль не подходит. Например: => BEGIN; DO $$ DECLARE rec record; BEGIN FOR rec IN (SELECT * FROM t) LOOP RAISE NOTICE '%', rec; DELETE FROM t WHERE id = rec.id; END LOOP; END; $$; ROLLBACK; BEGIN NOTICE: (1,"Раз (обработано)") NOTICE: (2,"Два (обработано)") NOTICE: (3,"Три (обработано)") DO ROLLBACK Такой цикл заменяется одной простой командой: => BEGIN; DELETE FROM t RETURNING *; ROLLBACK; BEGIN id | s ----+----------------— 1 | Раз (обработано) 2 | Два (обработано) 3 | Три (обработано) (3 rows) DELETE 3 ROLLBACK Передача курсора клиенту Курсорная переменная PL/pgSQL (типа refcursor) содержит имя открытого SQL-курсора. Когда говорят о курсоре как о памяти, отведенной в обслуживающем процессе для хранения состояния, используют термин портал. Таким образом, функция на PL/pgSQL может открыть курсор и вернуть его имя клиенту. Дальше клиент сможет работать с курсором так, как будто он сам его и открыл, но доступ будет иметь только к тем данным, которые ему предоставлены. Это дает еще один способ организации взаимодействия приложения и базы данных. Откроем курсор и посмотрим значение курсорной переменной: => DO $$ DECLARE cur refcursor; BEGIN OPEN cur FOR SELECT * FROM t; RAISE NOTICE '%', cur; END; $$; NOTICE: DO Это имя курсора (портала), который был открыт на сервере. Имя было сгенерировано автоматически. При желании имя можно задать явно (но оно должно быть уникальным): => DO $$ DECLARE cur refcursor := 'cursor12345'; BEGIN OPEN cur FOR SELECT * FROM t; RAISE NOTICE '%', cur; END; $$; NOTICE: cursor12345 DO Пользуясь этим, можно написать функцию, которая откроет курсор и вернет его имя: => CREATE FUNCTION t_cur() RETURNS refcursor AS $$ DECLARE cur refcursor; BEGIN OPEN cur FOR SELECT * FROM t; RETURN cur; END; $$ VOLATILE LANGUAGE plpgsql; CREATE FUNCTION Клиент начинает транзакцию, вызывает функцию, узнает имя курсора и получает возможность читать из него данные. На psql это делается так: => BEGIN; BEGIN => SELECT t_cur() AS curname \gset => \echo :curname => FETCH :"curname"; — кавычки нужны из-за спецсимволов в имени id | s ----+----------------— 1 | Раз (обработано) (1 row) => COMMIT; COMMIT Чтобы клиенту было проще, можно позволить ему самому устанавливать имя курсора: => DROP FUNCTION t_cur(); DROP FUNCTION => CREATE FUNCTION t_cur(cur refcursor) RETURNS void AS $$ BEGIN OPEN cur FOR SELECT * FROM t; END; $$ VOLATILE LANGUAGE plpgsql; CREATE FUNCTION Клиентский код упрощается: => BEGIN; BEGIN => SELECT t_cur('cursor12345'); t_cur -----— (1 row) => FETCH cursor12345; id | s ----+----------------— 1 | Раз (обработано) (1 row) => COMMIT; COMMIT Функция может вернуть и несколько открытых курсоров, используя OUT-параметры. Таким образом можно за один вызов функции обеспечить клиента информацией из разных таблиц, если это необходимо. Альтернативный подход — сразу выбрать все необходимые данные на стороне сервера и сформировать из них документ JSON или XML. Итоги Курсор позволяет получать и обрабатывать данные построчно. Цикл FOR упрощает работу с курсорами. Обработка в цикле естественна для процедурных языков, но этим не стоит злоупотреблять. |