Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
PAGE 4
Глава 13. Контроль паралелізму (Цілісність даних)
Ця глава описує поведінку системи PostgreSQL бази даних, коли дві або більше сесій пробують звернутися до одних же даних в один же час. Цілями в цій ситуації є забезпечення ефекутивного доступу для всіх сесій підтримуючи строгу цілісність дани. Кожен розробник додатків баз даних повинен бути знайомим з розділами цієї глави.
13.1. Вступ
PostgreSQL забезпечує великий набір інструментів, щоб розробники управляли конкуруючим доступом до даних. Внутрішньо стійкість даних підтримується використанням мультиверсійному моделі (Мультиверсійний Контроль Паралелізму MVCC). Це означає, що, під час запиту до базу даних, кожна транзакція бачить моментальний знімок даних (версію бази даних) так як було деякий час назад, незважаючи на поточний стан основних даних. Це захищає транзакції від перегляду непостійних даних що може бути викликане паралельними транзакціями модифікації одного і того ж рядка. MVCC, уникаючи явних аретувальних методологій традиційних систем бази даних, мінімізує блокування суперечок для того, щоб дозволити розсудливе виконання в розрахованих на багато користувачів середовищах.
Основною перевагою користування MVCC моделлю паралелізму управляють є не блокування а те що в MVCC блоках придатних для запитів (читання) дані не конфліктують з блоками призначеними для запису даних
тож читання ніколи не блокує запис і запис ніколи не блокує читання.
Таблично- і рядково- рівневі можливості блоків є також доступні в PostgreSQL для додатків, які не можуть легко пристосуватися до поведінки MVCC. Проте, належне використання MVCC загалом забезпечить краще виконання ніж блокування. Крім того, додаткові консультативні блокування забезпечують механізм для блокування, що не зв'язуються з однинарною транзакцією.
13.2. Ізоляція Операцій
Стандарт SQL визначає чотири рівні операційної ізоляції в термінах трьох явищ, які не повинні появляти між паралельними конкуруючими операціями. Ці небажані явища є:
брудне читання
Транзакція читає дані, записані конкурентною не прив’язаною транзакцією.
неповторюване читання
Транзакція перечитує дані, які вона вже читала, і виявляє, що дані були модифіковані іншою транзакцією (що було здійснено впроміжку між читаннями першою транзакцією).
примарне читання
Транзакція повторно виконує запит, що повертає набір рядів, які задовольняють пошуковій умові і виявляє, що набір рядів, що задовольняють умові, змінилася через іншу нещодавно виконану транзакцію.
Чотири рівні операційної ізоляції і відповідна поведінка описуються в Таблиці 13-1.
Table 13-1. Рівні Ізоляції Транзакцій SQL
|
Ізоляційний рівень |
Брудний рівень |
Неповторюваний рівень |
Примарний рівень |
|
Читання не довірене |
Можливе |
Можливе |
Можливе |
|
Читання довірене |
Не можливе |
Можливе |
Можливе |
|
Повторюване читання |
Не можливе |
Не можливе |
Можливе |
|
Серіалізабельне |
Не можливе |
Не можливе |
Не можливе |
У PostgreSQL, ви можете запросити будь-який з чотирьох стандартних рівнів операційної ізоляції. Але внутрішньо
є тільки два чіткі рівні ізоляції, які відповідають рівням Читання Довірене, і Серіалізабельне(перетворюване в послідовну форму). Коли ви вибираєте Недовірене Читання, вдійсності ви отримуєте Читання Довірене, і коли
ви вибираєте Повторюване Читання, то в дійсності ви отримуєте Серіалізабельне, тож фактичний рівень ізоляції, може бути строгішим ніж вибраний. Це дозволяє стандарт SQL : чотири рівні ізоляції тільки визначають які явища не повинні траплятися, вони не визначають які явища повинні статися. Причиню того що PostgreSQL забезпечує тільки два рівні ізоляції є те що це єдиний чутливий шлях позначення стандартних рівнів ізоляції для архітектури мультиверсійного контролю паралелізму паралелізму. Поведінка доступних рівнів ізоляції деталізується в наступних підрозділах.
Щоб встановити рівень операційної ізоляції транзакції, користуються командною SET TRANSACTION.
13.2.1. Ізоляційний Рівень Довіреного Читання
Довірене Читання - рівень заданої по умовчанню ізоляції в PostgreSQL. Коли транзакція користується цим рівне ізоляції, запит SELECT (без FOR UPDATE/SHARE умови) бачить тільки ті довірені дані що були безпосередньо перед початком запиту; воно ніколи не бачить не довірених даних чи змін довірених запитами конкурентних транзакцій. Запит SELECT бачить моментальний знімок бази даних, як той на якому він може виконуватися. Проте, SELECT бачить ефекти попередніх оновлень, що виконувала його власна транзакція, хоча вони ще не здійснені. Також відмітьте, що дві послідовні SELECT команди можуть бачити різні дані, хоча вони в межах єдиної транзакції, якщо інші транзакції вчинять зміни впродовж виконання першого SELECT.
UPDATE, DELETE, SELECT FOR UPDATE, і SELECT FOR SHARE команди поводяться так само, як і SELECT в рамках пошуку цільових рядків: вони тільки знайдуть цільові рядки, які були довірені, до часу старту команди. Проте, такий цільовий ряд, мог вже бути модифікований (або видалений чи блокований) іншою конкуруючою транзакцією до того часу коли його знайдуть. В цьому випадку, претендуючий модуль оновлення дочекається першої модифікуючої транзакції, щоб виконатися або прокрутитися назад (якщо він все ще виконується). Якщо перший модуль оновлення прокручується назад, то його ефекти відміняються і другий модуль оновлення може відновити модифікування оригінального знайденого ряду. Якщо перший модуль оновлення виконується, то другий модуль оновлення нехтуватиме рядом, якщо перший модуль оновлення видалив його, або спробує застосувати свою операцію до модифікованої версії ряду. Пошукова умова команди (умова WHERE) перевиконується, щоб переконатися чи модифікована версія ряду все ще відповідає заданій умові пошуку. Якщо так, другий модифікатор виконує свою операцію на модифікованій версії ряду. У випадку SELECT FOR UPDATE і SELECT FOR SHARE це означає, що це - модифікована версія ряду, який блокований і повернений клієнтові.
Через правило вище, можливим є бачити несталий моментальний знімок для команди-модифікатора: вона може бачити ефекти конкуруючих модифікуючих команд на тих же рядах, які пробує модифікувати, але не бачить ефекти команд на інших рядах у базі даних. Ця поведінка робить режим Довіреного Читання, непідходящим для команд, які включають складні пошукові умови; проте, вона корисна для простих випадків. Наприклад, розглянемо модифікацію банківських рахунків транзакціями типу:
BEGIN;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534;
COMMIT;
Якщо дві такі транзакції паралельно намагаються змінити баланс рахунку 12345, ми явно хочемо щоб друга транзакція працювала з вже модифікованою мерсією рядку рахунку. Оскільк одна команда впливає тільки на визначений рядок, дозвіл їй бачити модифіковану версію рядку не створює ніяких проблем несталості.
Більш комплексне використання може видати небажані результати в режимі Довіреного Читання. Для прикладу припустимо що команда DELETE працює на даних що добавляються і видаляються водночас з свого міся розташування іншою командою, тобто примустимо website є дворядковою таблицею з website.hits які рівні 9 і 10:
BEGIN;
UPDATE website SET hits = hits + 1;
-- run from another session: DELETE FROM website WHERE hits = 10;
COMMIT;
DELETE не матиме ніякого ефекту хоча рядок website.hits = 10 є і до і після UPDATE. Це відбувається, тому що значення рядку попереднього оновлення 9 пропускається, і коли UPDATE завершується і DELETE отримує блокування, нове значення ряду складає більше не 10 але 11, який більше не відповідає
критерії.
Оскільки режим Довіреного Читання розпочинає кожну команду з нового моментального знімка, який включає усі транзакції що здійснено аж до цієї миті, подальші команди в тій же транзакції бачитимуть ефекти із вже здійсненої конкуруючої транзакції у будь-якому випадку. Головною ідеєю проблеми описаної вище є бачить чи ні одинична команда абсолютно сталий вид бази даних.
Часкова ізоляція транзакцій, що забезпечується режимом Довіреного Читання є адекватною для багатьох додатків і цей режим є швидким і простим у використанні; проте, цього не достатньо для усіх випадків. Додатки що роблять складні запити і оновлення, можуть вимагати більш строге несуперечливе представлення бази даних наж те яке забезпечує режим Довіреного Читання.
13.2.2. Серіалізабельний Рівень Ізоляції
Серіалізабельний рівень ізоляції забезпечує ізоляцію строгих транзакцій. Цей рівень емулює серійне виконання транакцій, нібито транзакції виконуються по порядку, послідовно(серійно) а не одночасно. Проте, додатки, що користуються цим рівнем, мають бути готові повторювати транзакції через помилки серіалізації.
Коли транзакція користується серіалізабельним рівнем, запит SELECT бачить тільки які опрацьовані до того як транзакція почалась; воно ніколи не бачить недоопрацьовані дані, або змінені під час виконання транзакції іншими паралельними транзакціями. (Проте, запит бачить ефекти попередніх оновлень що виконуються в межах його власної транзакції, хоча вони ще не здійснені.) Ось і різниця від Довіреного Читання в тому що запис серіалізабельної транзакції бачить моментальний знімок бази, що був до початку транзакції а не самого запиту. Тож, успішні SELECT команди в межах єдиної транзакції бачать ті ж дані, тобто, вони не бачать зміни зробленими іншим транзакції, що здійснені після того, як їх власна транзакція почалася. (Ця поведінка може бути ідеальна для додатків звітів.)
UPDATE, DELETE, SELECT FOR UPDATE, і SELECT FOR SHARE команди поводяться так само, як SELECT в рамках пошуку цільових рядків: вони тільки знайдуть цільові ряди, які були опрацьовані, до старту транзакції. Проте, такий цільовий ряд, міг вже бути модифікований (або видалений чи блокований) іншою конкуруючою транзакцією до часом, його знаходження. В цьому випадку, серіалізабельна транзакція дочекається першої модифікуючої транзакції, щоб виконатися або прокрутити назад (якщо вона все ще працює). Якщо перший модуль модернізації прокручує назад, то його ефекти відміняються і серіалізабельна транзакція може відновити модифікування спочатку знайденого ряду. Але якщо перший модуль модифікації здійснює (і фактично модифікував або видалив ряд,а не лише блокував його) то серіалізабельна транзакція зробить реверс з повідомленням
ERROR: could not serialize access due to concurrent update
оскільки серіалізабельна транзакція не може змінити або заблокувати рядки, змінені іншими транзакціями після того, як серіалізабельна транзакція почалася.
Коли додаток отримує це повідомлення про помилку, він повинен припинити поточну транзакцію і повторити цілу транзакцію із самого початку. Наступного разу, транзакція побачить заздалегідь-здійснену зміну як частина свого початкового представлення бази даних, тож небуде ніякого логічного конфлікту у використанні нової версії ряду як відправної точки для оновлення нової транзакції.
Відмітьте, що тільки, модифікуючи транзакції, можливо треба буте повторювати; доступні тільки для читання транзакції ніколи не матимуть конфліктів серіалізації.
Серіалізабельний режим забезпечує тверду гарантію, що кожна транзакція бачить цілком стале представлення бази даних. Проте, додатку доведеться бути готовим, повторювати транзакції, коли конкурентні оновлення роблять неможливим підтвердження ілюзії серійного виконання. Оскільки вартість перероблення складних транзакцій може бути істотною, серіалізабельним режимом рекомендовано користуватись тільки, коли модифікуючі транзакції містять логіку досить складну щр може тдати неправильний результат при використанні режиму Довіреного Читання. Найбільш загально, Серіалізабельний режим потрібний, коли транзакція виконує декілька послідовних команд, які повинні бачити ідентичні представлення бази даних.
13.2.2.1. Серіалізабельна Ізоляція проти Існонної Серіалізабельності
Інтуїтивним значенням (і математичним визначенням) слова "серіалізабельність" є те, що будь-які дві успішні здійснені конкуруючі транзакції виконалися строго послідовно, одна за одною— хоча хто з них буде першою не було передбачувано спочатку. Важливим для розуміння є те що заборона небажаних поведінок перерахованих в Таблиці 13-1, не достатня для гарантування істинної серіалізабельності, і фактично Серіалізабельний режим PostgreSQL не гарантує серіалізабельнасть в цьому сенсі. Як приклад, розглядають табличку mytab, що спочатку містить:
class | value
-------+-------
1 | 10
1 | 20
2 | 100
2 | 200
Примустимо, що серіалізабельна транзакція А обчислює:
SELECT SUM(value) FROM mytab WHERE class = 1;
а потім вставляє результат (30) як value в новому ряду з class = 2. Одночасно, серіалізабельна транзакція B обчислює:
SELECT SUM(value) FROM mytab WHERE class = 2;
і отримує результат 300, який вставляє в новий ряд з class = 1. Потім обидві транзакції завершуються. Жодна з перерахованих небажаних поведінок не сталися, але ми маємо результат що не міг би появитися якби транзакції слідували послідовно. Якщо А виконувалась перед B, B вичислив би суму 330, не 300, і так само інший порядок привело б до іншої суми, вичисленої А.
Щоб гарантувати істинну математичну серіалізабельність, необхідно для системи бази даних наказати предикатний замок, що означає, що транзакція не може вставити або змінити ряд, який відповідав би WHERE умові запиту іншої конкуруючої транзакції. Наприклад, як тільки транзакція А виконала запит SELECT ... WHERE class = 1, система предикатного замку заборонила б транзакції B вставку будь-якого нового ряду з класом 1 поки транзакція А не завершиться. Така клокуваляна система є складною у створенні і екстримально дорогою у виконанні, оскільки кожна сесія повинна знати деталі кожного запиту, що виконується кожною конкуруючою транзакцією. І це практично марно, оскільки на практиці більшість не роблять речей, які змогли привести до проблем. (Звичайно приклад вище швидше просто хитрий і навряд чи, представляє якийсь реальний додаток.) Через це PostgreSQL не забезпечує предикатне блокування.
У випадках, де можливість несеріалізабельного виконання створює ризики, проблеми можуть бути виправлені відповідним використанням явного блокування. Подальше обговорення його в наступні секції.
13.3. Явне Блокування
PostgreSQL забезпечує різні режими замків(блоків), щоб управляти конкуруючим доступом до даних в таблицях. Ці режими можуть бути використані для прикладно-контрольованого блокування в ситуаціях, де MVCC не дає бажаної поведінки. Також, більшість команд PostgreSQL автоматично включають відповідні режими блокування щоб гарантувати не можливість неправильної модифікації таблиць на які вони посилаються під час їх виконання. (Наприклад, ALTER TABLE не може безпечно виконуватися одночасно з іншими операціями на одній таблиці, тож вона включає виключне блокування на таблиці і запобігає цьому.)
Щоб дослідити список наявних блокувань в сервері бази даних, використовують системний огляд pg_locks. Для більш конкретної інформації про контроль стану підсистеми замкового менеджера, прогляньте Главу 27.
13.3.1. Замки Табличного рівня
Список нижче показує доступні замкові режими і контексти, в яких вони використовуються автоматично PostgreSQL. Ви можете також включити будь-яке з цих блокувань явно командою LOCK. Пам'ятайте що усі ці замкові режими – замки табличного рівня, навіть якщо ім'я містить слово "Ряд"; імена замкових режимів історичні. До деякої міри імена відбивають типове використання кожного замкового режиму — але семантика всюди однакова. Реальною різницею між ними є тільки - набір замкових режимів з яким коден з них конфліктує (подивіться Таблицю 13-2). Дві транзакції не можуьб тримати блокування суперечливих режимів на одній таблиці в один час. (Проте, транзакція ніколи не суперечить собі. Наприклад, вона може включити ACCESS EXCLUSIVE і пізніше включити ACCESS SHARE на одній таблиці.) Неконфліктні замкові режими можуть тримати одночасно багато транзакцій. Зверніть увагу зокрема, що деякі замкові режими само-конфліктні (наприклад ACCESS EXCLUSIVEне може тримати більш ніж одна транзакція за один раз), а деякі не само-конфліктні (наприклад, ACCESS SHARE можуть тримати кілька транзакцій).
Режими замків табличного рівня
ACCESS SHARE
Конфліктує тільки з ACCESS EXCLUSIVE .
Команда SELECT включає блокування цього режиму на таблицях на які посилається. Взагалі, будь-який запит що тільки читає таблицю і не змінює її, включить цей замковий режим.
ROW SHARE
Конфліктує з EXCLUSIVE іACCESS EXCLUSIVE режимами.
Команди SELECT FOR UPDATE і SELECT FOR SHARE включають блокування цього режиму на цільових таблицях (в додаток до того, що ACCESS SHARE блокує на будь-яких інших таблицях, на які посилається але які не вибрані FOR UPDATE/FOR SHARE).
ROW EXCLUSIVE
Конфліктує SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, і ACCESS EXCLUSIVE.
Команди UPDATE, DELETE, і INSERT включають цей замковий режим на цільовій таблиці (в додаток до, ACCESS SHARE блокувань на будь-яких інших таблицях на які посилаються). Взагалі, цей замковий режим буде включений будь-якою командою, яка змінює дані в таблиці.
SHARE UPDATE EXCLUSIVE
Конфліктує з SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE,
EXCLUSIVE, і ACCESS EXCLUSIVE . Цей режим захищає таблицю від зміни конкуруючої схеми і виконання VACUUM.
Включається VACUUM (без FULL), ANALYZE, і CREATE INDEX CONCURRENTLY.
SHARE
Конфліктує з ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE,
EXCLUSIVE, і ACCESS EXCLUSIVE. Цей режим захищає таблицю від паралельної зміни даних.
Включає CREATE INDEX (без CONCURRENTLY).
SHARE ROW EXCLUSIVE
Конфліктує з ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW
EXCLUSIVE, EXCLUSIVE, і ACCESS EXCLUSIVE.
Цей замковий режим автоматично не включає жолна команда PostgreSQL.
EXCLUSIVE
Конфліктує з ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE
ROW EXCLUSIVE, EXCLUSIVE, іACCESS EXCLUSIVE. Цей режим дозволяє тільки паралельні ACCESS SHARE блокування, тобто, тільки читання з таблиці може паралельно пройти з транзакцією що утримує цей блок.
Цей замковий режим автоматично не включається на таблицях жодною командою PostgreSQL. Проте він включається на певних системних каталогах в деяких операціях.
ACCESS EXCLUSIVE
Конфліктує з блоками усіх режимів (ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE
UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, і ACCESS EXCLUSIVE). Цей режим гарантує, що утримувач це єдина транзакція, що звертається до таблиці.
Включається командами ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER, і VACUUM
FULL. Це також заданий по умовчанню замковий режим для команд LOCK TABLE, які не задають режим явно.
Попередження: Тільки ACCESS EXCLUSIVE блокує команду SELECT (без FOR UPDATE/SHARE).
Включений замок тримається до кінця транзакції. Але якщо блокування отримується після встановлення точки збереження, блокування звільняється негайно, якщо відбувається прокрутка до точки збереження. Це не суперечить принципу, що ROLLBACK відміняє усі ефекти команд починаючи з точки збереження. Так само діє блокування в межах PL/pgSQL виняткових блоків(не замків): помилка що видається з блоку автоматично звільняє замок.
Таблиця 13-2. Конфліктні замкові режими
|
Запитаний Режим замку |
Даний режим замку |
|||||||
|
ACCESS SHARE |
ROW SHARE |
ROW EXCLUSIVE |
SHARE UPDATE EXCLUSIVE |
SHARE |
SHARE ROW EXCLUSIVE |
EXCLUSIVE |
ACCESS EXCLUSIVE |
|
|
ACCESS SHARE |
Х |
|||||||
|
ROW SHARE |
Х |
Х |
||||||
|
ROW EXCLUSIVE |
Х |
Х |
Х |
Х |
||||
|
SHARE UPDATE EXCLUSIVE |
Х |
Х |
Х |
Х |
Х |
|||
|
SHARE |
Х |
Х |
Х |
Х |
Х |
|||
|
SHARE ROW EXCLUSIVE |
Х |
Х |
Х |
Х |
Х |
Х |
||
|
EXCLUSIVE |
Х |
Х |
Х |
Х |
Х |
Х |
Х |
|
|
ACCESS EXCLUSIVE |
Х |
Х |
Х |
Х |
Х |
Х |
Х |
Х |
13.3.2. Замки рівня рядків
На додаток до замків рівня таблиць, є замки рівня рядків, які можуть бути винятковими(exclusive) або загальними(shared) замками. Виняткові замки рівня рядків на окремо взятому ряду автоматично включаються, коли ряд модифікується чи видаляється. Блокування тримається поки транзакція не здійсниться або прокрутиться назад, подібно до замків рівня таблиць. Замки рівня рядків не впливають на запит даних; вони блокують тільки записувачів до того ж ряду.
Щоб включити винятковий замок рівня рядків фактично, не змінюючи ряд, вибирають ряд з SELECT FOR UPDATE. Відмітьте, що як тільки замок рівня рядків включений, транзакція може модифікувати рядок багато раз без конфліктів.
Щоб включити загальний замок рівня рядків, вибирають ряд з SELECT FOR SHARE. Загальне блокування
не перешкоджає іншим транзакціям включити те ж загальне блокування. Проте жодній транзакції не дозволено модифікувати удаляти або винятково заблоковувати рядок на якому будь яка інша транзакція тримає загальний замок. Будь-яка спроба зробити таке блокується до звільнення загального замку.
PostgreSQL не зберігає ніякої інформації про модифіковані ряди в пам'яті, тож немає ніякої межі на числі рядів, блокованих водночас. Проте, блокування ряду, може викликати би запис на диск, наприклад SELECT FOR UPDATE змінює відібрані ряди щоб відмітити їх зоблокованими, і це призводитиме до записів на диск.
На додаток до замків рівнів таблиць і рядків, замки рівня сторінки загальні/виняткові використовуються, для керування доступом запису\читання до сторінок таблиці в загальному буфері. Ці блокування звільняються негайно після того, як ряд є вибраним або модифікованим. Роробникам додатків зазвичай не потрібно хвилюватися про замки рівня сторінки але про них згадано тут для завершеності розділу.
13.3.3. Взаємоблокування(мертві замки)(Deadlocks)
Використання явного блокування може збільшити вірогідність появи взаємоблокування, яке полягаю в тому що дві(чи більше) транзакції тримають замки які вимагають інші. Наприклад, якщо транзакція 1 включає виняткове блокування на таблиці А а потім пробує придбати виняткове блокування на таблиці B, коли транзакція 2 має вже
Виключно заблокувала таблицю B і зараз хоче виняткового блокування на таблиці А, тож жодна з них не може продовжувати. PostgreSQL автоматично шукає такі ситуації взаємоблокування і вирішує їх, припиняючи одну з транзакцій що входить до такого замку, дозволяючи іншому(им) завершитися. (Яка транзакція завершиться не можливо передбачити тож не треба покладатися на це.)
Відмітьте, що взаємоблокування можуть також відбуватися як результат замків рівня рядків (і отже, вони можуть відбуватися, навіть якщо явне блокування не використане). Розглянемо випадок, в якому дві конкуруючі транзакції змінюють таблицю.
Перша транзакція виконується:
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 11111;
Це включає замок рівня рядків нарядку з вказаним номером облікового запису. Потім, друга транзакція
виконується:
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 11111;
Перше UPDATE успішно включає замок рівня рядків на вказаному рядку, і успішно його модифікує ряду. Проте, друге UPDATE виявляє, що рядок, який воно намагається модифікувати вже є блокований, тож воно чекає завершення транзакції, яка заблокувала ряд. Транзакція два зараз зараз чекає зевершення транзакції один щоб продовжити своє виконання. Зараз транзакція один виконується:
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222;
Транзакція один намагається включити замок рівня рядків на вказаному ряду, але не може: транзакція два вже тримає таке блокування. Тож вона чекає звершення транзакції два. Отже, транзакція один є блоковано на транзакції два, і транзакція два блокується на транзакції один: умова взаємоблокуванняблокування. PostgreSQL виявить цю ситуацію і відмінить одну з транзакцій.
Кращий захист проти взаємоблокувань блокувань уникати їх будучи впевненим, що усі додатки
користуючись базою даних включаєють блокування на множинних об'єктах в несуперечливому порядку. У прикладі вище, якщо обидві транзакції модифікували ряди в тому ж порядку, ніякого взаємоблокування не сталося б. Ви також маєте переконатися що замок включений на об’єкті в транзакції є найбільш обмежеючим режимом який потрібний для даного об’єкта. Якщо не можливл переконатися в цьому заздалегідь, то взаємоблокування можуть бути оброблені на льоту перезапуском транзакції що буда відмінена через взаємне блокування, .
Поки, не виявиться ситуації взаємоблокування, операційний пошук табличного і рядкового рівнів замків буде чекати на конфліктне блокування. Це означає що тримання транзакцій відкритими довгий період часу є поганою ідеєю (наприклад, чекаючи призначеного для користувача введення).
13.3.4. Консультативні Замки
PostgreSQL забезпечує засоби для створення блокувань, орієнтованих для додатків. Це так звані консультативні блокування, тому що система не вмикає їх використання — додатки мають самі вирішувати як їм використовувати ці замки правильно. Консультативні блокування можуть бути корисні для блокувальних стратегій, доволі непристосованих до моделі MVCC. Ввімкнене, консультативне блокування тримається до явного звільненння або кінця сесії. На відміну від стандартних блокувань, консультативні блокування не шанують операційну семантику: замок придбаний транзакцією все ще буде триматися навіть після перемоткки назад, і розблокування є теж ефективним навіть якщо воно викличе помилки транзакції пізніше. Одне і те ж блокування може бути ввімкене багато раз процесом що володіє ним: для кожного запиту ввіскнення замку має бути запита його вимкнення до того як він дійсно звільниться. (Якщо сесія вже тримає дане блокування, додаткові запити завжди матимуть успіх навіть якщо інші сесії чекають блокування.) Подібно до усіх блокувань в PostgreSQL, повний список консультаційних блокувань що в даний час підтримується будь-якою сесією може бути знайдений в системному огляді pg_locks.
Консультативні блокування знаходяться поза загальним пулом пам'яті, чий розмір визначає конфігураційні змінні max_locks_per_transaction і max_connections. Необхідно слідкувати щоб не виснажити цю пам'ять або сервер буде не в змозі надати будь-які блокування взагалі. Це накладає верхнє обмеження числа консультативних блокувань що надаються сервером, зазвичай від десятків до сотень тисяч залежно від того, як сервер конфігурований.
Загальне використання консультативних блокувань - емуляція песимістичних стратегій які зазвичай називаються системами управління даними «квартирний(плоский) файл». Поки прапорець, що зберігається в таблиці, може бути використаний для тієї ж мети консультативні блокування є швидші, уникають розхитування MVCC, і автоматично забираються сервером в кінці сесії. У певних випадках, користуючись цим методом консультативних блокувань , особливо в запитах з використанням явного порядку і умови LIMIT, необхідно обережно включати такі замки через порядок в якому вирази SQL виконуються. Наприклад:
SELECT pg_advisory_lock(id) FROM foo WHERE id = 12345; -- ok
SELECT pg_advisory_lock(id) FROM foo WHERE id > 12345 LIMIT 100; -- danger!
SELECT pg_advisory_lock(q.id) FROM
(
SELECT id FROM foo WHERE id > 12345 LIMIT 100
) q; -- ok
Вище у запитах, друга форма небезпечна, тому що LIMIT не гарантовано вмконається перед тим, як блокувальна функція виконуватиметься. Це, може змусити включитися деякі замки, неочікувані додатком, і отже не в змозі їх звільнити (до кінця сесію). Від точки зору додатку, такі блокування звисали б, але їх все рівно було б видно у pg_locks.
Функції для маніпулювання консультативними блокуваннями описуються в Таблиці 9-61.
13.4. Перевірка Сталості Даних на Рівні Додатків
Оскільки читачі в PostgreSQL не блокують дані, незважаючи на рівень операційної ізоляції, дані що читаються однією транзакцією можуть бути записаний поверх іншою конкуруючою транзакцією. Іншими словами, якщо ряд повернений SELECT, це не означає, що ряд все ще такий самий як повернутий (, тобто коли-небудь після того, як поточний запит почався). Ряд, міг бути модифікований або видалений транзакцією що вже виконалась, після того, як SELECT почався. Навіть якщо ряд все ще дійсний "зараз" він міг бути змінений або видалений перед тим, як поточна транзакція виконається або перемотається назад.
Інший спосіб думки про це є те, що кожна транзакція бачить моментальний знімок вмісту бази даних, і транзакції що виконуються одночасно, могли бачити різні моментальні знімки. Тож концепт “зараз" недовизначений. Зазвичай це не є веиклою проблемою, якщо клієнтські застосування ізолюються один від одного, але якщо клієнти можуть зв'язатися через канали за межами бази даних тоді це може викликати серйозні проблеми.
Щоб гарантувати поточну валідність ряду і захистити його проти конкуруючих оновлень, один повинен використовувати SELECT FOR UPDATE, SELECT FOR SHARE, або відповідного LOCK TABLE. (SELECT
FOR UPDATE і SELECT FOR SHARE блокує тільки повернені ряди проти конкуруючих оновлень коли LOCK TABLE блокує цілу таблицю.) Це треба взяти до уваги, переносячи додатки до PostgreSQL від інших середовищ.
Глобальні перевірки достовірності даних вимагають особливих думок під MVCC. Наприклад, банківський додаток міг забажати перевірити, чи сума усіх кредитів в одномій таблиці дорівнює сумі дебету в іншій таблиці, коли обидві таблиці активно модифікуються. Порівнюючи результати дві послідовних SELECT sum(...) команди не працюватимуть надійно в Довіреному Читанні, оскільки другий запит ймовірно включатиме результати транзакцій, що не перераховуються першим. Виконання двох сум в одній серіалізабельній транзакція дасть точне представлення тільки ефектів транзакцій, що здійснені перед тим, як серіалізабельна транзакція почалася — але апсотає питання чи відповідь все ще актуальна до часу доставки. Якщо серіалізабельна транзакція безпосередньо застосувала деякі зміни перед спробою зробити перевірку послідовності, повноцінність перевірки стає навіть більше спірною, відколи зараз вона включає деякі але не усі стартові для посттранзакції зміни. У таких випадках обережна людина, можгла забажати заблокувати усі таблиці, потрібні для перевірки, для того, щоб отримати безперечне зображення поточної реальності. Блокування SHARE(чи вище) може гарантувати, що немає ніяких нейтральних змін у блокованій таблиці окрім надійшовших з поточної транзакції.
Відмітьте також, що, якщо покладатися на явне блокування, щоб запобігти конкуруючим змінам, необхідно також використовувати режим Довіреного Читання, або бути обережним в Серіалізабельному, щоб отримати блокування перед виконанням запитів. Блокування, отримане серіалізабельною транзакцією гарантує, що транзакції-модифікатори все ще працюють на таблиці, але якщо моментальний знімок, бачений транзакцію, передує отриманню блокування, воно могло передувати деяким здійсненим цього моменту змінам в таблиці. Моментальний знімок серіалізабельної транзакції є фактично замерзлий в запуску його першого запиту або команди модифікації даних (SELECT, INSERT, UPDATE, or DELETE), тож можливо отримати блокування явно перед тим, як моментальний знімок буде замерзлим.
13.5. Блокування і Індекси
Хоча PostgreSQL забезпечує неблокування доступу запису\читання до табличних даних, воно не є рекомендованим для кожного методу індексного доступу в PostgreSQL. Різні індексні типи обробляються як зазначено нижче:
B-tree і індекси GiST
Короткострокові загальні/виняткові замки рівня сторінок використовуються для доступу запису\читання. Блокування звільнені негайно після того, як кожен індексний ряд вибраний або вставлений. Ці індексні типи забезпечують найвищий паралелізм без умов взаємоблокування.
Індекси Хеш
Загальні/виняткові замки рівня хеш-комірок використовуються для доступу запису\читання. Замки звільнюються після опрацювання цілої комірки. Замки рівня хеш-комірок забезпечують кращий паралелізм, ніж замки індексного рівня, але взаємоблокування можливе оскільки блокування тримаються довше, ніж одна індесна операція.
Індекси GIN
Короткострокові загальні/виняткові замки рівня сторінок використовуються для доступу запису\читання. Блокування звільнені негайно після того, як кожен порядковий ряд вибраний або вставлений. Але відмітьте, що вставка GIN-індексованого значення зазвичай проводить декілька порядкових ключових вставок за ряд, тож GIN, може виконувати багато роботи для вставки одного значення.
Зараз, B-tree індекси пропонують краще виконання для конкуруючих застосувань; відколи вони також мають
більше функцій, ніж хеш індекси, вони - рекомендований індексний тип для конкуруючих застосувань яким необхідно індексувати скалярні дані. Маючи справу з нескалярними даними, B-trees не корисні, і GiST або GIN індекси мають бути використані замість цього.
Глава 14. Забезпечення Швидкодії(Performance Tips)
На швидкодію запиту можуть впливати багато речей. Деякими з них може управляти користувач, поки інші фундаментальні для основного дизайну системи. Ця глава забезпечує деякі підказки для розуміння і налашьування швидкодії PostgreSQL.
14.1. Використання EXPLAIN
PostgreSQL створює план запиту для кожного запиту, який вона отримує. Вибір правильного план відповідного до
Структури запиту і властивостей даних дуже критично для забезпечення хорошої швидкодії, так система включає складного планувальника, який пробує вибрати хороші плани. Ви можете користуватися командою EXPLAIN щоб поглянути, який план запиту створює планувальник для будь-якого запиту. Читання плану - мистецтво, яке заслуговує об’ємного туторіалу(повчання), але це не воно; проте тут є деяка основна інформація.
Структура плану запиту - дерево вузлів плану. Вузли на рівні дна дерева - вузли табличного сканування : вони повертають необроблені ряди із таблиці. Є різні види вузлів сканування для різних методів доступу до таблиць : послідовні сканування, порядкові сканування, і індексні сканування бітового масиву. Якщо запит вимагає об’єднання, з'єднання частин, сортування, або інші дії над необробленими рядами, то це будуть додаткові вузли над вузлами сканування, для виконання цих операціїй. Знову, зазвичай є більш ніж один шлях для виконання цих операцій тож різні типи вузлів можуть появлятись туту також. Вивід EXPLAIN має один рядок на кожен вузол в дереві планів, що показує основний тип вузла плюс оцінює затрати що витратив планувальник для створення даного плану. Перший рядок (самий верхній вузол) має загальну вартість плану; це - цей номер, який планувальник прагне мінімізувати.
Ось - тривіальний приклад, тільки, щоб показати, що вивід нагадує:
EXPLAIN SELECT * FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
Числа що видані, EXPLAIN є (зліва направо) :
• Вирахувана старотова вартість (час, витрачений перед тим як вивід сканування, може початися, наприклад, час, щоб зробити сортування у сортувальному вузлі)
• Загальна вартість (, якщо усі ряди відшукані, хоча їх, може і не бути; наприклад, запит з умовою LIMIT зупинить стислість платежу повної вартості вхідного вузла вузла плану Limit)
• Оцінене число рядів, виведених цим вузлом плану(знову, тільки якщо виконання завершується)
• Оцінена середня ширина (у байтах) рядів, виведених цим вузлом плану
Витрати зважені в довільних модулях, визначених вартісними параметрами планувальника (подивіться Секцію 18.6.2). Традиційна практика - виміряти витрати в модулях вибірок дискових сторінок; тобто, seq_page_cost задано як 1.0 і інші вартісні параметри встановлюються відносно цього. (Приклади в цій секції виконуються із заданими по умовчанню вартісними параметрами.)
Важливо відмітити, що вартість вузли вузла вижчого рівня включає вартість усіх його дочірніх вершин. Вона є
також важлива, для усвідомлення того, що вартість тільки віддзеркалює речі, про які планувальник піклується. Зокрема вартість не враховує час, що витрачається, передаючи ряди результату до клієнта, який міг бути важливим чинником в тривалості дійсної розробки; але планувальник нехтує цим, тому що зміна плану не впливає на цей час. (Кожен правильний план виведе однаковий набір рядків.)
Значення rows трохи хитре, тому що це - не число рядів, оброблених або проглянутих вузлом плану. Воно зазвичай менше, видає кількість рядків що підійшли під умову WHERE у вузлі. Ідеально оцінка рядів верхнього рівня апроксимує число рядів фактично повернених, модифікованих, або видалених запитами.
Повертаючись до нашого прикладу:
EXPLAIN SELECT * FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
Воно таке ж пряме як і здається. Якщо ви робите:
SELECT relpages, reltuples FROM pg_class WHERE relname = ’tenk1’;
ви виявите, що tenk1 має 358 дискових сторінок і 10000 рядів. Повна вартість обчислюється як (disk
pages read(кількість прочитаних дискових сторінок) * seq_page_cost) + (rows scanned(кількість сканованих рядків) * cpu_tuple_cost) За умовчанням, seq_page_cost_складає 1.0 і cpu_tuple_cost складає 0.01, тож повна вартість складає(10000 * 0.01) = 458.
Зараз давайте змінимо оригінальний запит додавши умову WHERE :
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;
QUERY PLAN
------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..483.00 rows=7033 width=244)
Filter: (unique1 < 7000)
Зверніть увагу, що вивід EXPLAIN показує пропозицію WHERE, вживану як "філтр" умову; це
означає, що вузол плану перевіряє умову на кожен ряд, який переглядає, і виводить тільки ті що відповідають умові. Оцінка рядів виводу була зменшена із-за пропозиції WHERE. Проте скануванню все ще доводитиметься відвідати усі 10000 рядів, тож вартість не зменшилася; фактично вона зросла небагато
(на 10000 * cpu_operator_cost , щоб бути точним), щоб показати додатковий час CPU, що проводиться, перевіряючи WHERE умову.
Фактичне число рядів що вибере цей запит складає 7000, але оцінка rows тільки приблизна.
Якщо ви пробуєте дублювати цей експеримент, ви ймовірно отримаєте трохи іншу оцінку; окрім того
Вона буде змінюватись після кожної ANALYZE команди, тому що статистика щовидає ANALYZE взята з довільного зразка таблиці.
Зараз, давайте зробимо умову більш обмежувальною:
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=2.37..232.35 rows=106 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 < 100)
Тут планувальник вирішив користуватися двох короковим планом: план вузол дна відвідує індекс щоб знайти розташування рядів, що відповідають індексній умові, а потім верхнійплан вузол фактично вибирає ті рядки з таблиці безпосередньо. Вибирання рядів окремо значно дорожче, ніж їх послідовне читання , але через те, що не усі сторінки таблиці доведеться відвідати, це все ще дешевше, за послідовне сканування. (Причина для користування двома плановими рівнями є, що верхній план вузол місця розташування рядків визначених за індексом у фізичний порядок, щоб мінімізувати вартість окремої вибірки. "Бітовий масив", згаданий в іменах вузлів, - механізм, який робить сортування.)
Якщо умова WHERE – достатньо вибіркова, планувальник, може перемкнутися дов "простого" плану індексного сканування :
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 3;
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 (cost=0.00..10.00 rows=2 width=244)
Index Cond: (unique1 < 3)
В цьому випадку табличні ряди вибираються в індексному порядку, що робить їх ще дорожчими для читання але таке незначне здорозчання все ж ліпше за збільшення вартості внаслідок сортування їх локацій. Ви частіше всьогобудете зустрічати даний тип планування для запитів що вибирають тільки один рядок, і для запитів, які мають ORDER BY умову що відповідає індексному порядку.
Додмо іншу умову до умови WHERE :
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 3 AND stringu1 = ’xxx’;
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 (cost=0.00..10.01 rows=1 width=244)
Index Cond: (unique1 < 3)
Filter: (stringu1 = ’xxx’::name)
Додатковий умова stringu1 = 'xxx' скорочує оцінку вивідних рядків, але не вартість, тому що нам все ще доведеться відвідати той же набір рядів. Зверніть увагу, що stringu1 умова не може бути застосована, як порядкова умова (оскільки цей індекс є тільки на unique1 стовпці). Замість цього вона застосовується як фільтр на рядах, відшуканих індексом. Отже вартість піднялася трохи, щоб відзеркалити дану провірку.
Якщо є індекси на декількох стовпцях, на які посилається в WHERE, планувальник, може вибрати використання AND або OR комбінації індексів :
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;
QUERY PLAN
-------------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=11.27..49.11 rows=11 width=244)
Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
-> BitmapAnd (cost=11.27..11.27 rows=11 width=0)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique2 (cost=0.00..8.65 rows=1042 width=0)
Index Cond: (unique2 > 9000)
Але це вимагає відвідування обох індексів, тож не є обов’язковим використання тільки одного індексу і опрацьовувати іншу умову як фільтр. Якщо ви зміните діапазони що використовуються, ви побачите, як план змінюється відповідно.
Давайте попробуємо об’єднати дві таблиці, користуючись стовпцями, які ми обговорюємо:
EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
--------------------------------------------------------------------------------------
Nested Loop (cost=2.37..553.11 rows=106 width=488)
-> Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 < 100)
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00..3.01 rows=1 width=244)
Index Cond: (t2.unique2 = t1.unique2)
У цьому приєднанні з використанням вкладених циклів, зовнішнє (верхнє) сканування є тим самим бітовим масивом що ми бачили раніше тож вартість незмінна, тому що ми користувалися умовою WHERE unique1 < 100 на цьому вузлі . Умова t1.unique2 = t2.unique2 не доступна поки що, тож вона не впливає на кількість рядів зовнішньоьго сканування. Для внутрішнього (нижнього) сканування, unique2 значення поточного ряду зовнішнього скануваня вставляється у внутрішнє індексне сканування, щоб щоб забезпечити виконання індексної умови типу t2.unique2 = constant. Тож ми отримуємо той же план внутрішнього сканування і витрати, які ми б отримали від, скажімо, EXPLAIN SELECT * FROM tenk2 WHERE unique2 = 42. Витрати на вузол циклу потім встановлюються на підставі вартості зовнішнього сканування, плюс одне повторення внутрішнього сканування для кожного зовнішнього ряду (106 * 3.01, тут), плюс маленький час CPU для обробки приєднання.
У цьому прикладі кількість рядків виводу така сама як і в результаті двох сканувань але це не буде вірно в усіх випадках, тому що ви можете мати, WHERE умови, які посилаються на дві таблиті, тож можуть бути виконані тільки на етапі об’єднання, а ле не на кожному з вхідних сканувань. Наприклад, якщо ми додали, WHERE ...
AND t1.hundred < t2.hundred , це зменшило б кількість рядків виводу вузла об’єдання(об’єднаного вузла), але не змінило би жодного вхідного сканування.
Єдиний шлях подивитися на різні плани - змусити планувальника проігнорувати вирішення того яка стратегія буде найдешевшою, користуючись флагами enable/disable, описаними в Секції 18.6.1. (Це незрілий інструмент, але корисний. Подивіться також Секцію 14.3.)
SET enable_nestloop = off;
EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
------------------------------------------------------------------------------------------
Hash Join (cost=232.61..741.67 rows=106 width=488)
Hash Cond: (t2.unique2 = t1.unique2)
-> Seq Scan on tenk2 t2 (cost=0.00..458.00 rows=10000 width=244)
-> Hash (cost=232.35..232.35 rows=106 width=244)
-> Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 < 100)
Цей план пропонує витягнути 100 цікавих для нас рядів tenk1, використовуючи таке ж старе порядкове сканування, поміщуючи їх у хеш-таблиці в оперативній пам'яті, а потім зробити послідовне сканування tenk2, досліджуючи хеш таблицю для можливих відповідностей t1.unique2 = t2.unique2 для кожного tenk2 ряду. Вартість читання tenk1 і встановлення хеш-таблиці - стартова вартість для залечення хешу, оскільки не буде ніякого виводу поки ми не почнемо читання tenk2. Повна оцінка часу для приєднання також включає важке навантаження CPU, для перегляду хеш таблиці 10000 раз. Занотуйте, проте, що ми не заряджаємо 10000 разів 232.35; хеш таблиця установлена тільки раз в цьому типу плану.
Можливим є перевірити точність планувальника у розрахунку вартості, EXPLAIN ANALYZE. Ця команда фактично виконує запит, а потім відображає середній час запуску, накопичений в середині кожного план вузла разом з тією ж вартістю що показує EXPLAIN. Наприклад, ми можемо отримати результат подібно до цього:
EXPLAIN ANALYZE SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------Nested Loop (cost=2.37..553.11 rows=106 width=488) (actual time=1.392..12.700 rows=100 -> Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244) (actual time=Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0) (actual Index Cond: (unique1 < 100)
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00..3.01 rows=1 width=244) (actual Index Cond: (t2.unique2 = t1.unique2)
Total runtime: 14.452 ms
Відмітьте, що значення "Фактичний час"(“actual time”) є в мілісекундах реального часу, тоді як вартісні оцінки виражаються в довільних юнітах(без знаку валюти чи буль якого іншого); тож вони навряд чи співпадуть. Треба приділити увагу тому чи значення реалльного часу і вирахуваної вартості не є суперечливі.
У деяких планах запиту, це можливо для підпланового вузла виконуватися більше одного разу. Наприклад внутрішнє індексне сканування виконується однин раз для одного зовнішнішнього ряду в плані вище по вкладеному циклу. У таких випадках значення loops повідомляє повне число виконань вузла, і фактичні значення часу і рядів – є середніми за кількість виконань. Це зроблено, щоб зробити числа такими що можуть порівнюватись з шляхом в який показується вирахувана вартість. Помноже на значення loops для отримання фактичного часу проведеному у вузлі.
Total runtime, показаний EXPLAIN ANALYZE включає стартовий час, час завершення також і час потрачений на обробку рядів результату. Він не включає граматичний аналіз(parsing), повторні записи, або час планування. Для запиту SELECT, повна тривалість виконання зазвичай буде тільки трохи більше, ніж повний час план вузла верхнього рівня. Для команд INSERT, UPDATE, і DELETE, повна тривалість виконання можливо, була би значно більшою, тому що вона включає час, що проводиться, обробляючи ряди результату. Для цих команд, час для вищого план вузла є по суті час включає розташування старих рядів і/чи обчислювання нових, але не включає час, що проводиться, на застосовання змін. Час на запис тригерів, якщо вони є, є також за межами вищого план вузла, і показується окремо для кожного тригера.
Тож результати EXPLAIN можуть відноситися тільки до тих ситуацій що ви тестуєте; наприклад, зезультати маленької таблиці ніколи не можна використовувати для великої таблиці. Вартісні оцінки планувальника не лінійні тож він може використовувати різний план для великої чи малої таблиці. Критичним прикладом є коли таблиця займає тільки одну дискову сторінку, ви будете майже завжди отримувати послудовне сканування не залежно від присутності індексів. Планувальник усвідомлює, що таблиця не буде розміщена на інших дикових сторінках в будь якому випадку тож немає потреби лазити по інших сторінках шукаючи індекс.
14.2. Статистика Що Вирокистовується Планувальником
Як ми побачили з попередньої секції, планувальникові запитів треба оцінити число відшуканих рядів запитом для того, щоб зробити хороші альтернативи планів запиту. Ця секція забезпечує швидкий погляд на статистику, що система використовує для цих оцінок.
Один компонент статистики - повне число входів в кожну таблицю і індекс, також як і число дискових блоків, зайнятих кожною таблицею і індексом. Ця інформація зберігається в таблиці pg_class, в стовпці reltuples і relpages. Ми можемо подивитися на це із запитами, подібними до цього:
SELECT relname, relkind, reltuples, relpages
FROM pg_class
WHERE relname LIKE ’tenk1%’;
relname | relkind | reltuples | relpages
----------------------+---------+-----------+----------
tenk1 | r | 10000 | 358
tenk1_hundred | i | 10000 | 30
tenk1_thous_tenthous | i | 10000 | 30
tenk1_unique1 | i | 10000 | 30
tenk1_unique2 | i | 10000 | 30
(5 rows)
Тут ми можемо бачити, що tenk1 містить 10000 рядів, як роблять його індекси, але індекси є (недивно) набагато менші, ніж таблиця.
Через причини ефективності, reltuples і relpages не модифікуються на льоту, тож вони переважно місять застарілі значення. Їх модифікує VACUUM, ANALYZE , і декілька команд DDL типу CREATE INDEX. Автономне застосування ANALYZE, що не є частиною VACUUM, генерує приблизне reltuples значення оскільки воно не читає кожен ряд таблиці. Планувальник вимірить значення, які знайде в pg_class, щоб співставити з даним фізичним розміром таблиці, для кращого наближення.
Більшість запитів відшукують тільки фракцію рядів в таблиці, через умови WHERE, які обмежують ряди які мають бути дослідженими. Планувальникові треба зробити оцінку виборності умов WHERE, що є, фракцією рядів, які відповідають кожній умові в умові WHERE. Інформація використовувана для цього завдання зберігається у системному каталозі pg_statistic. Входи в pg_statistic модифіковані командами ANALYZE і VACUUM ANALYZE, і завжди приблизні навіть, коли свіжо модифіковані.
Замість перегляду pg_statistic безпосередньо, краще подивитися на його представлення pg_stats, досліджуючи статистику вручну. pg_stats спроектований для легкості читання. До того ж, pg_stats можуть читати усі, тоді як pg_statistic тільки суперкористувач. (Це запобігає непривілейованим користувачевам дізнавати щось з таблиць інших людей зі статистики. pg_stats представляє тільки рядки в доступних користувачу таблицях.) Для прикладу, ми, можгли зробити таке:
SELECT attname, inherited, n_distinct,
array_to_string(most_common_vals, E’\n’) as most_common_vals
FROM pg_stats
WHERE tablename = ’road’;
attname | inherited | n_distinct | most_common_vals
---------+-----------+------------+------------------------------------
name | f | -0.363388 | I- 580 Ramp+
| | | I- 880 Ramp+
| | | Sp Railroad +
| | | I- 580 +
| | | I- 680 Ramp
name | t | -0.284859 | I- 880 Ramp+
| | | I- 580 Ramp+
| | | I- 680 Ramp+
| | | I- 580 +
| | | State Hwy 13 Ramp
(2 rows)
Відмітьте, що два ряди відображаються для одного стовпця, один відповідає за повну наслідувальну ієрархію таблиці road(inherited=t), а інший включає тільки таблицю road (inherited=f).
Інформацією збереженою в pg_statistic ANALYZE ом, є максимальна кількість з входів в most_common_vals і histogram_bounds масиви для кожного стовпця, може бути встановлений на основі стовпця за стовпцем, користуючись командою ALTER TABLE SET STATISTICS, або глобально змінно заданням default_statistics_target configuration змінної. Задана по умовчанню межа складає на даний час 100 входів. Підйом межі, можливо, дозволив би бути точнішими оцінкам планувальника , особливо для стовпців з нерегулярними розповсюденням даних, ціно за це було б збільшення розміру pg_statistic а отже і збільшення часу її опрацювання. З іншого боку, нижча межа, можливо, була б достатня для стовпців з простим розповсюдженням даних.
Більше деталей про використання планувальника статистики можуть бути знайдені в Главі 56.
14.3. Контроль Планувальника Явними Умовами JOIN
Можливо управляти планувальником запиту до деякої міри користуючись явним синтаксисом JOIN. Щоб подивитися чому це має значення, спочатну нам потрібний якийсь фон.
У простому запиті приєднання, як наприклад:
SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;
планувальник вільний, з’єднувати ці таблиці в будь якому порядку. Наприклад, він міг би генерувати план запиту що приєднує А до B, користуючись WHERE умовою a.id = b.id, а потім приєднати до таблиці C, що приєднується користуючись іншою, WHERE умовою. Він міг би приєднати B до C а потім приєднати А до цього результату. (Усі приєднання у PostgreSQL виконуються між двома таблицями тож результат формується виходячи з цього правила(з’єднуються тільки 2 таблиці).) Важливим покажчиком є те що можливості різного приєднання дають семантично еквівалентні результати але різні вартості виконання. Тому, планувальник дослідить усіх їх, щоб знайти найефективніший запит.
Коли запит тільки включає дві або три таблиці, немає про які порядки об’єднання хвилюватись. Але число можливих замовлень приєднання може рости екпоненціально, оскільки число таблиць розширюється. Після десяти і більше –нелогічно їх виснажуючий пошук усіх варіантів, та навіть для 6-7 таблиць планувальник буде шукати варіанти ненормально довгий час. Коли є занадто багато вхідних таблиць планувальник PostgreSQL переключиться з виснажливого пошуку до генетичного ймовірнісного пошуку серед обмеженої кількості можливих варіантів. (Поріг переключення встановлює geqo_threshold run-time параметр.) Генетичний пошук займає менший час, але він не обов'язково знайде кращий можливий план.
Коли запит включає зовнішні приєднання, планувальник має меншу свободу, ніж для явних (внутрішніх) приєднань.
Наприклад, вважайте:
SELECT * FROM a LEFT JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);
Хоча обмеження цього запиту поверхнево подібні до попереднього прикладу, семантика є іншою, тому що ряд має бути випущений для кожного ряду А що не має ніякого відповідного ряду в приєднанні B і C. Тому планувальник не має ніякого вибору порядку приєднання: спочатку приєдання B до C а потім приєднати А до цього результату. Відповідно, цей запит займає менший час, плануючись, ніж попередній запит. У інших випадках планувальник, можливо, зміг би визначити, що більш ніж одне приєднання безпечне. Наприклад, дано:
SELECT * FROM a LEFT JOIN b ON (a.bid = b.id) LEFT JOIN c ON (a.cid = c.id);
Валідним є приєднання А до B чи C спочатку. Зараз, тільки FULL JOIN повністю обмежує порядок приєднання. Самі практичні випадки, що включають LEFT JOIN чи RIGHT JOIN, можуть бути перебудовані до деякої міри.
Явний внутрішній синтаксис приєднаннь (INNER JOIN, CROSS JOIN, чи JOIN) семантично такий же як, лістинг вхідних взаємовідносин FROM, так це не обмежує порядок приєднання.
Хоча більшість видів JOIN не повністю обмежують порядок об’єднання, можна інструктувати планувальник запиту PostgreSQL, щрб він опрацьовував всі типи JOIN як такі що оммежують порядок приєданання в будь-якому випадку. Для
прикладу, ці три запити логічно еквівалентні:
SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;
SELECT * FROM a CROSS JOIN b CROSS JOIN c WHERE a.id = b.id AND b.ref = c.id;
SELECT * FROM a JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);
Але якщо ми говоримо планувальникові враховувати порядок JOIN, другий і третій приклади займають менший час планування за 1. На варто хвилюватись через цей ефект якщо є тільки 3 таблиці, але воно можу врятувати всю ситуацію при більшій кількості таблиць.
Щоб змусити планувальника слідувати порядку приєднання, явно заному у JOIN, встановлюють параметр часу виконання join_collapse_limit до 1. (Нижче обговорюються інші можливі значення.)
Вам не треба обмежувати порядок приєднання повністю для того, щоб зменшити пошуковий час, тому що це є нормально користуватися операторами JOIN в межах елементів FROM списку. Наприклад, приймемо:
SELECT * FROM a CROSS JOIN b, c, d, e WHERE ...;
З join_collapse_limit = 1, це змушує планувальника приєднати А до B перед приєднанням до них до іншої таблиці, але, в той же час, не обмежую його вибір. У цьому прикладі, число можливих порядків приєднання 5.
Обмеження пошуків планувальника таким шляхо – дуже корисна техніка як і для зменешення часу планування так і для спрямування планувальника до вибору найкращого запиту. Якщо планувальник вибирає поганий порядок приєднання за умовчанням ви можете змусити його вибрати кращий за допомогою синтаксису JOIN, якви ви знаєте кращий порядок звичайно. Експериментування рекомендоване.
Доволі близьким до питання про зменшення часу планування є, згортання підзапитів в їх батьківському запиті.
Наприклад, таке:
SELECT *
FROM x, y,
(SELECT * FROM a, b, c WHERE something) AS ss
WHERE somethingelse;
Ця ситуація може витікати з викорастання виду щр містить приєдання; правило SELECT буде вставлено замість посилання виду, зводячи запит так як вище. Нормально, планувальник пробуватиме згортати підзапит до батьківського:
SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;
Це зазвичай призводить до кращого плану, ніж планування підзапиту окремо. (Наприклад, зовнішні WHERE умови, можуть бути такими, що приєднання до X до А спочатку виключає багато рядів А, уникаючи повного виводу підзапиту.) Але в той же час, ми збільшуємо час планування; тут, ми маємо проблему п'ятишляхового приєднання, що замінюється двома окремими три-хляховими методами приєднання. Через експоненціальне зростання числа можливостей, таке можу дуже впливати. Планувальник пробує уникати застрявання у величезній проблемі варіантів вибору приєднання не згортаючи підзапит, якщо більш ніж from_collapse_limit FROM елементів призводили би до батьківського запиту. Ви можете обміняти час планування на якість плану коригуванням цього параметра під час виконання зменшення або збільшенням його.
from_collapse_limit і join_collapse_limit похоже названі, тому що вони роблять майже одну річ: один управляє, коли планувальник "вирівнює" підзапити, і інший управляє при вирівнюванні явних приєднання. Зазвичай ви також встановили б join_collapse_limit рівною from_collapse_limit(щоб явні приєднання і підзапити діяли подібно) або встановлюють join_collapse_limit 1 (якщо ви хочете управляти порядком приєднання з явними приєднаннями). Але ви можете вставлювати їх як собі захочете якщо ви намагаєтесь знайти золоту середину між часом планування і часом виконання.
14.4. Заповнення Бази даних
Комусь можливо треба буде вставити велику кількість даних при первинному заповненні бази даних. Ця секція містить деякі судження про те, як зробити цей процес таким ефективним як тільки можливо.
14.4.1. Вимкнення Автопідтвердження
Користуючись множинними INSERTs вимкніть автопідтвердження і тільки зробіть тільки одне підтвердження вкінці. (У SQL це виглядає так: BEGIN спочатку і COMMIT вкінці. Деякі клієнтські бібліотеки, можуть робити так без вашої участі, в цьому випадку ви маєте переконатися чи дійсно дана бібліотека підтримує таке виконання.) Якщо
ви дозволяєте кожній вставці підтверджуватись окремо, PostgreSQL робить багато роботи для кожного ряду, що додається. Додаткова вигода від виконання усіх вставок в одну транзакцію є, що, якщо вставка одного ряду потерпіла невдачу то вставка всіх рядів що були вставлені до цьої точки буде прокручена назад тож вам не треба буде хвилюватися про частково завантажені дані.
14.4.2. Використання COPY
Користуйтеся COPY, щоб завантажити усі ряди в одній команді, замість серії команд INSERT. Команда COPY оптимізована для завантаження великої кількості рядів; вона менш гнучка, ніж INSERT, але має значно менше надлишковостей при завантаженнях великої кількості даних. Оскільки COPY - єдина команда, нетреба вимикати автопідтвердження при використання даного методу заповлення таблиці.
Якщо ви не можете користуватися COPY, може допомогти команада PREPARE для створення підготовленої команди INSERT а потім використовуйте EXECUTE так багато раз як потрібно. Це зменшує деякі надлишковості при повторюванні і плануванні INSERT. Різні інтерфейси забезпечують цей засіб в різний спосіб; шукайте "підготовлені твердження" в інтерфейсній документації.
Відмітьте, що, завантаження великої кількості рядів, користуючись COPY є майже завжди швидше, ніж INSERT, навіть якщо використовується PREPARE і багато вставок поміщені в одну транзакцію.
COPY швидша всього, коли її використовують в одній транзакції з командами CREATE TABLE чи TRUNCATE. У таких випадках ніякий WAL не треба записувати, тому що у разі помилки, файли що містять недано завантажені дані будуть видалені всерівно. Проте, та тільки, коли wal_level є minimal, оскільки в іншому випадку усі команди повинні записати WAL.
14.4.3. Видалення індексів
Якщо ви завантажуєте свіжо створену таблицю, найшвидший метод - створити таблицю, завантажити таблицю даними використовуючи COPY,а потім створити будь-які індекси, потрібні для таблиці. Створення індексу на існуючих даних швидше, ніж модифікування інкрементуючи кожний завантажений ряд.
Якщо ви додаєте великі кількості даних до існуючої таблиці, було би ефективним спочатку скинути всі індекси, завантажити таблицю і потім знову відновити всі індекси. Звичайно, швидкодія бази даних для інших користувачів
можливо, впала впід час того коли індекси відсутні. Треба також двічі подумати перед тим як скидати унікальні індекси оскільки перевірка на помилки дозволена унікальним обмеженням буде втрачені під час відсутності індексу.
14.4.4. Видалення сторонніх ключових обмежень
Так як і з індексами, сторонні ключові обмеження можуть бути перевірені «всі разом» більш ефективно ніж рядок за рядком. Тож може бути корисним скинути всі сторонні ключові обмеження , завантажити дані, і відновити обмеження. Знову ж таки, ви можете обміняти швидкість завантаженняданих на втрату перевірок на помилки поки обмежень немає.
Більше того, при завантаженні даних в таблицю з присутніми сторонніми ключовими обмеженнями, кожен новий рядок вимагатиме доступу до серверного списку тригерних подій(оскільки запуск тригера і виконує перевірку сторонніх ключових обмежень). Завантаження багатьох мільйонів рядків можк призвести до переповлнення доступної пам’яті запитами тригерних подій, що може спричинити нетолерантний свопінг чи навіть повний провал команди. Тож може бути важливим , а не лишень бажаним, скинути і пере-створити сторонні ключі при завантаженні великих об’ємів даних. Якщо типчасове видалення обмежень неприйнятне, єдиним виходом може бути розподіл операції завантаження на менші транзакції.
14.4.5. Збільшення maintenance_work_mem
Тимчасово збільшуючи змінну конфігурації maintenance_work_mem при завантаженні великих об’ємів даних може викликати збільшення швидкодії. Це допоможе пришвидшити команди CREATE INDEX і ALTER TABLE ADD FOREIGN KEY. Але воно незробить того ж для COPY, тож ця порада корисна тільки при використанні однієї або обох вишевказаних технік.
14.4.6. Збільшення checkpoint_segments
Тимчасове збільшення змінної конфігурації checkpoint_segments може також допмогти великим об’ємам даних завантажуватися швидше. Це через те що завантаження великих об’ємів даних в PostgreSQL призведе до збільшення частоти появи чекпоінтів в порівнянні з нормальною(визначену змінною конфігурації checkpoint_timeout). Будь коли коли появляється чекпоінт всі брудні сторінки мають записувати на диск. Тимчасовим збільшенням checkpoint_segments під час об’ємних завантажень даних, можна зменшити частоту появи чекпоінтів.
14.4.7. Вимкнення WAL архівації і потокоої реплікації
При завантаження великих об’ємів даних в інсталяцію що містить WAL архівацію або потокову реплікацію, швидшим може були взяти новий бекап даних по завершенні завантаження ніж виконували великий об’єм інкрементаційних даних WAL. Щоб попередити появу інкрементаційної WAL при завантаженні вимкніть архівацію і потокову реплікацію, заданням wal_level до minimal, archive_mode до off, and max_wal_senders до zero(0). Але майте наувазі що зміна цих значень призведе до перезавантаження сервера. Крім уникнення запусків WAL, це також збільшить швидкість деяких команд, тому що вони спроектовані без записів WAL взагалі(Вони можуть гарантувати безпеку від збоїв дешевшим способом роблячи fsync вкінці заміть запису WAL). Це діє для наступних команд:
• CREATE TABLE AS SELECT
• CREATE INDEX (і варіантів типу ALTER TABLE ADD PRIMARY KEY)
• ALTER TABLE SET TABLESPACE
• CLUSTER
• COPY FROM, коли цільво таблиці створена на обрізаній раніше цією ж транзакцією
14.4.8. Запуск ANALYZE після цього
Будь коли коли ви значно змінюєте поширення(розповсюдження) даних по таблиці використання ANALYZE дуже рекомендоване. Це відноситься і до завантаження великого об’єму даних в таблицю. Запуск ANALYZE (або VACUUM ANALYZE) перевіряє чи планувальник має актуальну статистику про таблицю. Без статистики, планувальник може приймати неправильні рішення під час планкування запитів, що призведе до погіршення швидкодії на будь-якій таблиці з неактуальною або некоректною статистикою. Відмітьте що якщо майстер автовакууму ввімкнений, то ANALYZE може запускатися автоматично; подивіться сеції 23.1.3 і 23.1.5 для більшої інформації
14.4.9. Деяка інформація про pg_dump
Дампові скрипти генеровані pg_dump автоматично включається кілька але не всі з вищестоящих нормативів. Для перезавантаження pg_dump так швидко як це тільки можливо, вам прийдеться зробити декілька речей вручну(Помітьте що ці речі відносяться до відновлення дампу а не створення його. Воно діє незалежно чи завантажувати дамп за допомогою psql чи використовувати pg_restore для завантаження з архівного файлу pg_dump).
За замовчуванням pg_dump використовує COPY, і коли генерується повний дамп схеми і даних, важливим є завантажити дані перед створенням індексів і сторонніх ключів. Тож в цьому випадку декілька керуючих нормативів опрацьовуються автоматично. Все що залишається зробити вам це:
• Встановіть відповідні (тобто, більші ніж зазвичай) значення для maintenance_work_mem і
checkpoint_segments.
• Якщо використовується WAL архівація або реплікація потоків вимкніть їх під час відновлення. Щоб зробити це задайте archive_mode на off, wal_level на minimal, і max_wal_senders на zero(0) перед завантаженням дампу. Потім поставте їх звичні значення і оновіть бекап бази.
• Прийміть що цілий дамп необхідно відновити однією транзакцією. Щоб зробити це примініть командний рядок -1 чи --single-transaction до psql чи pg_restore. Під час використання даного режиму навіть найменша помилка прокрутить назад ціле відновлення, можливо відміняючи кількагодинну роботу)). Залежно від того як розміщені дані, ручне прибирання можу бути ліпшим або ні. Команди COPY працюватимуть швидше всього, якщо ви користуєтеся єдиною транзакцією і маєте WAL архівацію вимкненою.
• Якщо кілька CPU доступні в сервері бази даних, користуйтеся --jobs опцією pg_restore . Вона дозволяє завантаження конкуруючих даних і порядкове створення.
• Запустіть ANALYZE після цього всього.
Дим тільки даних буде використовувати COPY, але він не буде скидати або чтворювати індекси, і він не буде зачіпати сторонні ключі. Тож при використанні дампу тільки даних – вм вирішувати чи створювати чи скидати індекси і сторонні ключі і якими модиками це робити. Все ще є корисним збільшення checkpoint_segments при завантаженні даних, але можете не збільшувати maintenance_work_mem; зробіть це коли вже будете створювати індекси і сторонні ключі після всього. І не забувайте робити ANALYZE після завершення; подивіться секції 23.1.3 і 23.1.5 для більшої інформації.
Глава 38. Процедурні Мови
PostgreSQL користувацьким функціям бути записаним на інших мовах окрім SQL і C. Ці інші мови в загальному називають процедурними мовами (PLs). Для функції, записаної в процедурній мові, сервер бази даних не має ніякого вбудованого знання про те, як інтерпретувати код функції. Замість цього, завдання передається спеціальному обробникові, який знає деталі мови. Обробник може виконувати усю роботу по граматичному аналізу, аналізу синтаксису, виконанню, і т.п. безпосередньо, або він можу слугувати "клеєм" між PostgreSQL і існуючою реалізацією мови програмування. Обробник це функція на писана на C скомпільована в загальному об’єкті і завантажувана по вимозі, так я к і інші функції C.
Є зараз чотири процедурні мови, доступні в стандартній версії PostgreSQL : PL/pgSQL (Глава 39), PL/Tcl (Глава 40), PL/Perl (Глава 41), і PL/Python (Глава 42). Є додаткові доступні процедурні мови, що не входять в центральний дистрибутив(ядро). Додаток G має інформацію про їх знаходження. Крім того інші мови можуть визначати користувачі; основи розробки нової процедурної мови покриваються в Главі 49.
38.1. Установка Процедурних Мов
Процедурна мова має бути "встановлена" в кожній базі даних, де вона має використовуватися. Але процедурні мови встановлені в базі даних template1 автоматично повністю доступні базам даних створених згодом, оскільки їх входи в template1 скопіюються CREATE DATABASE. Так адміністратор бази даних може вирішити які мови в яких базах даних будуть доступні і може зробити деякі мови, доступними за умовчанням, якщо вирішить так.
Для мов, підтримуваних стандартною версією, необхідно тільки виконати CREATE LANGUAGE language_name для інсталяції мови в дану базу даних . Альтернативно, програмний створювач-мов може бути використаний, щоб зробити це з командного рядка. Наприклад, для установки мови PL/Perl у база даних template1, використовують:
createlang plperl template1
Ручна процедура описана нижче рекомендована тільки для про які CREATE LANGUAGE знає.
Ручна Інсталяція Процедурної Мови
Процедурна мова встановлюється у базу даних в п'ять кроків, які повинен здійснювати суперкористувач бази даних. (Для мов, які, відомо, CREATE LANGUAGE з другого по четвертий кроки можуть бути опущений оскільки вони здійснюватимуться автоматично, якщо треба.)
1. Загальний об'єкт для обробника мови маєу бути скомпільований встановлений в відповідний бібліотечний каталог. Це працює таким самим чином як будування і встановлення можулів з стандарстими користувацькими функціями С; подивіться Секцію 35.9.6. Часто, обробник мови залежатиме від зовнішньої бібліотеки, яка забезпечує фактичний механізм мови програмування; якщо так, то вона повинна також бути встановленою.
2. Обробник має бути оголошений командою
CREATE FUNCTION handler_function_name()
RETURNS language_handler
AS ’path-to-shared-object’
LANGUAGE C;
Спеціальний тип повертання language_handler_говорить системі бази даних, що ця функція не повертає один з визначених SQL типів даних і безпосередньо не придатний для використання в твердженнях SQL.
3. Опціонально, обробник мови може забезпечити функцію обробника "влінію", яка виконує анонімні блоки коду (DO команди), записані на цій мові. Якщо вбудована функція обробника забезпечується мовою, оголосіть її командою
CREATE FUNCTION inline_function_name(internal)
RETURNS void
AS ’path-to-shared-object’
LANGUAGE C;
4. Опціонально, обробник мови може забезпечити функцію "валідатора", яка перевіряє функцію на коректність без її фактичного виконання. Функція-валідатор, якщо вона присутня, викликається CREATE FUNCTION. Якщо функція-валідатор забезпечується вибраною мовою викличіть її так:
CREATE FUNCTION validator_function_name(oid)
RETURNS void
AS ’path-to-shared-object’
LANGUAGE C;
5. PL має бути оголошена командою
CREATE [TRUSTED] [PROCEDURAL] LANGUAGE language-name
HANDLER handler_function_name
[INLINE inline_function_name]
[VALIDATOR validator_function_name] ;
Необов'язкове ключове слово TRUSTED задає, що мова не надає доступ до даних, що користвувач не може отримати іншим способом. Довірені мови проектуються для звичайних користувачів бази даних (без привілею суперкористувача) і дозволяють їм безпечно створювати функції і тригера. Оскільки функції PL виконуються усередині сервера бази даних, прапорець TRUSTED повинен бути наданий тільки мовам, які не дозволяють доступ до нутрощів сервера бази даних або файлової системи. Мови PL/pgSQL, PL/Tcl, і PL/Perl за замовчуванням довірені; мови PL/TclU PL/PerlU, і PL/PythonU спроектовані, щоб забезпечити необмежену функціональність і не повинні бути відмічені як довірені.
Приклад 38-1 показує , як би ручна інсталяційна процедура працювала з мовою PL/Perl.
Приклад 38-1. Ручна Інсталяція PL/Perl
Наступна команда говорить серверу бази даних, де знайти загальний об'єкт для функції PL/Perl обробника мови:
CREATE FUNCTION plperl_call_handler() RETURNS language_handler AS
’$libdir/plperl’ LANGUAGE C;
PL/Perl має вбудовану функцію обробника і функцію-валідатор, тож ми оголошуємо їх теж:
CREATE FUNCTION plperl_inline_handler(internal) RETURNS void AS
’$libdir/plperl’ LANGUAGE C;
CREATE FUNCTION plperl_validator(oid) RETURNS void AS
’$libdir/plperl’ LANGUAGE C;
Команда:
CREATE TRUSTED PROCEDURAL LANGUAGE plperl
HANDLER plperl_call_handler
INLINE plperl_inline_handler
VALIDATOR plperl_validator;
потім визначає, що заздалегідь оголошені функції треба викликати для процедур функцій і тригерів де атрибут мови - plperl.
У заданій по умовчанню інсталяції PostgreSQL, обробник для PL/pgSQL мови сформований і встановлений всередину каталогу "Бібліотека(library)"; до того ж, PL/pgSQL мова безпосередньо встановлюється в усіх базах даних. Якщо підтримка Tcl сконфігурована, обробники для PL/Tcl і PL/TclU сформовані і встановлені у каталозі бібліотека, але мова безпосередньо не встановлюється у будь-якій базі даних за умовчанням. Також, PL/Perl і PerlU обробники PL/сформовані і встановлені, якщо підтримка Perl конфігурована, і PythonU обробник PL/є встановлюється, якщо підтримка Python сконфігурована, але ці мови не встановлюються за умовчанням.
Глава 39. PL/pgSQL – Процедурна мова SQL
39.1. Короткий огляд
PL/pgSQL - завантажувана процедурна мова для PostgreSQL системи бази даних. Цілями дизайну PL/pgSQL були створення завантажувальної процедурної мови що
• може бути використаною для створення функцій і тригерних процедур
• додає керуючі структури, до мови SQL
• може виконувати складні обчислення
• наслідує усі визначувані користувачем типи, функції, і операторів
• може бути визначеною, щоб бути довіреною сервером
• простою для використання.
Функції створені PL/pgSQL може бути використані скрізь, де стандартні функції змогли бути б використані. Для прикладу, можливо створити складні умовні функції обчислення і пізніше користуючись ними для визначення операторів або користуватися ними в індексних виразах.
У PostgreSQL 9.0 і пізніше, PL/pgSQL встановлюється за умовчанням. Проте це - все ще завантажуваний модуль тож особливо свідомі для захисту адміністратори змогли захотіти видалити його.
39.1.1. Переваги Користування PL/pgSQL
SQL - мова PostgreSQL і більшості інших реляційних баз даних використовувана як мова запитів. Вона є
переносною і простою, для вивчення. Але кожне твердження SQL повинне виконуватися індивідуально сервером бази даних.
Це означає, що ваше клієнтський додаток повинен відправити кожен запит серверу бази даних, почекати його обробки, отримати його і опрацювати результати, зробити деякі обчислення обчислення, потім а потім відправити інші запити на сервер. Усе це вимагає міжпроцесної взаємодії і вносить надлишки в мережу, якщо ваш клієнт на іншій машині, ніж сервер бази даних.
З PL/pgSQL ви можете групувати блок обчислення і серії запитів усередині серверу бази даних, тож ви маєте владу процедурної використання SQL, з надзишками комунікації між клієнтом і сервером.
• Зайва пересилка туди і назад між клієнтом і сервером виключені
• Проміжні результати, які клієнту не потрібні, не треба переміщувати між сервером і клієнтом
• Можна уникнути багаторазового аналізу запиту
Це може вирости у значе збільшення швидкодії в порівнянні з додатками що не користуються збереженими функціями.
Також, з PL/pgSQL ви можете користуватися усіма типами даних, операторами і функціями SQL.
39.1.2. Підтримуваний Аргумент і Результуючий тип Даних
Функції записані в PL/pgSQL може приймати аргументи будь якого скалярного або масивного типу даних підтримуваних сервером, і вони можуть результувати в будь який з цих типів. Вони також можуть приймати або повертати будь-який складний тип(рядковий тип) визначений ім’ям. Також можливо оголосити функції PL/pgSQL як повертаючий record, що означає що результат є рядковим типом чиї стовпці визначені специфікаціями у запиті, як обговорюється у Секції 7.2.1.4.
PL/pgSQL функції можуть бути оготошені такими що приймають змінну кількість аргументів використовуючи маркерVARIADIC. Це працює точно так само як для функцій SQL, як обговорюється в Секції 35.4.5.
PL/pgSQL можуть також бути оголошені, щоб прийняти і повернути поліморфний типи anyelement anyarray, anynonarray, і anyenum. Фактичні типи даних, оброблених поліморфною функцією можуть мінятися від виклику до виклику як обговорено в Секції 35.2.5. Приклад показується в Секції 39.3.1.
PL/pgSQLможуть також бути оголошені, щоб повернути "набір" (або таблицю) будь-якого типу даних що може бути повернений як єдиний зразок. Така функція генерує свій вивід виконуючи RETURN NEXT для кожного бажаного елементу результуючого набору, або користуючись RETURN QUERY, щоб вивести результат оцінки зробленого запиту.
Нарешті, PL/pgSQL функція може бути оголошена, щоб повернутися void, якщо це не має ніякого корисного значення повернення.
PL/pgSQL функції можуть також бути оголошені з параметрами виводу замість явної специфікації типу що повертається. Це не додає ніякої фундаментальної можливості до мови, але це часто зручно особливо для повернення множинних значень. RETURNS TABLE може використовуватись замість RETURNS SETOF.
Специфічні приклади з'являються в Секції 39.3.1 і Секції 39.6.1.
39.2. Структура PL/pgSQL
PL/pgSQL - мова з блоковою структурою. Повний текст з визначенням функції має бути блоком.
Блок визначений, як:
[ <<label>> ]
[ DECLARE
declarations ]
BEGIN
statements
END [ label ];
Кожне оголошення і кожне твердження в межах блоку закінчується крапкою-комою. Блок, який з'являється
в межах іншого блоку повинен мати крапку з комою після END, як показано вище; проте кінцевий END, що завершує, тіло функції не вимагає крапки з комою.
Попередження: загальна помилка - записати крапку з комою негайно після BEGIN. Це некоректно і буде приводити до синтаксичної помилки.
label потрібна тільки, якщо ви хочете ідентифікувати блок для використання в твердженні EXIT, або для готування імен змінних, оголошених у блоці. Якщо мітка дається після END,вона повинна відповідати мітці на початку блоку.
Усі ключові слова є нечутливими до регістру. Ідентифікатори неявно конвертуються в нижній регістр за винятком тих що є в подвійних лапках, так ніби вони є звичайними командами SQL.
Коментарії пишуться в коді PL/pgSQL так, як і в звичайному SQL. Подвійним дефісом (--) починається коментарій це тягнеться до кінця рядка. /* запускає блоковий коментар, який тягнеться до наступного */.
Будь-яке твердження в операторній секції блоку може бути підблоком. Підблоки можуть використовуватись для групування або локалізації змінних в маленькі групи тверджень. Змінні, оголошені в підблоці маскують будь-які подібно названі змінні зовнішніх блоків на час існування підблоку; але ви всерівно зможете звернутись до зовнішніх змінних інших блоків просто задаючи їх імена з мітками їх блоків. Наприклад:
CREATE FUNCTION somefunc() RETURNS integer AS $$
<< outerblock >>
DECLARE
quantity integer := 30;
BEGIN
RAISE NOTICE ’Quantity here is %’, quantity; -- Prints 30
quantity := 50;
--
-- Create a subblock
--
DECLARE
quantity integer := 80;
BEGIN
RAISE NOTICE ’Quantity here is %’, quantity; -- Prints 80
RAISE NOTICE ’Outer quantity here is %’, outerblock.quantity; -- Prints 50
END;
RAISE NOTICE ’Quantity here is %’, quantity; -- Prints 50
RETURN quantity;
END;
$$ LANGUAGE plpgsql;
Відмітьте: є фактично прихований "зовнішній блок", що оточує тіло будь-якої PL/pgSQL функції. Цей блок забезпечує оголошення параметрів (якщо є) функції, також як і деяких спеціальних змінних як наприклад FOUND (подивіться Секцію 39.5.5). Зовнішній блок маркірується з ім'ям функції означаючи, що параметри і спеціальні змінні можуть бути кваліфіковані з ім'ям функції.
Важливо не поплутати команди BEGIN/END для групування тверджень в PL/pgSQL з так само-названими командами SQL для контролю транзакцій. PL/pgSQL BEGIN/END є тільки для групування; вони не починають або закінчують транзакцію. Функції і тригерні процедури завжди виконується в межах транзакції, встановленій зовнішнім запитом – вони не можуть почати або завершити цю транзакцію, оскільки немає контексту в якому вони могли б виконуватись. Проте, блок, що містить умову EXCEPTION фактично формує підтранзакцію, яка може бути прокручена назад без впливу на зовнішню транзакцію. Для більшо інформації подивіться Секцію 39.6.5.
39.3. Оголошення
Усі змінні, використані у блоці, мають бути оголошені в секції оголошень блоку. (Єдиним виключенням є ітераційна змінна циклу FOR, що виконує ітерації над діапазоном цілочисельних значень, вона є автоматично оголошено як ціла змінна, і також параметр циклу циклу FOR, що виконує ітерації результат курсора автоматично оголошується як record змінна(запису).)
PgSQL змінні PL/можуть мати будь-який SQL тип даних, як наприклад integer, varchar, і char.
Ось деякі приклади оголошень змінних :
user_id integer;
quantity numeric(5);
url varchar;
myrow tablename%ROWTYPE;
myfield tablename.columnname%TYPE;
arow RECORD;
Загальний синтаксис опису змінної є:
name [ CONSTANT ] type [ NOT NULL ] [ { DEFAULT | := } expression ];
Умова DEFAULT, якщо є задана, задає початкове значення, призначене змінній, при вході в блок. Якщо умова DEFAULT не дається то змінна ініціалізувалася до SQL нульового значення. CONSTANT опція не дає змінній перепризначатися, тож така змінна буде константою до кінця блоку. Якщо NOT NULL вказаний, призначення нуля видає помилку. Усі змінні оголошені, як NOT NULL повинны мати своэ ненульове значення за замовчуванням.
Значення змінної за умовчанням вирахуване і призначене змінній кожного разу при вході в блок (не лише одного разу на виклик функції). Так, наприклад, призначаючи now() змінну типу timestamp примушує змінну мати час поточного звернення до функції, не час, коли функція була скомпільована.
Приклади:
quantity integer DEFAULT 32;
url varchar := ’http://mysite.com’;
user_id CONSTANT integer := 10;
39.3.1. Оголошення Параметрів Функції
Параметри, що передаються функціям, названі з ідентифікатори $1, $2, і т.п. Опціонально, псевдоніми можуть бути
оголошено для імен параметрів типу $n для підвищеної читабельності. Або псевдонім, або числовий ідентифікатор може потім бути використаний, щоб послатися на значення параметра.
Є два шляхи створити псевдонім. Шлях, що вважається кращим, - надати ім'я параметру в команді CREATE FUNCTION , наприклад:
CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
BEGIN
RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;
Іншим шляхом є, єдиний доступний для версій до PostgreSQL 8.0, - явно оголосити псевдонім, користуючись синтаксисом оголошення
name ALIAS FOR $n;
Той же приклад в цьому стилі нагадує:
CREATE FUNCTION sales_tax(real) RETURNS real AS $$
DECLARE
subtotal ALIAS FOR $1;
BEGIN
RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;
Відмітьте: Ці два приклади не абсолютно еквівалентні. У першому випадку, на subtotal може посилатися як sales_tax.subtotal, але в другому випадку так не можна. (Якщо б ми приєднали мітку до свнутрішнього блоку, subtotal зміг бути кваліфікований з цією міткою.)
Дещо більше прикладів:
CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$
DECLARE
v_string ALIAS FOR $1;
index ALIAS FOR $2;
BEGIN
-- some computations using v_string and index here
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION concat_selected_fields(in_t sometablename) RETURNS text AS $$
BEGIN
RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
END;
$$ LANGUAGE plpgsql;
Коли PL/pgSQL функція оголошується з параметрами виводу, їм дані імена $n - типу і необов'язкові псевдоніми в таким шляхом як і для нормальних вхідних параметрів. Вихідний параметр - фактично змінна, яка стартує з значення NULL; фона має бути призначена під час виконання функції. Кінцеве значення параметра - те, що повернене. Наприклад, податковий приклад може бути зроблений таким чином:
CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
BEGIN
tax := subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;
Зверніть увагу, що ми опустили RETURNS real — ми могли включити його, але воно було лишнім.
Вихідні параметри самі корисні, при повертання кількох знаяень. Тривіальний приклад є:
CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
BEGIN
sum := x + y;
prod := x * y;
END;
$$ LANGUAGE plpgsql;
Як обговорюється в Секції 35.4.4, це ефективно створює анонімний тип запису(record) для результату функції. Якщо умова RETURNS дана, вона повинна казати RETURNS record.
Іншим способом оголошення PL/pgSQL функції є з використанням RETURNS TABLE, наприклад:
CREATE FUNCTION extended_sales(p_itemno int)
RETURNS TABLE(quantity int, total numeric) AS $$
BEGIN
RETURN QUERY SELECT quantity, quantity * price FROM sales
WHERE itemno = p_itemno;
END;
$$ LANGUAGE plpgsql;
Це точно еквівалентно до оголошення одного або більше OUT параметрів і задання RETURNS SETOF
sometype.
Коли тип повертання PL/pgSQL функціі оголошується як поліморфний (anyelement anyarray, anynonarray, або anyenum), створюється спеціальний параметр $0. Його тип даних є фактичним типом результату функції, що виведений від вхідних типів (подивіться Секцію 35.2.5). Це дозволяє функції мати доступ до фактичного типу повертання, як показано в Секції 39.3.3. $0 ініціалізований, як нуль і може бути модифікований функцією, тож він може використовуватися для утримання резльтату якщо необхідно. $0 може також мати псевдонім. Наприклад, ця функція працює над будь-яким типом даних, який має + оператор:
CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
RETURNS anyelement AS $$
DECLARE
result ALIAS FOR $0;
BEGIN
result := v1 + v2 + v3;
RETURN result;
END;
$$ LANGUAGE plpgsql;
Тако ж ефекту можна досягнути огалошенням одного або більше параметрів виводу поліморфними типами. У цьому випадку параметр $0 не використовується; вихідні параметри самі по собі виконують ту саму ціль.
Наприклад:
CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement,
OUT sum anyelement)
AS $$
BEGIN
sum := v1 + v2 + v3;
END;
$$ LANGUAGE plpgsql;
39.3.2. Псевдоніми
newname ALIAS FOR oldname;
Синтаксис ALIAS загальніший, ніж пропонується в попередній секції: ви можете оголосити псевдонім
для будь-якої змінної, а не лише для параметрів функції. Основною ціллю цього є надання інших і мен змінним з наперед заданими іменами, як наприклад NEW чи OLD в межах тригерної процедури.
Приклади:
DECLARE
prior ALIAS FOR old;
updated ALIAS FOR new;
Оскльіки ALIAS створює два різні способи називати той же об'єкт, необережне використання може привести до спантеличення. Краще користуватися цим тільки з метою перевизначення наперед заданих імен.
39.3.3. Копіювання Типів
variable%TYPE
%TYPE задає тип даних стовпця змінної або таблиці. Ви можете користуватися цим, щоб оголосити змінні, що зберігатимуть значення бази даних. Наприклад, припустимо ви маєте стовпець названий user_id у вашій таблиці users. Щоб оголосити змінну з тими ж типом даних як і users.user_id ви записуєте:
user_id users.user_id%TYPE;
Користуючись %TYPE, вам не треба знати тип даних структури, на яку ви посилаєтеся, і найбільш важливо, якщо тип даних згадуваного елементу змінюється в майбутньому (наприклад: ви змінюєте тип user_id від integer до real), вам, не требу було б змінювати оголошення вашої функції.
%TYPE особливо цінний в поліморфних функціях, оскільки типи даних, потрібні для змінних можуть зиінюватися від одного виклику до іншого. Відповідні змінні можуть бути створені застосуванням %TYPE до аргументів функції або змінних результату.
39.3.4. Рядкові типи
name table_name%ROWTYPE;
name composite_type_name;
Змінна складеного типу називається змінною ряду (чи row-type(рядкового типу) змінна). Змінна такого типу може вміщувати увесь результат запитів SELECT або FOR, поки набір стовбців запиту відповідає визначеному типу змінної. Індивідуальні поля рядкового значення доступні за допомогою звичайного оператор крапки, наприклад rowvar.field.
Рядкова змінна може бути оголошена того ж типу що і рядки в таблиці або виді, з допомогою table_name%ROWTYPE; або вона може бути оголошена іменем складного типу. (Оскільки кожна таблиця має асоційовані з нею складні типи однакових імен, то в PostgreSQL не має значення чи ви пишете %ROWTYPE чи ні. Але форма з %ROWTYPE більш портабельна.)
Параметри до функції можуть бути складних типів (повні рядки таблиці). В цьому випадку звітуючий ідентифікатор $n біде рядковою змінною, і порля можуть бути вибраними з нього, для прикладу $1.user_id.
Тільки користувацькі стовпці можуть бути доступні для змінний рядкового типу, але не OID чи інші системні стовпці(тому що рядок можу бути з виду). Поля рядкового типу успадковують розмір рядка або ПРЕЦЕСІЮ для типу даних з таблиці типу char(n).
Ось приклад використання складних типів. table1 і table2 і мають хоча б одне згадуване поле:
CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$
DECLARE
t2_row table2%ROWTYPE;
BEGIN
SELECT * INTO t2_row FROM table2 WHERE ... ;
RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;
END;
$$ LANGUAGE plpgsql;
SELECT merge_fields(t.*) FROM table1 t WHERE ... ;
39.3.5. Записні типи(типи запису)( Record Types)
name RECORD;
Змінні запису подібні до змінних рядкового типу, але в них нема наперед визначеної структури. Вони приймають дійсною структуру яку вони отримують внаслідок команд SELECT або FOR. Підструктура змінної запису може змінюватись під час кожного її присвоєння. Це означає що до свого першого дійсного присвоєння така зимінна немає підструктури і кожна спроба доступу до її полів викличе помилку часу виконання.
Помітьте що RECORD не є справжнім типом даних і тільки його тримачем. Також необхідно зрозуміти що коли функція PL/pgSQL оголошена з типом повертання record, це не те само що змінна запису, навіть якщо ця функція використовує таку змінну для вміщення свого результату. В обох випадках структура рядку не відома після написання функції, але для функції що повертає record дійсна структура визначається під час аналізу запиту, тоді коли змінна запису можу змінювати свою структуру в польоті.
39.4. Вирази
Всі вирази що є в PL/pgSQL опрацьовуються використовуючи головний серверний SQL виконувач.
Наприклад якшо ви пишете вираз PL/pgSQL типу
IF expression THEN ...
PL/pgSQL опрацює звіт посиланням запиту типу
SELECT expression
головному движку SQL. Під час формування команди SELECT всі появи імен змінних PL/pgSQL замінюються параметрами, деталі в Секції 39.10.1. Це дозволяє плану звіту SELECT бути підготовленим лише раз а потім перевикористовуватись для подібних розрахунків з різними значеннями змінних. Тож що дійсно стається при першому використанні команди PREPARE. Для прикладу, якщо ми оголосили дві цілочисельні змінні x and y, і написали
IF x < y THEN ...
Те що стається за сценою еквівалентне такому
PREPARE statement_name(integer, integer) AS SELECT $1 < $2;
І потім цей підготовлений рядок EXECUTE(виконується) для кожної команди IF, з даними значеннями змінних PL/pgSQL використаних як значення параметрів. План запиту підготовлений у такий спосіб збережений на період життя з’єднання з базою даних, як описано в Секції 39.10.2. Зазвичай ці деталі не є важливими для користувача PL/pgSQL, але вони корисні для діагностики проблем.
39.5. Базові твердження
В цій і наступних секціях, ми описуємо всі типи тверджень явно зрозумілих для PL/pgSQL. Все що не є одним з них є командою SQL і воно відсилається на головний движок бази для виконання, як описується в секціях 39.5.2 39.5.3.
39.5.1. Призначення
Призначення значення змінній в PL/pgSQL пишеться так:
variable := expression;
Як пояснювалось раніше, вираз в такому твердженні опрацьовується командою SQL SELECT
Що посилає це до головного движка бази. Вираз має зводити одиничне значення (можливо рядкове значення, якщо змінна є рядкового типу). Цільова змінна має бути одиничною змінною(опціонально кваліфікована з іменем блоку), полем рядку або змінною запису, або елементом масиву що є простою змінною або полем.
Якщо тип даних результату не співпадає з типом даних змінної, чи змінна має специфічний розмір\ПРЕЦЕСІЮ (типуchar(20)), значення результату буде неявно конвертоване інтерпретатором PL/pgSQL використовуючи результуючий тип виводу функції і тип змінної на вводі функції. Помітьте що це може призвести до помилки часу виконання генерованої ввідною функцією, якщо стрінгова форма результуючого значення не прийнятна для ввідної функції.
Приклади:
tax := subtotal * 0.06;
my_record.user_id := 20;
39.5.2. Виконання команди Без результату
Для будь-якої команди SQL що не повертає рядки, для прикладу INSERT без умови RETURNING, ви можете виконати команду всередині функції PL/pgSQL простим написанням цієї команди.
Будь-яке ім’я змінної PL/pgSQL що з’являється в тексті команди опрацьовується як параметр, а потім значення цієї змінної опрацьовується як параметр під час виконання. Це точно так само як виконання описане раніше для виразів; для деталей Секція 39.10.1.
Під час виконання команди SQL таким шляхом, PL/pgSQL планує команду лише раз а потім повторно використовує її для інших виконань, до кінця зв’язку з базою даних. Використання цього показані в секції Section 39.10.2.
Деколи корисним є опрацювання виразу або запиту SELECT але відміна його результату, для прикладу виклик функції що має побічні ефекти але некорисне значення результату. Для виконання цього в PL/pgSQL, використовуйте твердження PERFORM:
PERFORM query;
Це виконує query і відміняє результат. Пишіть query так як би ви писали в команді SQL SELEC, але замініть ключове слово SELECT з PERFORM. PL/pgSQL змінні будуть передані до запиту як до команди що не має результату, і план знаходиться в такий самий шлях. Також, спеціальна змінна FOUND призначається true якщо звіт видає хоч один рядок, або false якщо не видається жодного (Дивіться секцію 39.5.5).
Відмітьте: Ви можете очікувати що SELECT безпосередньо завершить цей результат, але зараз єдиним можливим варіантом є використання PERFORM. Команди SQL що можуть повертати, типу SELECT, буде відкинена як помилкова хіба що присутня умова INTO що обговорюється в наступній секції.
Приклад:
PERFORM create_mv(’cs_session_page_requests_mv’, my_query);
39.5.3. Виконання запиту з результатом в один рядок
Результат команд SQL що видають один рядок (можливо декілька стовпців) може бути призначений до змінної запису, змінної рядкового типу, або списку скалярних змінних. Це робиться написанням базових команд SQL
І додавання умови INTO. Для прикладу,
SELECT select_expressions INTO [STRICT] target FROM ...;
INSERT ... RETURNING expressions INTO [STRICT] target;
UPDATE ... RETURNING expressions INTO [STRICT] target;
DELETE ... RETURNING expressions INTO [STRICT] target;
де target може бути (record) змінною запису, рядковою змінною, або розділеним комами списком простих змінних і записових\рядкових полів. PL/pgSQL змінні будуть замінені і в решті запиту, і план знаходиться, так само як вищеописаний для команд що не повернтають рядки. Це працює для SELECT, INSERT/UPDATE/DELETE з RETURNING, і утилітних команд що попертають резутьтат набором рядків(типу EXPLAIN). За винятком опції INTO, команди SQL такі самі як і поза PL/pgSQL.
Попередження: Відмітьте що інтерпретація SELECT з INTO відрізняється від звичайної PostgreSQL команди
SELECT INTO , де INTO ціль – це новостворена таблиця. Якщо вихочете створити таблицю з результату SELECT всередині функції PL/pgSQL використовуйте синтаксис CREATE TABLE ... AS SELECT.
Якщо ряд або змінний список використовуються як ціль, стовпці результату запиту повинні точно відповідати структурі мети відносно кількості і типів даних, іншакше виникне помилка часу виконання. Коли змінна запису є метою, вона автоматично конфігурує себе до типу рядків результуючих стовпців запиту.
Умова INTO може з'явитися майже будь-де в команді SQL. Зазвичай вона записується або якраз перед або тільки після списку select_expressions в команді SELECT, або у кінці команди для інших командних типів. Рекомендується дотримуватись цих правил оскільки PL/pgSQL аналізатор може стати строгішим в майбутніх версіях.
Якщо STRICT не визначений в умові INTO, то target буде призначена до першого рядка поверненого запитом, або до нулів якщо запит не повертає рядків. (Помітьте що “перший рядок” не явно визнаяений якщо ви не користуєтесь ORDER BY.) Будь які результати після першого рядка є відкиненими. Ви можете перевірити спеціальну змінну FOUND (Section 39.5.5) щоб визначити чи рядок був повернений:
SELECT * INTO myrec FROM emp WHERE empname = myname;
IF NOT FOUND THEN
RAISE EXCEPTION ’employee % not found’, myname;
END IF;
Якщо опція STRICT визначена, запит має повертати тільки один рядок, бо в іншому випадку буде помилка часу виконання типу NO_DATA_FOUND (нема рядків) або TOO_MANY_ROWS (більше одного рядка). Ви можете використовувати блок виключення(винятковий блок), якщо бажаєте спіймати помилку:
BEGIN
SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE EXCEPTION ’employee % not found’, myname;
WHEN TOO_MANY_ROWS THEN
RAISE EXCEPTION ’employee % not unique’, myname;
END;
Успішне виконання команди STRICT завжди ставить FOUND до true.
Для INSERT/UPDATE/DELETE з RETURNING, PL/pgSQL видає помилку для більш ніж одного поверненого рядка, навіть якщо STRICT не визначений. Тому що нема опції типу ORDER BY для визначення який саме рядок буде повернений.
Note: Опція STRICT підпадає під поведінку Oracle PL/SQL’s SELECT INTO і подібних тверджень.
Для опрацювання випадків де вам потрібно опрацювати декілька рядків з запиту SQL подивіться секцію 39.6.4.
39.5.4. Виконання Динамічних Команд
Часто вам буде потрібно реалізувати динамічні команди у ваших функціях PL/pgSQL, що є командами що будуть задіювати різні таблиці і різні типи даних кожен раз при їх виконанні. PL/pgSQL зазвичайні спроби знайти план для команд(як обговорено в Section 39.10.2) не буду працювати для такого сценарію. Для вирішення такої проблеми існує твердження EXECUTE :
EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];
де command-string є вирозом що зводить лінійку(рядок) (типу text) що містить команду що має бути виконана. Опціональна target є змінною запису, рядковою змінною, або розділеним комами списком простих змінних і записових\рядкових полів, де будуть зберігатися результати команди. Опціональні вирази USING підтримують вставку значень в команду.
Ніякої заміни змінних PL/pgSQL не відбувається на обчислених рядках команд. Всі змінні що вимагаються вомандою повинні бути вставленими в командну строку так як спроектовано; або ви можете використовувати параметри так як описано нижче.
Також не знаходить плану для команд створених EXECUTE. Замість цього команда готується кожного разу коли вона має запускатись. Тож командний рядок може бути динамічно створений всередині функції для виконання дій на різних таблицях з різними типами даних.
Умова INTO задає куди результуючі рядки команди SQL мають бути призначені. Якщо в рядок чи список змінних то вони мають точно співпадати зі структурою результуючих рядків (при використанні змінної запису, вона сконфігурує свою структуру автоматично). Якщо є декілька рядків результату то тільки самий перший буде призначений до змінної INTO . Якщо немає рядків результату, NULL буде призначений до INTO. Якщо нема умови INTO результат запиту є вимкненим.
Якщо дана опція STRICT помилка буде якщо результат відмінний від оного рядка. Командний рядок може використовувати параметри що позначені як $1, $2 і т.д. в команді. Ці символи посилаються на значення умови USING . Цим методом зручніше вставляти потрібні змінні як текст: це уникає надлишків часу на конвертацію до тексту і назад, і це значно менш схильно др SQL-інєкційних атак оскльки не вимагає ставити лапки. Приклад:
EXECUTE ’SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2’
INTO c
USING checked_user, checked_date;
Відмітьте що символи парамертів можуть бути використані тільки для значень даних — якщо ви хочете використовувати динамічно розбриділену таблицею або імена стовпців, ви маєте ввести їх вручну в командний рядок. Для прикладу, якщо попередній запит потребував бути виконаним на динамічно вибраній таблиці, ви можете зробити це:
EXECUTE ’SELECT count(*) FROM ’
|| tabname::regclass
|| ’ WHERE inserted_by = $1 AND inserted <= $2’
INTO c
USING checked_user, checked_date;
Іншим обмеженням параметричних символів є те що вони працюють тільки з SELECT, INSERT, UPDATE, і
DELETE. В інших типіх команд (загально названими утилітними командами), ви маєте вставляти дані текстуально навіть якщо це звичайні значення.
EXECUTE з простим сталим командним рядком і з деякими параметрами USING , що в першому прикладі зверху, є еквівалентною простому написанню команди безпосередньо в PL/pgSQL і дозволяє атоматичну заміну змінних PL/pgSQL. Важливою різницею є те що EXECUTE переплановує команду перед кожним виконанням, генеруючи план з новими потрібними значеннями; коли PL/pgSQL зазвичай знаходить один план і використовує його багато раз. В ситуаціях де найкращий план залежить лишень від значень змінних, EXECUTE може бути значно швидшим; а коли план не чутливий до значень параметрів, репланування буде втратою часу.
SELECT INTO в даний час не підтримується в EXECUTE; замість цього виконуйте план команди SELECT з визначенням INTO як частиною EXECUTE .
Note: PL/pgSQL EXECUTE команди ніяк не відносяться до EXECUTE SQL команд підтримуваних PostgreSQL сервером. Серверні EXECUTE команди не можуть бути безпосередньо використані в середині функцій PL/pgSQL .
Приклад 39-1. Вставляння в лапки значень в динамічних запитах
При працюванні з динамічними командами вам часто потрібно буде маніпулювати слешами вбо одинарними лапками. Рекомендованим видом вставленням в лапки є доларове вставлення в лапки(лапкування). (Якщо в вас є легальний код що не використовує доларове лапкування подивіться Section 39.11.1, яка допоможе вам в деяких моментах перекладу коду в кращу схему.)
Динамічні значення для вставки вимагають дбайливого маніпулювання оскільки вони самі по собі можуть містити символи лапок. Приклад (перебачає використання доларово лапкування, тож не потрібно ставити подвійні лапки):
EXECUTE ’UPDATE tbl SET ’
|| quote_ident(colname)
|| ’ = ’
|| quote_literal(newvalue)
|| ’ WHERE key = ’
|| quote_literal(keyvalue);
Цей приклад демонструє використання quote_ident і quote_literal (гляньте Section
9.4). Для безпеки, вирази що містять табличні або стовпчикові ідентифікатори повинні пройти через quote_ident перед вставкою їх в динамічний запит. Вирази що містять значення що мають бути літеральними рядками в сконструйованій команді повинні пройти крізь quote_literal. Ці функції виконують відповідні кроки для повернення вхідного тексту обмеженого в одинарні або подвійні лапки відповідно, з усіма присутніми спеціальними символами коректно виведеними(заслешованими).
Через те що quote_literal позначені STRICT, вона завжди буде повертати 0 якщо викликана з 0 значенням. В прикладі вище якби newvalue чи keyvalue були 0, весь запит був би 0 і виникла би помилка від EXECUTE. Ви можете уникнути цього використанням функції quote_nullable яка працює так само як і quote_literal за винятком того що викликана з 0 аргументом вона видає рядок NULL. Для прикладу,
EXECUTE ’UPDATE tbl SET ’
|| quote_ident(colname)
|| ’ = ’
|| quote_nullable(newvalue)
|| ’ WHERE key = ’
|| quote_nullable(keyvalue);
Якщо ви працюєте з значеннями що можуть бути 0, вам зазвичай потрібно використовувати quote_nullable замість quote_literal.
Як завжди , потрібно слідкувати щоб нульові значення в запиті не призвели до неочікуваного результату.
Для прикладу умова WHERE
’WHERE key = ’ || quote_nullable(keyvalue)
Ніколи не буде успішною якщо keyvalue є 0, тому що результатом виконання операції на еквівалентність = з нульовим операндом завжди є нуль. Якщо ви хочете щоб 0 працював як звичане значення, вам потрібно буде переписати вище як
’WHERE key IS NOT DISTINCT FROM ’ || quote_nullable(keyvalue)
(Зараз, IS NOT DISTINCT FROM значно менш ефективно ніж =, тож не використовуйте його без необхідності. подивіться Section 9.2 для більшої інформації про 0 і IS DISTINCT.)
Занотуйте що доларове лапкування корисне тільки для лапкування фіксованого тексту. Дуже поганою ідеєю буде написати щось на зразок цього:
EXECUTE ’UPDATE tbl SET ’
|| quote_ident(colname)
|| ’ = $$’
|| newvalue
|| ’$$ WHERE key = ’
|| quote_literal(keyvalue);
Тому що воно зламається якщо вміст newvalue буде містити $$. Це саме для всіх доларових лапкувань. Тож для безпеки лапкування тексту з невідомив вмістом, ви маєте використовувати quote_literal, quote_nullable, чи quote_ident, відповідно.
Значно більший приклад EXECUTE і іншого є в Example 39-7, що будує і виконує CREATE FUNCTION команду для визначення нової функції.
39.5.5. Отримання Статусу Результату
Є декілька шляхів визначити ефект виконання команди. Перший метод використання GET
DIAGNOSTICS , такої форми:
GET DIAGNOSTICS variable = item [ , ... ];
Ця команда дозволяє відновлювати(знаходити) індикатори стану системи. Кожен item є ключовим словом що призначає значення статусу до вибраної змінної (яка має бути типу даних спроможного до пересилки). На даний сам доступними пунктами статусу є ROW_COUNT, кількість рядків опрацьованих останньою командою SQL відправлениою до движка SQL, і RESULT_OID, OID(ідентифікатор об’єкта) останнього рядка вставленого останньою командою SQL. Відмітьте що RESULT_OID корисний тільки після INSERT команди в таблицю з OIDs.
Приклад:
GET DIAGNOSTICS integer_var = ROW_COUNT;
Другим методом є перевірка спеціальної змінної FOUND, типуboolean. FOUND за замовчуванням має значення false всередині кожного PL/pgSQL виклику функції. Вона задається наступними типами команд:
• Команда SELECT INTO задає FOUND в true якщо рядок є присвоєним, false якщо нема рядка.
• Команда PERFORM задає FOUND true якщо вона задає(і відміняє) один або більше рядків, false якщо нема рядка.
• UPDATE, INSERT, і DELETE задають FOUND в true якщо повпливали хоча б на 1 рядок, false iякщо ні.
• Команда FETCH задає FOUND в true при поверненні рядка, false – нема рядка поверненого.
• Команда MOVE задає FOUND в true якщо успішно переміщує курсор, false якщо ні.
• FOR задає FOUND в true якщо вона ітерується 1 або більше раз, якщо ні - false. Для всіх 4 типів FOR (цілочисельний FOR, наборо-записувальний FOR, динамічний наборо-записувальний FOR, і курсорний FOR ). FOUND задається якщо FOR існує; всередині виконання циклу, FOUND не модифікується FOR, хоча вона може бути змінена виконанням інших команд в тілі циклу.
•RETURN QUERY і RETURN QUERY EXECUTE задають FOUND в true якщо запит вертає хоч 1 ряд, false - ні.
Інші команди PL/pgSQL не змінюють стану FOUND. Занотуйте що EXECUTE змінює вивід GET DIAGNOSTICS, але не міняє FOUND.
FOUND локальна змінна в функціях PL/pgSQL ; всі зміни у ній впливають лише на дану функцію.
39.5.6. Не Виконання(робіння) зовсім Нічого
Іноді корисними є команди що не роблять нічого. Для прикладу вони можуть ідентифікувати що однин хід if/then/else ланцюжка буде завжди пустий. Для цьої цілі є твердження NULL :
NULL;
Для прикладу наступні два фрагменти еодк є еквівалентними:
BEGIN
y := x / 0;
EXCEPTION
WHEN division_by_zero THEN
NULL; -- ignore the error
END;
BEGIN
y := x / 0;
EXCEPTION
WHEN division_by_zero THEN -- ignore the error
END;
Що ліпше – то вже діло смаку.
Відмітьте: В Oracle’s PL/SQL, списки пустих тверджень не дозволені, тож команда NULL якраз вимагається для таких випадків. PL/pgSQL дозволяє вам не писати нічого наприклад.
39.6. Керуючі структури
Керуючі структури, - ймовірно найкорисніша (і важлива) частина PL/pgSQL.З PL/pgSQL керуючими структурами, ви можете маніпулювати даними PostgreSQL дуже гнучко і потужно.
39.6.1. Повернення З Функції
Є дві команди, доступні, що дозволяють вам повертати дані з функції:, RETURN and RETURN
NEXT.
39.6.1.1. RETURN
RETURN expression;
RETURN з виразом відміняє функцію і повертає значення expression викликачу. Ця форма використовується для функцій PL/pgSQL що не повертають набірr .
При поверненні скалярного типу, будь-який вираз може бути використаний. Вивід виразу буде автоматично конвертовано до типу виводу функції для присвоєнь. Для повертання складного(рядкового)
значення, ви маєте написати змінну запису або рядкову змінну як expression.
Якщо ви оголоси функцію з вихідними параматрами, пишіть тіки RETURN без виразу. Теперішні значення вихідних параметрів змінних будуть повернуті.
Якщо ви оголосили функцію типу void, RETURN може бути використана для передчасного виходу з функції; але не пишіть визар після RETURN.
Повертаєме значення функції не може бути залишене невизначеним. Якщо контрольдосягне верхнього блооку фінкції без попадання в RETURN, вийде помилка часу виконання. Це не відноситься до функцій з вихідними параметрами void. В таких випадках RETURN автоматично виконується якщо досягнуто блок вищого рівня.
39.6.1.2. RETURN NEXT і RETURN QUERY
RETURN NEXT expression;
RETURN QUERY query;
RETURN QUERY EXECUTE command-string [ USING expression [, ... ] ];
Коли функції PL/pgSQL оголошені повертати SETOF sometype, процедура дещо інша. В такому випадку для повертання значень функції використовуйте RETURN NEXT чи RETURN QUERY , а потім фінальну команду RETURN без аргументів що індикують про завершення функції. RETURN NEXT може використовувати і зі скалярними і з складними типами даних; зі складними, буде повернена ціла “таблиця” результатів. RETURN QUERY
Приєднує результат виконання запито до набору результуючого набору функції. RETURN NEXT і RETURN
QUERY можуть бути легко перероблені у звичайні –набароповертаючі функції, в такому випадку їх результати будуть конкатеновані(складені докупи).
RETURN NEXT і RETURN QUERY в діййсно сті не повертають з функції — фони просто додають нуль або кілька рядків до результуючого набору функції. Виконання поті продовжується з наступного рядочка коду функції PL/pgSQL. Успішно виконані RETURN NEXT чи RETURN QUERY спричиняють побудову результуючого набору. Фінальний RETURN, який не має мати аргументів, заствляє контроль вийти з функції (або просто досягнути кінця функції).
RETURN QUERY має варіант RETURN QUERY EXECUTE, що визначає що запит має виконуватись динамічно. Вирази параметрів можуть бути вставлені в обчислений(командний) рядок запиту використовуючи USING, так яамо як і в команді EXECUTE.
Якщо ви оголосили функцію з вихідними параметрами, напишіть просто RETURN NEXT без виразу. При кожному виконанні текучі значення вихідних параметрів змінних будуть збережені для подальшого повернення як рядок результату. Відмітьте що ви маєте позначати вивд функції як SETOF record коли кілька вихідних параметрів, або SETOF sometype коли є тіки одни параметр типу sometype, для створення наборо-повертаючої функції з вихідними параметрами.
Ось приклад використання RETURN NEXT:
CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
INSERT INTO foo VALUES (1, 2, ’three’);
INSERT INTO foo VALUES (4, 5, ’six’);
CREATE OR REPLACE FUNCTION getAllFoo() RETURNS SETOF foo AS
$BODY$
DECLARE
r foo%rowtype;
BEGIN
FOR r IN SELECT * FROM foo
WHERE fooid > 0
LOOP
-- can do some processing here
RETURN NEXT r; -- return current row of SELECT
END LOOP;
RETURN;
END
$BODY$
LANGUAGE ’plpgsql’ ;
SELECT * FROM getallfoo();
Помітьте: Доступна на даний час реалізація RETURN NEXT і RETURN QUERY зберігає весь результуючий набір пере поверненням його з функції як описувалось раніше. Це означає що якщо функція PL/pgSQL видає дуже великий результуючий набр, швидкодія може бути меленькою: дані будуть записуватись на диск що уникнути виснаження пам’яті, але функція вцілому не поверне поки весь набір не буде сгенеровано. Майбутня версія PL/pgSQL може дозволити користувачам визначати для наборо-повертаючих функції задавати цей ліміт. Зараз же, точка на якій починається запис на диск контролюється конфігураційною змінною work_mem . Адміністратори що мають достатню кількість пам’яті для зберігання великах наборів результатів мають збільшити цей параметр.
39.6.2. Умовні вирази
Команди IF і CASE дозволяють вам виконувати альтернативні команди залежно від умови. PL/pgSQL має 3 форми IF:
• IF ... THEN
• IF ... THEN ... ELSE
• IF ... THEN ... ELSIF ... THEN ... ELSE
І дві форми CASE:
• CASE ... WHEN ... THEN ... ELSE ... END CASE
• CASE WHEN ... THEN ... ELSE ... END CASE
39.6.2.1. IF-THEN
IF boolean-expression THEN
statements
END IF;
Команди IF-THEN є спрощеною формою IF. Твердження між THEN і END IF будуть виконуватись якщо умова є true. В іншому випадку вони пропускаються.
Приклад:
IF v_user_id <> 0 THEN
UPDATE users SET email = v_email WHERE user_id = v_user_id;
END IF;
39.6.2.2. IF-THEN-ELSE
IF boolean-expression THEN
statements
ELSE
statements
END IF;
Команди IF-THEN-ELSE добавляють до IF-THEN можливість визначити команди які мають виконуватися якщо умова не є true. (включно з тим коли умова є NULL.)
Приклади:
IF parentid IS NULL OR parentid = ”
THEN
RETURN fullname;
ELSE
RETURN hp_true_filename(parentid) || ’/’ || fullname;
END IF;
IF v_count > 0 THEN
INSERT INTO users_count (count) VALUES (v_count);
RETURN ’t’;
ELSE
RETURN ’f’;
END IF;
39.6.2.3. IF-THEN-ELSIF
IF boolean-expression THEN
statements
[ ELSIF boolean-expression THEN
statements
[ ELSIF boolean-expression THEN
statements
...]]
[ ELSE
statements ]
END IF;
Іноді у вас є більш ніж тільки дві альтернативи. IF-THEN-ELSIF забезпечує зручний метод з перевірки декількох альтернатив по черзі. Умови IF перевіряються підряд до першої true. Потім асоційовані твердження(s) виконуються, після якого контроль переходить до наступного твердження після END IF. (Ніякі наступні умови IF не перевіряються.) Якщо жоден з IF умов є true, то блок (якщо є) ELSE виконується.
Ось - приклад:
IF number = 0 THEN
result := ’zero’;
ELSIF number > 0 THEN
result := ’positive’;
ELSIF number < 0 THEN
result := ’negative’;
ELSE
-- hmm, the only other possibility is that number is null
result := ’NULL’;
END IF;
Ключове слово ELSIF також може виглядати ELSEIF.
Альтернативним шляхом виконання цього є використання вкладених IF-THEN-ELSE, ось так:
IF demo_row.sex = ’m’ THEN
pretty_sex := ’man’;
ELSE
IF demo_row.sex = ’f’ THEN
pretty_sex := ’woman’;
END IF;
END IF;
Хоча, цей метод вимагає написання END IF для кожного IF, тож він буль незручний за ELSIF при великій кількості альтернатив.
39.6.2.4. Простий CASE
CASE search-expression
WHEN expression [, expression [ ... ]] THEN
statements
[ WHEN expression [, expression [ ... ]] THEN
statements
... ]
[ ELSE
statements ]
END CASE;
Проста форма CASE забезпечує умовне виконання базоване на операторах еквівалентності. search-expression виконується (один раз) і порівнюється з кожним expression в умовах WHEN. Якщо співпадіння знаходиться statements виконуються, і потім контроль переходить до наспного за END CASE рядочка коду. (Всі інші умови WHEN не виконуються.) Якщо не знаходиться співпадіння, виконується ELSE statements ; але якщо немає ELSE ,виникає виняток CASE_NOT_FOUND .
Ось простий приклад:
CASE x
WHEN 1, 2 THEN
msg := ’one or two’;
ELSE
msg := ’other value than one or two’;
END CASE;
39.6.2.5. Пошуковий CASE
CASE
WHEN boolean-expression THEN
statements
[ WHEN boolean-expression THEN
statements
... ]
[ ELSE
statements ]
END CASE;
Пошукова форма CASE забезпечує умовне виконання базоване на правдивості булевських(boolean) виразів. boolean-expression кожної умови WHEN виконується в крок, поки одна з них не зводиться до true. Потім відповідні statements виконуються, і контроль переходить до наступної лінійки коду після END CASE. (Всі інші WHEN не виконуються.) Якщо не знаходиться true, ELSE statements виконуються; але якщо немає ELSE, то виникає виняток CASE_NOT_FOUND.
Ось приклад:
CASE
WHEN x BETWEEN 0 AND 10 THEN
msg := ’value is between zero and ten’;
WHEN x BETWEEN 11 AND 20 THEN
msg := ’value is between eleven and twenty’;
END CASE;
Ця форма CASE повністю еквівалентна IF-THEN-ELSIF, за винятком того що звертання на відсутнє ELSE викликає помилку.
39.6.3. Прості цикли
З командами LOOP, EXIT, CONTINUE, WHILE, і FOR ви можете заставити вашу функцію PL/pgSQL
Повторювати серію команд.
39.6.3.1. LOOP
[ <<label>> ]
LOOP
statements
END LOOP [ label ];
LOOP представляє собою безумовний цикл що повторяється поки не завершиться командою EXIT або командою RETURN. Опціональна label може використовуватися EXIT і CONTINUE всередині вкладених циклів для визнчення до якого з вкладених циклів відносяться які команди.
39.6.3.2. EXIT
EXIT [ label ] [ WHEN boolean-expression ];
Якщо не дано label, найвнутрішніший цикл завершується і наступний за END LOOP рядок опрацьовується наступним. Якщо дано label, вона має бути міткою вкладеного цикла цього або якогось іншого рівня або блоку. Потім названий цикл чи блок зупиняється і виконання продовжується з наступного рядочка коду що слідує за відповідним блоку або циклу END ом.
Якщо є WHEN , цикл виходить тіки тоді коли boolean-expression є true. В іншому випадку, контрольпереходить рядку коду після EXIT.
EXIT може бути використовуваний зі всіма типами циклів; він не обмежений використанням безумовними циклами.
При використанні в блоці BEGIN, EXIT віддає контроль наступному рядку після завершення блоку.
Занотуйте що для цього має використовуватися мітка; не помічений EXIT ніколи не використається до блоку
BEGIN .
Приклади:
LOOP
-- some computations
IF count > 0 THEN
EXIT; -- exit loop
END IF;
END LOOP;
LOOP
-- some computations
EXIT WHEN count > 0; -- same result as previous example
END LOOP;
<<ablock>>
BEGIN
-- some computations
IF stocks > 100000 THEN
EXIT ablock; -- causes exit from the BEGIN block
END IF;
-- computations here will be skipped when stocks > 100000
END;
39.6.3.3. CONTINUE
CONTINUE [ label ] [ WHEN boolean-expression ];
Якщо немає мітки label, неступна ітерація найбільш внутрішнього циклу починається. Отже всі команди згадувані в тілі циклу пропускаються, і контроль повертається до контролюючого виразу циклу (якщо присутній) для визначення чи інша ітерація циклу потрібна. Якщо label присутня, вона визначає який з вкладених циклів буде продовжений.
Коли є WHEN, наступна ітерація починається коли boolean-expression є true. В іншому випадку, контроль віддається команді після CONTINUE.
CONTINUE може бути використане з усіма типами циклів; він не обмежений використанням безумовними циклами.
Приклади:
LOOP
-- some computations
EXIT WHEN count > 100;
CONTINUE WHEN count < 50;
-- some computations for count IN [50 .. 100]
END LOOP;
39.6.3.4. WHILE
[ <<label>> ]
WHILE boolean-expression LOOP
statements
END LOOP [ label ];
Команда WHILE повторює тіло циклу поки boolean-expression є true. Вираз перевіряється перед кожним входом в тіло циклу.
Для прикладу:
WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
-- some computations here
END LOOP;
WHILE NOT done LOOP
-- some computations here
END LOOP;
39.6.3.5. FOR (цілочисельний варіант)
[ <<label>> ]
FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOP
statements
END LOOP [ label ];
Ця форма FOR створює цикл що ітерує через діапазон цілочисельних(integer) значень. Ім’я змінної name автоматично визначається типу integer і існує тільки в циклі (будь яке існуюче таке ім’я поза циклом ігнорується ним). Два вирази позволяють зменшувати або збільшувати границю діапазону що ітерується при вході в цикл . Якщо умова BY не визначена то кроком ітерації є 1, в іншому випадку цяе крок задається умовою BY, що опрацьовується при вході в цикл.
Якщо REVERSE визначена то крок ітерації віднімається, а не додається, після кожної ітерації.
Деякі приклади FOR циклів:
FOR i IN 1..10 LOOP
-- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop
END LOOP;
FOR i IN REVERSE 10..1 LOOP
-- i will take on the values 10,9,8,7,6,5,4,3,2,1 within the loop
END LOOP;
FOR i IN REVERSE 10..1 BY 2 LOOP
-- i will take on the values 10,8,6,4,2 within the loop
END LOOP;
Якщо нижня границя більша за верхню (або менша, у випадку REVERSE ), ціло циклу не виконується взагалі. Без видавання помилок.
Якщо label прикріплена до FOR то на ітераційну змінну можна посилатися використовуючи її ім’я кваліфіковане з даною label.
39.6.4. Ітерація результатів запитів
Використовуючи різні види FOR, ви можете ітерувати дані запитів і маніпулювати цими даними відповідно. Синтаксис:
[ <<label>> ]
FOR target IN query LOOP
statements
END LOOP [ label ];
target є змінною запису, рядковою змінною, або розділеним комами списком скалярних змінних. target успішно призначає кожний результуючий рядок з query і тіло циклу виконується для кожного рядка. Ось приклад:
CREATE FUNCTION cs_refresh_mviews() RETURNS integer AS $$
DECLARE
mviews RECORD;
BEGIN
PERFORM cs_log(’Refreshing materialized views...’);
FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP
-- Now "mviews" has one record from cs_materialized_views
PERFORM cs_log(’Refreshing materialized view ’
|| quote_ident(mviews.mv_name) || ’ ...’);
EXECUTE ’TRUNCATE TABLE ’ || quote_ident(mviews.mv_name);
EXECUTE ’INSERT INTO ’
|| quote_ident(mviews.mv_name) || ’ ’
|| mviews.mv_query;
END LOOP;
PERFORM cs_log(’Done refreshing materialized views.’);
RETURN 1;
END;
$$ LANGUAGE plpgsql;
Цикл завершений командою EXIT, останнє значення призначеного рядка доступне і після циклу.
query використаний в цьому типі FOR може бути будь якою SQL командою що повертає рядки викрикачу: SELECT найбільш загально використовувана, але ви можете також користуватись INSERT, UPDATE, чи DELETE з умовою RETURNING . Деякі утилітні команди типу EXPLAIN також будуть працювати.
PL/pgSQL змінні є заміщеними в тексті запиту, і план запиту кешований для можливого перевикористання, як обговорюється в секціях 39.10.1 і 39.10.2.
Команда FOR-IN-EXECUTE є іншим варіантом для ітерації рядків:
[ <<label>> ]
FOR target IN EXECUTE text_expression [ USING expression [, ... ] ] LOOP
statements
END LOOP [ label ];
Ця як і попередня форма, приймає що запит-джерело визначений як рядочковий вираз, що виконується і переплановується при кожному вході в цикл FOR . Це дозволяє програмісту вибрати швидкість наперед запланованого запиту або гнучкість динамічного запиту, так як і з EXECUTE. Та як і з EXECUTE, параметричні значення можуть бути вставлені в динамічну команду за допомогою USING.
Іншим методом такого є курсор.
Це описоне в секції 39.7.4.
39.6.5. Відлов помилок
За замовчуванням, будь-яка помилка що виникає в PL/pgSQL функції відміняє виконання функції, як і її транзакції. Ви можете ловити помилки і відновлюватися з них за допомогою блоку BEGIN з умовою EXCEPTION . Синтаксисом є розширений синтаксис блоку BEGIN:
[ <<label>> ]
[ DECLARE
declarations ]
BEGIN
statements
EXCEPTION
WHEN condition [ OR condition ... ] THEN
handler_statements
[ WHEN condition [ OR condition ... ] THEN
handler_statements
... ]
END;
Якщо не виникає помилка, ця форма блоку просто виконує всі statements, і потім контроль переходить наступній команді після END. Але якщо помилка виникає всередині statements, подальше виконання statements зупиняється і контроль передається списку EXCEPTION . Список обшукується на першу condition що підходить під помилку що виникла. Якщо співпадіння знайдене, виконується handler_statements , і потім контроль переходить до наступної команди після END. Якщо співпадіння немає, помилка пропагується через умову EXCEPTION де її немає: помилка пможе опрацюватися зовнішнім блоком EXCEPTION, або якщо таого немає то робота функції припиняється.
Імена condition можуть бути тіки такими як у Додатку A. Ім’я категорії підпадає під будь-яку помилку цієї категорії. Спеціальне ім’я умови OTHERS підпадає під будь-яку помилку крім QUERY_CANCELED. (уе можливо але не завжди розумно лапати помилку QUERY_CANCELED за іменем.) Імена умов не чутливі до регістру. Також умова помилки може бути визначена кодом SQLSTATE ; для прикладу дані рядки є еквівалентними:
WHEN division_by_zero THEN ...
WHEN SQLSTATE ’22012’ THEN ...
Якщо виникає нова помилка в вибраному handler_statements, вона не можу бути спіймана цим блоком EXCEPTION і пона пропагується на ззовні. Зовнішня умова EXCEPTION може спіймати її. Коли помилка спіймана умовою EXCEPTION локальні змінні функції PL/pgSQL залишають такі значення які мали при виникненні помилки, але всі зміни до бази даних в блоці будуть відмінені. Ось приклад:
INSERT INTO mytab(firstname, lastname) VALUES(’Tom’, ’Jones’);
BEGIN
UPDATE mytab SET firstname = ’Joe’ WHERE lastname = ’Jones’;
x := x + 1;
y := x / 0;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE ’caught division_by_zero’;
RETURN x;
END;
Коли контроль досягає присвоєння y, виникне помилка division_by_zero . Але вона буде спіймана умовою EXCEPTION . Значення поверненне в RETURN буде інкрементованим значенням x, але ефекти команди UPDATE будуть відмінені. Команда INSERT що передує блоку не буде відмінена, тож тож кінцевим результатом в базі даних є Tom Jones а не Joe Jones.
Tip: Блок з умовою EXCEPTION значно дорожчий для входу і виходу ніж без неї. Тож не користуйтесь EXCEPTION без потреби. Всередині опрацьовувача винятків, змінна SQLSTATE містить коди помилки що відповідає за виняток що стався (в таблиці Table A-1 список можливих кодів помилок). Змінна SQLERRM містить повідомлення помилки асоційоване з винятком. Ці змінні невизначені поза опрацьовувачем винятків.
Приклад 39-2. Помилки з UPDATE/INSERT
Цей приклад використовує опрацьовувач винятків для виконання UPDATE абоINSERT відповідно:
CREATE TABLE db (a INT PRIMARY KEY, b TEXT);
CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
LOOP
-- first try to update the key
UPDATE db SET b = data WHERE a = key;
IF found THEN
RETURN;
END IF;
-- not there, so try to insert the key
-- if someone else inserts the same key concurrently,
-- we could get a unique-key failure
BEGIN
INSERT INTO db(a,b) VALUES (key, data);
RETURN;
EXCEPTION WHEN unique_violation THEN
-- do nothing, and loop to try the UPDATE again
END;
END LOOP;
END;
$$
LANGUAGE plpgsql;
SELECT merge_db(1, ’david’);
SELECT merge_db(1, ’dennis’);
39.7. Курсори
Замість виконання цілого запиту відразу, можливо встановити курсор, який формує запит, а потім читає результат запиту в декілька рядів за один раз. Одна причина для виконання цього – уникати переповнювання пам'яті, коли результат містить великий масив рядів. (Проте PL/pgSQL користувачам не треба хвилюватися про це,оскільки, FOR цикли автоматично користуються курсором для уникнення проблем пам'яті.) Більш цікаве використання - повернути посилання курсору, який що створила функція, дозволяючи визивачу читати ряди. Це забезпечує ефективний шлях повернути великий набір рядів від функцій.
39.7.1. Оголошення Змінних Курсора
39.7.1. Оголошення змінних курсору
Весь доступ до крусору в PL/pgSQL здійснюється через курсорні змінні, які завжди є спеціального типу refcursor. Одним з методів створення курсорної змінної – це просто оголошення змінної типу refcursor. Інший шлях – це використання синтаксису декларації курсора, який в загальному виглядає так:
name [ [ NO ] SCROLL ] CURSOR [ ( arguments ) ] FOR query;
(FOR можу бути замінен IS для сумісності з Oracle .) Якщо визнечений SCROLL , курсор буде спроможний прокручуватись назад; Якщо визначений NO SCROLL , прокрутки назад не буде; якщо немає цих змінних то це буде залежати від запиту буде прокрутка чині. arguments, якщо є, розділений комами список пар name datatype що визначаєть імена що будуть замінені значеннями параметрів в даному запиті. Справжні імена для цих значеьн будуть визначені пізніше, при відкритті курсору.
Деякі приклади:
DECLARE
curs1 refcursor;
curs2 CURSOR FOR SELECT * FROM tenk1;
curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;
Всі ці три змінні мають тип refcursor, але перша можу використовуватися в будь- якому запиті, коли друга має повністю визначені границі запитів для неї, а остання має параметризовані границі запитів для неї. (key буде замінений цілочисельним значенням при відкритті курсору.)
Змінна curs1 є необмеженою оскільки не відноситься що жодного конкрутного запиту.
39.7.2. Відкриття курсорів
Перед використанням курсору для повернення рядків, він має бути відкритий. (Це еквівалентна функція до SQL команди DECLARE CURSOR.) PL/pgSQL має три форми команди OPEN , дві з яких використовують необмежені курсорні змінні а третій обмежена.
Note: Обмежені курсорні змінні також можуть використовуватися без явного відкриття курсору, з допомогою FOR що описано в Секції 39.7.4.
39.7.2.1. OPEN FOR query
OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR query;
Курсорна змінна відкрита і надана специфічному запиту для виконання. Курсор ще не може бути відкритий, для йього він має бути оголошений необмеженою курсорною змінною (що є простою змінною refcursor). Запитом має бути SELECT, або щось що повертає рядки (типуEXPLAIN). Запит опрацьовується як звичанй команди SQL в PL/pgSQL: PL/pgSQL змінні замінюються і план є знайдений для можливого перевикористання. Коли змінна PL/pgSQL замінюється в курсорному запиту, значення змінної таке як при OPEN; інші зміни дпної змінної не будуть впливати на поведінку курсору. Опції SCROLL і NO SCROLL мають такі самі значення як для обмеженого курсору.
Приклад:
OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
39.7.2.2. OPEN FOR EXECUTE
OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR EXECUTE query_string
[ USING expression [, ... ] ];
Курсорна змінна відкрита і надана специфічному запиту для виконання. Курсор ще не може бути відкритий, для йього він має бути оголошений необмеженою курсорною змінною (що є простою змінною refcursor). Запит заданий як рядковий вираз так як і в EXECUTE. Це дає гнучкість – тож план міяється від запуску до запуску (Section
39.10.2), це також значить що заміна змінних не робитьсяв командному рядку. Як і з EXECUTE, значення параметрів можуть бути вставлені використовуючи USING. Опції SCROLL і NO SCROLL мають такі самі значення як для обмеженого курсору.
Приклад:
OPEN curs1 FOR EXECUTE ’SELECT * FROM ’ || quote_ident(tabname)
|| ’ WHERE col1 = $1’ USING keyvalue;
В цьому прикладі, ім’я таблиці вставлене в запит текстуально, тож використання quote_ident() рекомендоване для уникнення ін’єкцій SQL. Значення для порівнянн для col1 вставлене USING тож воно не потребує лапкування
39.7.2.3. Відкривання обмеженого курсору
OPEN bound_cursorvar [ ( argument_values ) ];
Ця форма OPEN використовується для курсорних зміннх що були зв’язані з запитом під час його створення.
Курсор не можу бети все відкритий. Список дійсних значень аргументів має бути визначений тільки тоді коли курсор може приймати ці аргументи. Ці значення будуть замінені у запиті. План запиту для обмеженого курсою завжди кешабельний(зберігається); тут немає еквіваленту EXECUTE. Помітьте що SCROLL і NO SCROLL не можуть бути визначені оскільки поведінка прокрутки вже визначена.
Відмітьте що оскільки заміна змінних відбулась на обмеженому курсором запиті, є два шляхи подати значення на курсор: явним аргуметом OPEN, або неявно посилаючись на PL/pgSQL змінну у запиті. Хоча тільки змінні оголошені у створенні обмеженого курсору будуть замінятися в ньому. В обох випадках значення що має бути передане визначається на етапі OPEN.
Приклади:
OPEN curs2;
OPEN curs3(42);
39.7.3. Використання курсорів
Коли курсор відкритий, ним можна керувати командами описаними тут.
Ці маніпуляції не обов’язково мають бути в одній функції з тою що відкрила курсор. Ви можете повернути значення refcursor з функції і дозволити викликачу опрацювати курсор. (Взагальному, значення refcursor є просто рядком імені так названого порталу що містить активний запит для курсору. Це ім’я можу бути пропущене, присвоєне іншій змінній refcursor , і т.д., без турбування порталу.)
Всі портали неявно закриваются при завершенні транзакції. Тож значення refcursor можливе для посилань на нього тільки під час працюючої транзакції.
39.7.3.1. FETCH
FETCH [ direction { FROM | IN } ] cursor INTO target;
FETCH повертає наступний рядкод з курсору до цілі, що може ьбути рядковою змінною, зімінною запису, або списком простих змінних розділених комами, тк як в SELECT INTO. Якщо немає найтпного рядку, ціль стає NULL(s). Як і з SELECT INTO, спеціальна змінна FOUND може бути перевірена чи рядки були отримані чи ні.
Умова direction можу бути будь чим що дозволено SQL FETCH командою крім тиго що можу повертати більш ніж 1 рядок; namely, це може бути NEXT, PRIOR, FIRST, LAST, ABSOLUTE count, RELATIVE count, FORWARD, чи BACKWARD. Отримання direction таке як і визначення NEXT. direction значення що вимагають руху назад проваляться якщо не буде визначено опцію SCROLL.
cursor має бути ім’ям refcursor змінною що посилається відкритим порталом курсору.
Приклади:
FETCH curs1 INTO rowvar;
FETCH curs2 INTO foo, bar, baz;
FETCH LAST FROM curs3 INTO x, y;
FETCH RELATIVE -2 FROM curs4 INTO x;
39.7.3.2. MOVE
MOVE [ direction { FROM | IN } ] cursor;
MOVE переміщує курсор без поверення даних. MOVE працює так само як FETCH , за винятком того що він тіки переміщує курсор і не повертає рядків. Як і з SELECT INTO, спеціальну змінну FOUND можна перевірити чи був наступний рядок для переміщення.
Умова direction можу бути будь чим що дозволене в SQL FETCH , типу NEXT, PRIOR, FIRST, LAST, ABSOLUTE count, RELATIVE count, ALL, FORWARD [ count | ALL ], чи BACKWARD [ count | ALL ]. Отримання direction таке як і визначення NEXT. direction значення що вимагають руху назад проваляться якщо не буде визначено опцію SCROLL.
Приклади:
MOVE curs1;
MOVE LAST FROM curs3;
MOVE RELATIVE -2 FROM curs4;
MOVE FORWARD 2 FROM curs4;
39.7.3.3. UPDATE/DELETE WHERE CURRENT OF
UPDATE table SET ... WHERE CURRENT OF cursor;
DELETE FROM table WHERE CURRENT OF cursor;
Коли курсор розміщений на табличному рядку, цей рядок може бути модифікований або видалений використовуючи курсор для ідентифікації рядку. Є обмеження того яким може бути запит курсору (взагальному, не груванням) і найкраще використовувати FOR UPDATE в курсорі. Для більшої інформації дивіться сторінку DECLARE.
Приклад:
UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;
39.7.3.4. CLOSE
CLOSE cursor;
CLOSE закриває портал що тримає відкритий курсор. Це може бути використане для вивільнення ресурсів до завершення транзакції, або для виввільнення змінної курсору для нового відкриття.
Приклад:
CLOSE curs1;
39.7.3.5. Повернення курсорів
PL/pgSQL функції можуть повертати курсори визивачам. Це корисно для повернення кількох рядків чи стовпців, особливо з дуже веливким результуючими наборами. Для цього, функція відкриває курсор і повертає його ім’я визивачу (або просто відкриває курсор використовуючи ім’я визначене чи якимось чином відоме визивачу). Визивач можу потім діставати рядки з курсору. Курсор можу бути закритий визивачем чи закриється автоматично при завершенні транзакції.
Ім’я порталу для курсору може бути визначене програмістом або згенероване автоматично. Для задання ім’я просто присвойте рядочок змінній refcursor перед відкриттям. Рядкове значення refcursor буде використане OPEN як ім’я тримаючого порталу. Хоча якщо змінна refcursor є нулем, OPEN автоматично генерує ім’я шо не конфліктує з усіма існуючими іменами порталів, і присвоює його змінній refcursor .
Помітьте: Обмежена курсорна змінна ініціалізована з рядковим значенням що представляє її ім’я, тож ім’я порталу таке саме як і змінної курсору, якщо програміст, звичайно, не перевизначив його перед відкриттям курсору. Але необмежена змінна курсору призначається з нульовим іменем, тож вона отримає автоматично згенероване унікальне ім’я, якщо не буду перевизначена.
Наступний приклад показує один з шляхів яким ім’я курсору може бути отримане визивачем:
CREATE TABLE test (col text);
INSERT INTO test VALUES (’123’);
CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS ’
BEGIN
OPEN $1 FOR SELECT col FROM test;
RETURN $1;
END;
’ LANGUAGE plpgsql;
BEGIN;
SELECT reffunc(’funccursor’);
FETCH ALL IN funccursor;
COMMIT;
Наступний приклад використовує автоматичну генерацію імені курсору :
CREATE FUNCTION reffunc2() RETURNS refcursor AS ’
DECLARE
ref refcursor;
BEGIN
OPEN ref FOR SELECT col FROM test;
RETURN ref;
END;
’ LANGUAGE plpgsql;
-- need to be in a transaction to use cursors.
BEGIN;
SELECT reffunc2();
reffunc2
--------------------
<unnamed cursor 1>
(1 row)
FETCH ALL IN "<unnamed cursor 1>";
COMMIT;
Наступний приклад показує один з шляхів повернення багатьох курсорів з однієї функції:
CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$
BEGIN
OPEN $1 FOR SELECT * FROM table_1;
RETURN NEXT $1;
OPEN $2 FOR SELECT * FROM table_2;
RETURN NEXT $2;
END;
$$ LANGUAGE plpgsql;
-- need to be in a transaction to use cursors.
BEGIN;
SELECT * FROM myfunc(’a’, ’b’);
FETCH ALL FROM a;
FETCH ALL FROM b;
COMMIT;
39.7.4. Ітерація результатів курсору
Є варіант команди FOR що дозволяє ітерувати рядки повернені курсором.
Синтаксис:
[ <<label>> ]
FOR recordvar IN bound_cursorvar [ ( argument_values ) ] LOOP
Statements
END LOOP [ label ];
Змінна курсору має бути призначена до якого запиту під час оголошення, і вона не може бути зразу відкритою. Команда FOR автоматично відкриває курсор, і закриває його при виході з циклу. Список аргументів має бути огголошений якщо курсор може приймати аргументи. Ці значення будуть замінені в запиті, та як і в OPEN. Змінна recordvar автоматично визначається типом record і існує тільки в циклі (всі дублюючі імена поза циклом ігноруюються). Як тільки кожний рядок курсору присвоюється змінній запису, виконується тіло циклу.
39.8. Помилки і повідомлення
Використовуйте команду RAISE для оповіщення повідомлень і помилок.
RAISE [ level ] ’format’ [, expression [, ... ]] [ USING option = expression [, ... ] ];
RAISE [ level ] condition_name [ USING option = expression [, ... ] ];
RAISE [ level ] SQLSTATE ’sqlstate’ [ USING option = expression [, ... ] ];
RAISE [ level ] USING option = expression [, ... ];
RAISE ;
Опція level визначає безпечність від помилок. Дозволеними рівнями є DEBUG, LOG, INFO, NOTICE,
WARNING, і EXCEPTION, з EXCEPTION що є зазамовчуванням. EXCEPTION виклиає помилку (що зазвичай завершеє транзакцію); інші просто генерують повідомлення різних пріоритетних рівнів. Незалежно чи повідомлення деякого пріоритету звітоване клієнту, чи записано в серверний лог, чи і то і друге вони всі керюються конфігураційниим змінними the log_min_messages і client_min_messages. Секція 18 для інформації.
Після level якщо він присутній, ви можете записати формат format (Що має бути простим рядковим літералом, а не виразом). Рядок формату задає який таекст буде в повідомленні. Рядок формату може слідувати за опціональним аргументом виразом що має бути вставдлений в повідомлення. В рядку формату, % замінюється рядковим представленням наступного значення аргументу. Напишіть %% для задання літералу %.
В цьому прикладі, значення v_job_id замінить % в рядку:
RAISE NOTICE ’Calling cs_create_job(%)’, v_job_id;
Ви можете додати додаткову інформацію до звіту помилки написанням USING що слідує за option =
expression. Дозволені ключові слова option є MESSAGE, DETAIL, HINT, і ERRCODE, поки кожне expression можу бути будь-яким рядковим виразом. MESSAGE задає текст повідомлення помилки (ця опція може використовуватись у формі RAISE що включає рядок формату перед USING). DETAIL включає деталі помилки до повідомлення, а HINT включає повідомлення підказку. ERRCODE задає код помилки (SQLSTATE), одної з причин Додатку A, або безпосередньо п’ятицифровий код SQLSTATE.
Цей приклад відмінить транзакцію з заданим повідомленням помилки і підказкою :
RAISE EXCEPTION ’Nonexistent ID --> %’, user_id
USING HINT = ’Please check your user id’;
Ці два повідомлення показують два еквівалентні шляхи задання SQLSTATE:
RAISE ’Duplicate user ID: %’, user_id USING ERRCODE = ’unique_violation’;
RAISE ’Duplicate user ID: %’, user_id USING ERRCODE = ’23505’;
Є другий синтаксис RAISE в якому головним аргументом є ім’я умови або SQLSTATE для прикладу:
RAISE division_by_zero;
RAISE SQLSTATE ’22012’;
В цьому синтаксисі, USING дозволяє ввімкнути користувацьке повідомлення про помилку, деталі, чи підказку. Іншим шляхом виконання ранішого прикладу є
RAISE unique_violation USING MESSAGE = ’Duplicate user ID: ’ || user_id;
Іншим варіантом є написання RAISE USING чи RAISE level USING і покласти все інше в список USING.
Останній параметр RAISE немає параметрів. Ця форма може використовуватися тільки в умові EXCEPTION блоку BEGIN; вона спричиняє помилку що виникла перекинути до іншого блоку опрацювання.
Якщо немає ім’я умови чи SQLSTATE не визначена в RAISE EXCEPTION , зазвичай використовують RAISE_EXCEPTION (P0001). Якщо немає тексту повідомлення, зазвичай використовують ім’я умови або SQLSTATE як текс повідомлення.
Note: При заданні коду помилки SQLSTATE , ви не обмеженні в вбудованих кодах помилок, але можете вибрати будь який код помилки що містить п’ять цифр і/або букв ASCII верхнього регістру, відмінних від 00000. Не рекомендовано використовувати коди помилок що завершеюються трьома нулями, тому що це категорія помилок що лапаються тільки лапанням всієї категорії.
39.9. Тригерні процедури
PL/pgSQL може оголошувати тригерні процедури. Тригерна процедура строюється CREATE
FUNCTION командою, оголошуйте її як безаргументну функцію з типом повернення trigger. Відмітьте що функція має оголошуватись як безаргументна навіть якщо вона передбачає прийняття аргументів визначених у CREATE TRIGGER— тригерний аргумент передається TG_ARGV, як описано нижче.
Коли функція PL/pgSQL визначена як тригер, декілька спеціальних змінних стврюється автоматично в ьлоці верхнього рівня. Вони є:
NEW
Тип даних RECORD; містить нові рядки бази даних для операцій INSERT/UPDATE в тригерах рівня рядків. Ця змінна є NULL в тригерах командного рівня і для операцій DELETE.
OLD
Тип даних RECORD; містить старі рядки бази даних для операції UPDATE/DELETE в тригерах рівня рядків. Ця змінна є NULL в тригерах командного рівня і для операцій INSERT.
TG_NAME
Тип даних name; містить ім’я тригера що зараз виконується.
TG_WHEN
Тип даних text; рядок BEFORE або AFTER в залежності від визначення тригера.
TG_LEVEL
Тип даних text; рядок ROW або STATEMENT в залежності від визначення тригера.
TG_OP
Тип даних text; рядок INSERT, UPDATE, DELETE, або TRUNCATE каже для якої операції запущений тригер.
TG_RELID
Тип даних oid; об’єктне ID таблиці що спричиняє пробудження тригеру.
TG_RELNAME
Тип даних name; ім’я таблиці що спричиняє пробудження тригеру. Вона поки що не видалена,
Але може зникнути в наступних версіях. Використовуйте TG_TABLE_NAME замість неї .
TG_TABLE_NAME
Тип даних name; ім’я таблиці що спричиняє пробудження тригеру.
TG_TABLE_SCHEMA
Тип даних name; ім’я схеми таблиці що спричиняє пробудження тригеру.
TG_NARGS
Тип даних integer; кількість аргументів даних тригерній процедурі командою CREATE
TRIGGER.
TG_ARGV[]
Тип даних масив text; аргументи з команди CREATE TRIGGER . Нумерація з 0. Неправильні індекси (менші 0 або більші tg_nargs) чезультують в нульове значення.
Тригерна функція має повертати або NULL або запису/рядкове значення точно такої самої структури як у таблиці для якої працює тригерна процедура.
Тригери рівня рядків запалених BEFORE можуть повертати 0 щоб сказати менеджеру тригерів пропустити решту операцій для цього рядка (i.e., всі подальші тригери не запускаються, і INSERT/UPDATE/DELETE не появляються для даного рядка). Якщо значення не є нульовим то операції для даного рядка продовжуються. Повернення рядкового значення відмінного від оригінального значення NEW сигналізує що рядок був вставлений або модифікований. Тож уоли функція хоче щоб тригерна процедура виконалась нормально без заміни значення рядка, NEW (або його значення) має повертатись. Щоб запобігти збереженню можливим є замінити одиничні значення безпосередньо в NEW і повернути модифікований NEW, або побудувати абсолютно новий запис\рядок для повернення. У випадку тригера на DELETE, повернене значення немає безпосередніх впливів, але воно немає бути нульовим щоб тригер виконався. Відмітьте щоt NEW є нулем в DELETE тригерах, тож повернення його немає змісту. А корисною ідіомою з DELETE тригерів що може бути повернена є OLD.
Значення поверення на тригерах рядкового рівня запалених AFTER або тригерах рівня команд запалених BEFORE або AFTER завжди ігнорується; воно можу бути навіть 0. Хоча, будь –який з цих типів тригерів може зіпсувати всю операцію викликом помилки.
Приклад 39-3 приклад тригерної процедури в PL/pgSQL.
Приклад 39-3. тригерної процедури в PL/pgSQL.
Цей приклад тригеру переконується що кожного разу при вставці чи модифікації рядка в таблиці час і ім’я штампуються в рядок. І він перевіряє чи ім’я працівника задане і чи його оклад є позитивним значенням.
CREATE TABLE emp (
empname text,
salary integer,
last_date timestamp,
last_user text
);
CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
BEGIN
-- Check that empname and salary are given
IF NEW.empname IS NULL THEN
RAISE EXCEPTION ’empname cannot be null’;
END IF;
IF NEW.salary IS NULL THEN
RAISE EXCEPTION ’% cannot have null salary’, NEW.empname;
END IF;
-- Who works for us when she must pay for it?
IF NEW.salary < 0 THEN
RAISE EXCEPTION ’% cannot have a negative salary’, NEW.empname;
END IF;
-- Remember who changed the payroll when
NEW.last_date := current_timestamp;
NEW.last_user := current_user;
RETURN NEW;
END;
$emp_stamp$ LANGUAGE plpgsql;
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
Іншим методом для створення логу для таблиці є створення іншої таблиці що містить рядок для кожної вскавки,
Модифікації або видалення. Цей метод може бути застосований як і аудит змін в таблиці. Приклад
39-4 показує приклад тригерної процедури для аудиту PL/pgSQL.
Приклад 39-4. A PL/pgSQL тригерної процедури для аудиту
Цей приклад тригеру переконується що будь-яка модифікація, вставка або видалення рядка в таблиці emp записується(аудитується) в таблиці emp_audit . Поточні час і користувач штампуються в рядок поряд з типом операції.
CREATE TABLE emp (
empname text NOT NULL,
salary integer
);
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
stamp timestamp NOT NULL,
userid text NOT NULL,
empname text NOT NULL,
salary integer
);
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
BEGIN
--
-- Create a row in emp_audit to reflect the operation performed on emp,
-- make use of the special variable TG_OP to work out the operation.
--
IF (TG_OP = ’DELETE’) THEN
INSERT INTO emp_audit SELECT ’D’, now(), user, OLD.*;
RETURN OLD;
ELSIF (TG_OP = ’UPDATE’) THEN
INSERT INTO emp_audit SELECT ’U’, now(), user, NEW.*;
RETURN NEW;
ELSIF (TG_OP = ’INSERT’) THEN
INSERT INTO emp_audit SELECT ’I’, now(), user, NEW.*;
RETURN NEW;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$emp_audit$ LANGUAGE plpgsql;
CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();
Одне з використань тригерів – ци підтримувати сумуючу таблицю іншої таблиці. Результуюча сумарна таблиця може бути використана замість оригінальної таблиці бля певних запитів — часто неймовірно зменшуючи час виконання. Ця техніка часно використовується в Data Warehousing(Складуванні Даних)(від слова «склад»), коли таблиці виміряних даних або даних спостереження(фактові таблиці) можуть мати надзвичайно великі розміри. Приклад 39-5 є прикладом тригерної процедури PL/pgSQL що підтримує сумарну таблицю для фактових таблиць у складуванні даних.
Приклад 39-5. A PL/pgSQL Тригерна процедура для підтримування сумарної таблиці
Сце ма деталізована туту є часково базованою на прикладі Grocery Store(Бакалійного Складу) прикладу The Data Warehouse Toolkit(Чемоданчика майстра для вкладування даних) Ralph Kimball(Ральфа Кімбалла).
--
-- Main tables - time dimension and sales fact.
--
CREATE TABLE time_dimension (
time_key integer NOT NULL,
day_of_week integer NOT NULL,
day_of_month integer NOT NULL,
month integer NOT NULL,
quarter integer NOT NULL,
year integer NOT NULL
);
CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);
CREATE TABLE sales_fact (
time_key integer NOT NULL,
product_key integer NOT NULL,
store_key integer NOT NULL,
amount_sold numeric(12,2) NOT NULL,
units_sold integer NOT NULL,
amount_cost numeric(12,2) NOT NULL
);
CREATE INDEX sales_fact_time ON sales_fact(time_key);
--
-- Summary table - sales by time.
--
CREATE TABLE sales_summary_bytime (
time_key integer NOT NULL,
amount_sold numeric(15,2) NOT NULL,
units_sold numeric(12) NOT NULL,
amount_cost numeric(15,2) NOT NULL
);
CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);
--
-- Function and trigger to amend summarized column(s) on UPDATE, INSERT, DELETE.
--
CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER
AS $maint_sales_summary_bytime$
DECLARE
delta_time_key integer;
delta_amount_sold numeric(15,2);
delta_units_sold numeric(12);
delta_amount_cost numeric(15,2);
BEGIN
-- Work out the increment/decrement amount(s).
IF (TG_OP = ’DELETE’) THEN
delta_time_key = OLD.time_key;
delta_amount_sold = -1 * OLD.amount_sold;
delta_units_sold = -1 * OLD.units_sold;
delta_amount_cost = -1 * OLD.amount_cost;
ELSIF (TG_OP = ’UPDATE’) THEN
-- forbid updates that change the time_key -
-- (probably not too onerous, as DELETE + INSERT is how most
-- changes will be made).
IF ( OLD.time_key != NEW.time_key) THEN
RAISE EXCEPTION ’Update of time_key : % -> % not allowed’,
OLD.time_key, NEW.time_key;
END IF;
delta_time_key = OLD.time_key;
delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
delta_units_sold = NEW.units_sold - OLD.units_sold;
delta_amount_cost = NEW.amount_cost - OLD.amount_cost;
ELSIF (TG_OP = ’INSERT’) THEN
delta_time_key = NEW.time_key;
delta_amount_sold = NEW.amount_sold;
delta_units_sold = NEW.units_sold;
delta_amount_cost = NEW.amount_cost;
END IF;
-- Insert or update the summary row with the new values.
<<insert_update>>
LOOP
UPDATE sales_summary_bytime
SET amount_sold = amount_sold + delta_amount_sold,
units_sold = units_sold + delta_units_sold,
amount_cost = amount_cost + delta_amount_cost
WHERE time_key = delta_time_key;
EXIT insert_update WHEN found;
BEGIN
INSERT INTO sales_summary_bytime (
time_key,
amount_sold,
units_sold,
amount_cost)
VALUES (
delta_time_key,
delta_amount_sold,
delta_units_sold,
delta_amount_cost
);
EXIT insert_update;
EXCEPTION
WHEN UNIQUE_VIOLATION THEN
-- do nothing
END;
END LOOP insert_update;
RETURN NULL;
END;
$maint_sales_summary_bytime$ LANGUAGE plpgsql;
CREATE TRIGGER maint_sales_summary_bytime
AFTER INSERT OR UPDATE OR DELETE ON sales_fact
FOR EACH ROW EXECUTE PROCEDURE maint_sales_summary_bytime();
INSERT INTO sales_fact VALUES(1,1,1,10,3,15);
INSERT INTO sales_fact VALUES(1,2,1,20,5,35);
INSERT INTO sales_fact VALUES(2,2,1,40,15,135);
INSERT INTO sales_fact VALUES(2,3,1,10,1,13);
SELECT * FROM sales_summary_bytime;
DELETE FROM sales_fact WHERE product_key = 1;
SELECT * FROM sales_summary_bytime;
UPDATE sales_fact SET units_sold = units_sold * 2;
SELECT * FROM sales_summary_bytime;
39.10. PL/pgSQL Під Ковпаком(КОрою)
Ця секція обговорює деякі деталі реалізації, які часто важливі для знання для користувачів PL/pgSQL.
39.10.1. Заміна Змінних
SQL команди і вирази PL/pgSQL функції можуть посилатися на змінні і параметри функції. Поза сценою, PL/pgSQL замінює змінні запиту для таких посилань. Тільки ті параметри будуть замінені де це синтаксично дозволено. Як екстемальний приклад візьмемо, цей приклад бідного стилю програмування:
INSERT INTO foo (foo) VALUES (foo);
Перша поява foo має синтаксично бути іменем таблиці, тож вона не буде замінена, навіть якщо функція має змінну foo. Наступною появою має бути ім’я стовпця таблиці тож воно також не буде замінене. Тільки третя поява може бути кандидатом для посилання на змінну функції.
Відмітьте: PostgreSQL версії до 9.0 захочуть замінити всі три змінні у всіх випадках, що призведе до синтаксичних помилок.
Оскільки ім’я змінних синтаксично не відрізняються від імен стовпсів таблиці, може бути невизначеність в командах що також звертаються до таблиць: дане ім’я відноситься до стовпця таблиці чи до змінної? Давайте змінимо попередній приклад так:
INSERT INTO dest (col) SELECT foo + bar FROM src;
Тут dest і src мають бути іменами таблиць, і col має бути стопцем dest, але foo і bar можуть бути і змінними і стопцями src.
За замовчуванням , PL/pgSQL видасть помилку якщо ім’я команди SQL може посилатись на змінну і на стовпець. Ви можете уникнути такої проблеми переіментувням стопця, або визначенням посиланням невизначеності, або казанням PL/pgSQL яка інтерпретація бажана.
Найпростіше – переіменувати стовпець. Загальним правилом програмування є використання різних іменних сонвенцій для PL/pgSQL змінних які ви використовуєте для імен стовпців. Для прикладу якщо ви назвете v_something коли жодна з ваших змінних не починається з v_, не буде конфлікту.
Альтернативно ви можете визначити змінні невизначеності щоб пролити на них світло. У прикладі вище, src.foo неневизначеним посиланням на стовпець таблиці. Для цього посилайтеся на змінні з мітками їх блоків (дивіться секцію 39.2). Для прикладу
<<block>>
DECLARE
foo int;
BEGIN
foo := ...;
INSERT INTO dest (col) SELECT block.foo + bar FROM src;
Тут block.foo означає змінну навіть якщо є стовпець foo в src. Параметри функції, так як і в спеціальній змінній FOUND, можуть бути кваліфіковані з ім’ям функції, тому що вони неявно оголошені в блоці позначеному іенем функції.
Деколи є непрактичним виправляти всі невизначеності в великому коді PL/pgSQL. В такому випадку ви можете визначити щоб PL/pgSQL сприймав всі невизначеності як змінні (що сумісно з поведінкою PL/pgSQL’s перед PostgreSQL 9.0), або як стовпці таблиці (що сумісно з системами Oracle).
Щоб зробити це поставте конфігураційну змінну plpgsql.variable_conflict до одного з error, use_variable, чи use_column (де error – є значенням по замовчуванню). Цей параметр впливає на всі наступні сомпіляції колманд в PL/pgSQL функціях, але не до тих що вже скомпільовані в сесії. Для задання параметру до завантаження PL/pgSQL , важливим є мати “plpgsql” доданим до списку custom_variable_classes в postgresql.conf. Тому що зміна цього параметру може привести до непередбачуваних зимін в поведінці функцій PL/pgSQL, воно може бути змінене тільки суперкористувачем.
Ви також можете задавати поведінку в базисі функція-до-функції, вставлянням цих спеціальних команд в почат коду функції:
#variable_conflict error
#variable_conflict use_variable
#variable_conflict use_column
Вони впливають тільки на функцію в якій записані, і перевизначають налаштування plpgsql.variable_conflict. Приклад
CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
#variable_conflict use_variable
DECLARE
curtime timestamp := now();
BEGIN
UPDATE users SET last_modified = curtime, comment = comment
WHERE users.id = id;
END;
$$ LANGUAGE plpgsql;
Якщо команда UPDATE , curtime, comment, і id будуть посилатися до зміннихі параметрів має чи ні users колонки тих імен. Помітьте що ми мали визначити посилання до users.id в умові WHERE для зроблення посилання на табличну колонку. Але нам не потрібно визначати посилання на comment як ціль в списку UPDATE , тому що синтаксично це має бути колонкою users.Ми можемо написати цю саму функцію без залежання від variable_conflict налаштувань так:
CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
<<fn>>
DECLARE
curtime timestamp := now();
BEGIN
UPDATE users SET last_modified = fn.curtime, comment = stamp_user.comment
WHERE users.id = stamp_user.id;
END;
$$ LANGUAGE plpgsql;
Заміна змінних не відбудеться в командному рядку даному до EXECUTE або одному з його варіатів.
Якщо вам потрібно вставити необхідні значення в таку команду, використовуйте USING, як описано в секції 39.5.4.
Заміна змінних зараз працює тільки з SELECT, INSERT, UPDATE, і DELETE командами,
Тому що головний движок SQL дозволяє мати параметри тіки таким звітам. Для використання несталого імені чи значення для команд інших типів (утилітних), ви маєете використовувати EXECUTE .
39.10.2. Кешування Плану
PL/pgSQL інтерпретатор аналізує початковий текст функції і видає внутрішнє двійкове командне дерево першого разу при виклику функції (в межах кожної сесії). Командне дерево повністю транслює структуру твердження PL/pgSQL, але індивідуальні вирази SQL і команди SQL що є в функції не транслюються негайно.
Оскільки кожне вираження і команда SQL уперше виконується у функції, PL/pgSQL інтерпретатор створює готовий план (користуючись SPI менеджеровими SPI_prepare and SPI_saveplan функціями) виконання.
Подальші візити до цього виразу або команди використовують багаторазово готовий план. Отже, функція
з умовним кодом, який містить багато тверджень, для яких плани виконання, можливо прийдеться тільки готувати, а зберігати ті, які дійсно використані впродовж тривалості життя підключення бази даних.
Це може істотно скоротити весь час, потрібний для аналізу і генерування плану виконання для тверджень в PL/pgSQL функціях. Недоліком є, що помилки в специфічному вираженні або команді не можуть бути виявлені доки ця частина функції не досягається у виконанні. (Тривіальний синтаксис помилки буде виявлений впродовж початкового аналізуючого проходу, але що-небудь глибше не буде виявлене.)
Збережений план повторно плануватиметься автоматично, якщо є будь-яка зміна схеми до будь-якої таблиці, використаного в запиті, або якщо будь-яка визначувана користувачем функція, використана в запиті, перевизначена. Це робить повторно використовуваний готовий план прозорим у більшості випадків, але є кутові випадки, де поганий план, може бути повторно використаним. Прикладом є, що скидання і знову створення визначуваного користувачем оператора не впливатиме на вже-поміщені в кеш плани; вони продовжуватимуть визивати функцію що є в основі оператора оригіналу, якщо він не був змінений. Коли необхідний, кеш може скинути на диск старт свіжої сесії бази даних.
Оскільки PL/pgSQL зберігає плани виконання таким чином, команди SQL, які з'являються безпосередньо в
PL/pgSQL функції повинні посилатися на ті ж таблиці і стовпці при кожному виконанні; тобто, ви не можете використовувати параметр як ім'я таблиці або стовпця в команді SQL. Щоб обійти це обмеження ви можете сконструювати динамічні команди, користуючись PL/pgSQL EXECUTE — ціною буде будівництво нового плану виконання при кожному виконанні.
Іншою важливий точкою є, що готові плани параметризуються, щоб дозволити значенням PL/pgSQL змінюватись, щоб змінитися від одного використання до наступного, як обговорювалось детально вище. Іноді це означає
що план менш ефективний, ніж це було б, якщо генерується для специфічного значення змінної. Як приклад
вважати
SELECT * INTO myrec FROM dictionary WHERE word LIKE search_term;
де search_term - PL/pgSQL змінна. Поміщений в кеш план для цього запиту ніколи не користуватиметься індексом
на word, оскільки планувальник не може припускати, що шаблон LIKE буде ліворуч поставлений на якір під час виконання Щоб користуватися індексом, запит повинен плануватися із специфічною константою LIKE. Це є
ще одна ситуація, де EXECUTE може бути використаний, щоб змусити генерувати новий план для кожного виконання.
Мінлива природа змінних запису подарувала іншу проблему у зв'язку з цим. Коли поля змінної запису використані у виразах або твердженнях, типи даних полів не повинні змінюватися від одного виклику функції до наступного, оскільки кожне вираження плануватиметься, користуючись типом даних що є присутнім, коли вираження уперше досягнуте. EXECUTE може бути використаний, щоб наблизитися до цієї проблеми коли необхідно.
Якщо та ж функція використана як тригер більш ніж для одної таблиці, PL/pgSQL готується і кеші
планує самостійно для кожної такої таблиці— тобто, є кеш для кожної комбінації тригерної функції і таблиці, не лише для кожної функції. Це полегшує деякі з проблем зі зміною типів даних; наприклад, тригерна функція зможе працювати успішно із стовпцем, названим key, навіть якщо вана має різні типи в різних таблицях.
Також, функції з поліморфними типами аргументу мають окремий кеш плану для кожної комбінації
з типів фактичного параметра для яких вони були викликані, таким чином, що відмінності типу даних не викликають несподіваних відмов.
Кешування плану може іноді мати дивовижні ефекти на інтерпретації чутливих для часу значеь. Для прикладу є різниця між тим, що ці дві функції роблять:
CREATE FUNCTION logfunc1(logtxt text) RETURNS void AS $$
BEGIN
INSERT INTO logtable VALUES (logtxt, ’now’);
END;
$$ LANGUAGE plpgsql;
і:
CREATE FUNCTION logfunc2(logtxt text) RETURNS void AS $$
DECLARE
curtime timestamp;
BEGIN
curtime := ’now’;
INSERT INTO logtable VALUES (logtxt, curtime);
END;
$$ LANGUAGE plpgsql;
В випадку logfunc1, головний аналізатор PostgreSQL знає, готуючи план для INSERT що рядок ’now’ треба інтерпретувати як timestamp, тому що цільовий стовпець logtable є цим типом. Отже, ’now’ буде перетворений в константу, коли INSERT планується, а потім використаний в усіх викликах logfunc1 впродовж тривалості життя сесії. Само собою зрозуміло, що, це - не те, що хоче програміст.
В випадку logfunc2, головний аналізатор PostgreSQL не знає, чим тип ’now’ повинен стати і тому він повертає значення даних типу text, що містить рядок now. Впродовж призначення до локальної змінної curtime, PL/pgSQL інтерпретатор приводить цей рядок до timestamp типу викликаючи text_out і timestamp_in для конвертації. Тож вирахуваний чсовий штамп модифікується на кожному виконанні як і очікує програміст.
39.11. Підказки для Розробки в PL/pgSQL
Єдиний хороший шлях розробки в PL/pgSQL - користуватися текстовим редактором вашого вибору, щоб створити ваші функції і в іншому вікні, користуватися psql, щоб завантажити і перевірити ті функції. Якщо ви робите це таким чином, це є хороша ідея, записувати функції використовуючи CREATE OR REPLACE FUNCTION. Цим шляхом ви межететільки перезавантажити функцію для зміни її визначення. Наприклад:
CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $$
....
$$ LANGUAGE plpgsql;
Виконуючи psql, ви можете завантажити або перезавантажити такий файл функції з :
\i filename.sql
а потім негайно використати команди SQL, щоб перевірити функцію.
Інший хороший шлях розробкм в PL/pgSQL з інструментом GUI доступу до бази даних, який полегшує розробку на процедурній мові. Один приклад такого інструменту - pgAdmin, хоча інші також існують. Ці інструменти часто забезпечують зручні особливості як наприклад вихід одиничних лапок і забезпечення простішого відновлення і налагодження функції.
39.11.1. Обробка Лапок
Код PL/pgSQL, в якому функція вказана в CREATE FUNCTION як рядковий літерал. Якщо ви записуєте рядковий літерал в звичайний шлях в одиничних лапках, потім будь-які одиничні лапки усередині тіла функції мають бути подвоєні; також будь-які бекслеші мають бути подвоєні (тільки з використання синтаксису виходу рядка). Подвійні лапки у більш марудних, і у складних випадках де код може стати не на жарт незбагненним, тому що прийдеться шукти пари до дюжини ляпок. Рекомендуються, замість цього писати тіло функції "dollarquoted (доларолапкованим)" (подивіться Секцію 4.1.2.4). У доларо-лапкуючому методі, ви ніколи не подвоїте жодні лапки, але змушені будете вибирати різні доларові мітки для кожного рівня вашої функції. Для прикладу ви можете написати свою CREATE FUNCTION як:
CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $PROC$
....
$PROC$ LANGUAGE plpgsql;
Всередині цього, ви можете використовувати лапки для простих рядкових літералів в SQL командах і $$ для розділу фрагментів SQL команд що є рядками. Якщо вам потрібний текст в лапках що містить $$, ви можете використовувати $Q$, і тд.
Найтупний чарт показує шо ви маєте робити при написанні коду баз доларового лапкування. Це може бути корисним для портування коду без доларового лапкування в код з ним.
1 лапка
Для початку і кінця тіла функції:
CREATE FUNCTION foo() RETURNS integer AS ’
....
’ LANGUAGE plpgsql;
Будь де всередині обмеженого одинарними лапками тіла функції , лапки мають мати пари.
2 лапки
Для рядкових літералів в тілі функції:
a_output := ”Blah”;
SELECT * FROM users WHERE f_name=”foobar”;
А з доларовим лапкуванням:
a_output := ’Blah’;
SELECT * FROM users WHERE f_name=’foobar’;
Що є однаковим для аналізатора PL/pgSQL в обох випадках.
4 лапки
Коли вам потрібні одинарні лапки у рядковій константі в тілі функції:
a_output := a_output || ” AND name LIKE ””foobar”” AND xyz”
Значення що відноситься до a_output буде: AND name LIKE ’foobar’ AND xyz.
В доларовому лапкуванні:
a_output := a_output || $$ AND name LIKE ’foobar’ AND xyz$$
будьте обережні що будь який доларовий розділювач навколо цього є не тільки $$.
6 лапок
Коли одинарні лапки в рядку всередині тіла функції в сусідстві з з кінцем цієї рядкової константи:
a_output := a_output || ” AND name LIKE ””foobar”””
Значенням a_output буде: AND name LIKE ’foobar’.
В доларовому лапкуванні:
a_output := a_output || $$ AND name LIKE ’foobar’$$
10 лапок
Коли ви хочете одинарні лапки в рядковій константі (з кількістю 8 лапок) в сусідстві з кінцем цьої рядкової константи ( +2). Вам знадибиться тільки для функції що генерує інші функції, як в прикладі 39-7. Для прикладу:
a_output := a_output || ” if v_” ||
referrer_keys.kind || ” like ”””””
|| referrer_keys.key_string || ”””””
then return ””” || referrer_keys.referrer_type
|| ”””; end if;”;
Значенням a_output буде:
if v_... like ”...” then return ”...”; end if;
З доаровим лапкуванням:
a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like ’$$
|| referrer_keys.key_string || $$’
then return ’$$ || referrer_keys.referrer_type
|| $$’; end if;$$;
Ми вважаємо що нам потрібно класти тільки одні лапки в a_output, тому що воно буде перелапковано перед використанням.
39.12. Портування з Oracle PL/SQL
Ця секція пояснює відмінності між PostgreSQL PL/pgSQL мовою і Oracle PL/SQL, щоб допомогти розробникам, які портують додатки з Oracle® в PostgreSQL.
PL/pgSQL подібний до PL/SQL у багатьох аспектах. Це є блокова-структура, імперативна мова, і
усі змінні доведеться оголосити. Призначення, цикли, умовні вирази подібні. Основні відмінності
ви повинні мати на увазі, переносячи з PL/SQL до PL/pgSQL є:
• Якщо ім'я, використане в команді SQL, змогло бути або ім'ям стовпця таблиці, або посиланням на змінну функції, PL/SQL сприймає це як ім'я стовпця. Це відповідає PL/pgSQL plpgsql.variable_conflict = use_column поведінці, яка не є значенням по умовчанню, як пояснено у Секції 39.10.1. Часто краще уникати таких двозначностей, але якщо вам доведеться перенести велику кількість коду, який залежить від цієї поведінки, налаштування variable_conflict,буде найкращим рішенням.
• У PostgreSQL тіло функції має бути записане як рядковий літерал. Тому вам треба використовувати
доларове лапкування або вихідні одиничні лапки в тілі. (Подивіться Секцію 39.11.1.)
• Замість пакетів, користуються схемами, щоб організувати ваші функції в групи.
• Оскільки немає ніяких пакетів, немає ніяких змінних рівня пакету також. Це трошви набридає. Ви можете зберегти стан за-сесію в тимчасових таблицях замість того.
• Цілочисельні FOR цикли з REVERSE працюють порізному: PL/SQL відраховує з другого номера до першого, а PL/pgSQL відраховує з першолго до другого, що випагає розвороту циклу при портуванні. Це доволі неприємна невідповідність але навряд чи вона буде змінена. (дивіться секцію 39.6.3.5.)
• FOR цикли на запитах (але не на курсорах) також працюють по іншому: змінна цілі(s) має бути оголошеною, коли PL/SQL завжди оголошує її неявно. І на додачу до цього ітерувальна змінна є доступною після завершення циклу.
• Є деякі відмінності в використанні курсорів.
39.12.1. Портування Прикладів
Приклад 39-6 показує, як перенести просту функцію з PL/SQL до PL/pgSQL.
Приклад 39-6. Перенесення Простої Функції з PL/SQL до PL/pgSQL
Ось - функція Oracle PL/SQL :
CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
v_version varchar)
RETURN varchar IS
BEGIN
IF v_version IS NULL THEN
RETURN v_name;
END IF;
RETURN v_name || ’/’ || v_version;
END;
/
show errors;
Давайте подивимось на відмінності її з PL/pgSQL:
• Ключове слово RETURN в прототипі функції (не в тілі) стає RETURNS в PostgreSQL. Також, IS стає AS, і вам треба додати умову LANGUAGE, тому що PL/pgSQL не є єдиною мовою.
• У PostgreSQL, тіло функції є рядковим літералом, тож вам треба користуватися лапками або доларовими лапками навколо нього. Це замінює / в підході Oracle.
• show errors команди не існує в PostgreSQL, і вона не потрібна оскільки про помилки звітується автоматично.
Це, як би ця функція виглядала, перенесена до PostgreSQL:
CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
v_version varchar)
RETURNS varchar AS $$
BEGIN
IF v_version IS NULL THEN
RETURN v_name;
END IF;
RETURN v_name || ’/’ || v_version;
END;
$$ LANGUAGE plpgsql;
Приклад 39-7 показує, як перенести функцію, яка створює іншу функцію і як обробити проблему з лапками що виникає
Приклад 39-7. Переніс Функції, яка Створює Іншу Функцію з PL/SQL до PL/pgSQL
Наступна процедура виймає ряди з SELECT і формує велику функцію з результатами в IF, для ефективності.
Це версія Oracle :
CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS
CURSOR referrer_keys IS
SELECT * FROM cs_referrer_keys
ORDER BY try_order;
func_cmd VARCHAR(4000);
BEGIN
func_cmd := ’CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR,
v_domain IN VARCHAR, v_url IN VARCHAR) RETURN VARCHAR IS BEGIN’;
FOR referrer_key IN referrer_keys LOOP
func_cmd := func_cmd ||
’ IF v_’ || referrer_key.kind
|| ’ LIKE ”’ || referrer_key.key_string
|| ”’ THEN RETURN ”’ || referrer_key.referrer_type
|| ”’; END IF;’;
END LOOP;
func_cmd := func_cmd || ’ RETURN NULL; END;’;
EXECUTE IMMEDIATE func_cmd;
END;
/
show errors;
Тут - те, як би ця функція виглядала в PostgreSQL:
CREATE OR REPLACE FUNCTION cs_update_referrer_type_proc() RETURNS void AS $func$
DECLARE
CURSOR referrer_keys IS
SELECT * FROM cs_referrer_keys
ORDER BY try_order;
func_body text;
func_cmd text;
BEGIN
func_body := ’BEGIN’;
FOR referrer_key IN referrer_keys LOOP
func_body := func_body ||
’ IF v_’ || referrer_key.kind
|| ’ LIKE ’ || quote_literal(referrer_key.key_string)
|| ’ THEN RETURN ’ || quote_literal(referrer_key.referrer_type)
|| ’; END IF;’ ;
END LOOP;
func_body := func_body || ’ RETURN NULL; END;’;
func_cmd :=
’CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host varchar,
v_domain varchar,
v_url varchar)
RETURNS varchar AS ’
|| quote_literal(func_body)
|| ’ LANGUAGE plpgsql;’ ;
EXECUTE func_cmd;
END;
$func$ LANGUAGE plpgsql;
Зверніть увагу, як тіло функції формується окремо і передається через quote_literal для подвоєння лапок в ній. Ця техніка потрібна, тому що ми не можемо безпечно користуватися доларовим лапкуванням для визначення нової функції: ми не знаємо достовірно, від яких рядків є інтерпольовано поле referrer_key.key_string. (Ми вважаємо тут, що eferrer_key.kind може бути довіреною що вона завжди буде host, domain, чи url, але referrer_key.key_string, може бути будь чим і навіть містити знаки долара.) Ця функція - фактично удосконалення оригіналу Oracle, тому що вона не генеруватиме поламаний код, коли referrer_key.key_string чи referrer_key.referrer_type містять лапки.
Приклад 39-8 показує, як перенести функцію із OUT параметрами і операцією над рядками. PostgreSQL не має стандартної instr функції, але ви можете створити її, користуючись комбінацією інших функцій.
У Секції 39.12.3 є PL/pgSQL реалізації instr, якими ви можете користуватися, щоб зробити ваше портування простішим.
Приклад 39-8. Переніс Процедури З Операцією над Рядками і OUT Параметрами з PL/SQL до PL/pgSQL
Наступна процедура Oracle PL/SQL використана, щоб проаналізувати URL і повернути декілька вузлів елементів ( шлях, і запит).
Це версія Oracle :
CREATE OR REPLACE PROCEDURE cs_parse_url(
v_url IN VARCHAR,
v_host OUT VARCHAR, -- This will be passed back
v_path OUT VARCHAR, -- This one too
v_query OUT VARCHAR) -- And this one
IS
a_pos1 INTEGER;
a_pos2 INTEGER;
BEGIN
v_host := NULL;
v_path := NULL;
v_query := NULL;
a_pos1 := instr(v_url, ’//’);
IF a_pos1 = 0 THEN
RETURN;
END IF;
a_pos2 := instr(v_url, ’/’, a_pos1 + 2);
IF a_pos2 = 0 THEN
v_host := substr(v_url, a_pos1 + 2);
v_path := ’/’;
RETURN;
END IF;
v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
a_pos1 := instr(v_url, ’?’, a_pos2 + 1);
IF a_pos1 = 0 THEN
v_path := substr(v_url, a_pos2);
RETURN;
END IF;
v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
v_query := substr(v_url, a_pos1 + 1);
END;
/
show errors;
Ось можливий переклад на PL/pgSQL:
CREATE OR REPLACE FUNCTION cs_parse_url(
v_url IN VARCHAR,
v_host OUT VARCHAR, -- This will be passed back
v_path OUT VARCHAR, -- This one too
v_query OUT VARCHAR) -- And this one
AS $$
DECLARE
a_pos1 INTEGER;
a_pos2 INTEGER;
BEGIN
v_host := NULL;
v_path := NULL;
v_query := NULL;
a_pos1 := instr(v_url, ’//’);
IF a_pos1 = 0 THEN
RETURN;
END IF;
a_pos2 := instr(v_url, ’/’, a_pos1 + 2);
IF a_pos2 = 0 THEN
v_host := substr(v_url, a_pos1 + 2);
v_path := ’/’;
RETURN;
END IF;
v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
a_pos1 := instr(v_url, ’?’, a_pos2 + 1);
IF a_pos1 = 0 THEN
v_path := substr(v_url, a_pos2);
RETURN;
END IF;
v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
v_query := substr(v_url, a_pos1 + 1);
END;
$$ LANGUAGE plpgsql;
Ця функція можу бути використана як:
SELECT * FROM cs_parse_url(’http://foobar.com/query.cgi?baz’);
Приклад 39-9 показує, як перенести процедуру, яка користується численними особливостями, які специфічні в Oracle.
Приклад 39-9. Перенесення Процедури з PL/SQL до PL/pgSQL
Версія Oracle :
CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS
a_running_job_count INTEGER;
PRAGMA AUTONOMOUS_TRANSACTION;Ê
BEGIN
LOCK TABLE cs_jobs IN EXCLUSIVE MODE;Ë
SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;
IF a_running_job_count > 0 THEN
COMMIT; -- free lockÌ
raise_application_error(-20000,
’Unable to create a new job: a job is currently running.’);
END IF;
DELETE FROM cs_active_job;
INSERT INTO cs_active_job(job_id) VALUES (v_job_id);
BEGIN
INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, sysdate);
EXCEPTION
WHEN dup_val_on_index THEN NULL; -- don’t worry if it already exists
END;
COMMIT;
END;
/
show errors
Процедура типу цього може легко бути переконвертована в функцію PostgreSQL що повертає void. Ця процедура цікава бо може навчити нас кількох речей:
1 Немає PRAGMA команди в PostgreSQL.
2 Якщо ви робите LOCK TABLE в PL/pgSQL, замок не буде відпушено поки не закінчиться транзакція виклику.
3Ви не можете використовувати COMMIT в PL/pgSQL функції. Функція виконується в якісь зовнішній транзакції тож COMMIT завершить виконання цієї транзакції. Хоча в цій ситуації це не важливо бо замок відпуститься LOCK TABLE коли виникне помилка.
Це, як ми змогли перенести цю процедуру до PL/pgSQL:
CREATE OR REPLACE FUNCTION cs_create_job(v_job_id integer) RETURNS void AS $$
DECLARE
a_running_job_count integer;
BEGIN
LOCK TABLE cs_jobs IN EXCLUSIVE MODE;
SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;
IF a_running_job_count > 0 THEN
RAISE EXCEPTION ’Unable to create a new job: a job is currently running’;Ê
END IF;
DELETE FROM cs_active_job;
INSERT INTO cs_active_job(job_id) VALUES (v_job_id);
BEGIN
INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now());
EXCEPTION
WHEN unique_violation THEN Ë
-- don’t worry if it already exists
END;
END;
$$ LANGUAGE plpgsql;
1 Синтаксис RAISE відмінний від Oracle’, хоча базовий випадок RAISE exception_name працює подібно.
2, Імена винятків підтримувані PL/pgSQL інші ніж в Oracle. Набір вбудованих винятків набагато більший (Додаток A). Зараз немає можливості оголосити користувацікі імена винятків, хоча для цього ви може використовувати хначення SQLSTATE.
Основна функціональна різниця між цією процедурою і еквівалентом Oracle є, що виключний замок на таблиці cs_jobs триматиметься завершення викликаючої транзакції. Також, якщо викликаюча програма пізніше відміниться (наприклад через помилку), ефекти цієї процедури будуть прокручені назад.
39.12.2. Інші Речі, за якими варто слідкувати
Ця секція пояснює декілька інших речей, за якими слідкувати, переносячи функції Oracle PL/SQL до PostgreSQL.
39.12.2.1. Неявне зворотне Перемотування після винятків
У PL/pgSQL, коли виняток лапається EXCEPTION, уся база даних змінюється оскільки блок BEGIN автоматично прокручується назад. Тобто, поведінка еквівалентна до того, що б ви отримали в Oracle з:
BEGIN
SAVEPOINT s1;
... code here ...
EXCEPTION
WHEN ... THEN
ROLLBACK TO s1;
... code here ...
WHEN ... THEN
ROLLBACK TO s1;
... code here ...
END;
Якщо ви переводите процедуру Oracle, яка користується SAVEPOINT і ROLLBACK TO в цьому стилі то ваше завдання просте: тільки опустити SAVEPOINT і ROLLBACK TO. Якщо ви маєте процедуру, що використовує SAVEPOINT і ROLLBACK TO в інший спосіб тоді прийдеться подумати.
39.12.2.2. EXECUTE
PL/pgSQL версія EXECUTE працює подібно як в PL/SQL, але вам необхідно пам’ятати використовувати quote_literal і quote_ident як в секції 39.5.4. Конструкти типу EXECUTE ’SELECT * FROM $1’; не будуть працювати без тих функцій.
39.12.2.3. Optimizing PL/pgSQL Functions
PostgreSQL дає вам два модифікатори створення функцій для покращення оптимізації: “volatility”(мінливість) (чи функція завжди повертає однаковий результат з однаковим вхідним аргументом) і “strictness”(точність) (чи функція повертає нуль з нульовим вхідним аргументом). Для деталей гляньте сторінку CREATE FUNCTION.
З використанням цих модифікаторів, ваша CREATE FUNCTION команда може виглядати так:
CREATE FUNCTION foo(...) RETURNS integer AS $$
...
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
39.12.3. Додаток
Ця секція містить код для набору Oracle-сумісних instr функцій що ви можете використати для полекшення портування ваших додатків.
--
-- instr functions that mimic Oracle’s counterpart
-- Syntax: instr(string1, string2, [n], [m]) where [] denotes optional parameters.
--
-- Searches string1 beginning at the nth character for the mth occurrence
-- of string2. If n is negative, search backwards. If m is not passed,
-- assume 1 (search starts at first character).
--
CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS $$
DECLARE
pos integer;
BEGIN
pos:= instr($1, $2, 1);
RETURN pos;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
CREATE FUNCTION instr(string varchar, string_to_search varchar, beg_index integer)
RETURNS integer AS $$
DECLARE
pos integer NOT NULL DEFAULT 0;
temp_str varchar;
beg integer;
length integer;
ss_length integer;
BEGIN
IF beg_index > 0 THEN
temp_str := substring(string FROM beg_index);
pos := position(string_to_search IN temp_str);
IF pos = 0 THEN
RETURN 0;
ELSE
RETURN pos + beg_index - 1;
END IF;
ELSE
ss_length := char_length(string_to_search);
length := char_length(string);
beg := length + beg_index - ss_length + 2;
WHILE beg > 0 LOOP
temp_str := substring(string FROM beg FOR ss_length);
pos := position(string_to_search IN temp_str);
IF pos > 0 THEN
RETURN beg;
END IF;
beg := beg - 1;
END LOOP;
RETURN 0;
END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
CREATE FUNCTION instr(string varchar, string_to_search varchar,
beg_index integer, occur_index integer)
RETURNS integer AS $$
DECLARE
pos integer NOT NULL DEFAULT 0;
occur_number integer NOT NULL DEFAULT 0;
temp_str varchar;
beg integer;
i integer;
length integer;
ss_length integer;
BEGIN
IF beg_index > 0 THEN
beg := beg_index;
temp_str := substring(string FROM beg_index);
FOR i IN 1..occur_index LOOP
pos := position(string_to_search IN temp_str);
IF i = 1 THEN
beg := beg + pos - 1;
ELSE
beg := beg + pos;
END IF;
temp_str := substring(string FROM beg + 1);
END LOOP;
IF pos = 0 THEN
RETURN 0;
ELSE
RETURN beg;
END IF;
ELSE
ss_length := char_length(string_to_search);
length := char_length(string);
beg := length + beg_index - ss_length + 2;
WHILE beg > 0 LOOP
temp_str := substring(string FROM beg FOR ss_length);
pos := position(string_to_search IN temp_str);
IF pos > 0 THEN
occur_number := occur_number + 1;
IF occur_number = occur_index THEN
RETURN beg;
END IF;
END IF;
beg := beg - 1;
END LOOP;
RETURN 0;
END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;