Транзакции и курсоры
В PostgreSQL используется специфический подход к обработке транзакций в базах данных. В терминологии SQL транзакцией называется процесс синхронизации результатов выполнения команды с состоянием данных в базе. Данные не записываются на диск немедленно, а лишь отражаются в «текущей» информации состояния, хранящейся в базе. В результате фиксации транзакции последствия выполнения команды окончательно фиксируются в текущем состоянии базы данных.
Возникает очевидная проблема — что произойдет, если два пользователя одновременно попытаются зафиксировать взаимоисключающие изменения в одном объекте базы данных? В некоторых СУБД подобные конфликты предотвращаются путем блокировки (locking).
Блокировкой называется механизм, запрещающий выборку из объекта базы данных на время его модификации, и наоборот. Применение блокировки связано с очевидными проблемами из области быстродействия. Например, выборка заблокированных данных становится невозможной до момента завершения обновления.
В PostgreSQL используется механизм MVCC (Multi-Version Concurrency Control),
позволяющий выполнять команды SQL в отложенных транзакционных блоках.
Таким образом, каждое подключение к серверу PostgreSQL до фиксации результатов фактически поддерживает временный «образ» объектов базы данных, модифицируемых в транзакцпопном блоке.
Если в программе отсутствует команда начала транзакциопного блока, все команды SQL, передаваемые PostgreSQL, фиксируются автоматически, то есть база данных синхронизируется с. результатами команд сразу же после их выполнения. Тем не менее при явном создании транзакщюнного блока изменения в базе данных остаются невидимыми для других пользователей вплоть до фиксации. Таким образом, появляется возможность одновременного внесения изменений в разные объекты базы данных. Далее все изменения либо фиксируются, либо откатываются (отменяются).
При откате транзакции все объекты возвращаются к состоянию, в котором они находились до начала транзакционпого блока. Такая возможность может пригодиться при восстановлении после незавершенных операций или при отмене любых частичных изменений. Отмененные транзакции не фиксируются в базе данных, и другие пользователи даже не замечают последствий незавершенной транзакции.
В PostgreSQL также поддерживаются курсоры — универсальный и гибкий механизм ссылок на выполненные запросы SQL. Курсор позволяет перебрать содержимое итогового набора и включить в выборку только некоторые из полученных записей. При правильном использовании курсоры повышают эффективность работы со статическими наборами записей в приложениях. Курсоры выполняются только в транзакционных блоках.
В следующем подразделе описаны основные принципы работы с транзакциями и курсорами. В частности, в нем рассматриваются команды создания, фиксации и отката транзакций, а также способы объявления, перемещения и выборки в курсорах.
Транзакционные блоки
Транзакционные блоки создаются командой SQL BEGIN, за которой могут следовать необязательные ключевые слова WORK или TRANSACTION. Эти ключевые слова делают команду более наглядной, но никак не влияют на работу ее пли транзакци-онного блока.
В листинге 7.38 приведен пример создания транзакционного блока в базе данных booktown.
Листинг 7.38. Создание транзакции
bOOktown=# BEGIN;
BEGIN
С точки зрения пользователя, вносящего изменения в базу данных, все команды SQL после команды BEGIN выполняются немедленно. Но как упоминалось выше, для других пользователей эти изменения остаются невидимыми вплоть до момента фиксации транзакционного блока.
Транзакционный блок завершается командой SQL COMMIT, за которой также могут следовать необязательные ключевые слова WORK или TRANSACTION. В листинге 7.39 команда SQL COMMIT синхронизирует состояние базы данных с результатами команды UPDATE.
Листинг 7.39. Фиксация транзакции
booktown-# BEGIN; BEGIN
booktown=# UPDATE subjects SET location = NULL
booktown-f WHERE id = 12;
UPDATE 1
booktown=# SELECT location FROM subjects WHERE id - 12:
location
(1 row)
booktown=# COMMIT;
COMMIT
Как видно из листинга, хотя результаты команды UPDATE немедленно отражаются на выборке, выполняемой командой SELECT, другие пользователи, подключенные к той же базе данных, ничего не будут знать о них вплоть до выполнения команды COMMIT.
Транзакции откатываются командой SQL ROLLBACK, за которой также могут следовать необязательные ключевые слова WORK или TRANSACTION.
В листинге 7.40 мы создаем транзакцию, вносим изменения в таблицу subjects и убеждаемся в их присутствии. Затем транзакция откатывается, и таблица возвращается к состоянию, в котором она находилась до начала транзакции.
Листинг 7.40. Откат транзакции
booktown=# и
BEGIN
booktown=# SELECT * FROM subjects WHERE id = 12;
id | subject | location
12 | Religion | (1 row)
booktown=# UPDATE subjects SET location = 'Sunset Dr'
booktown-# WHERE id = 12;
UPDATE 1
booktown=# SELECT * FROM subjects WHERE id = 12;
id | subject | location
12 | Religion | Sunset Dr
(1 row)
booktown=# ROLLBACK;
ROLLBACK
booktown=# SELECT * FROM subjects WHERE id = 12;
id | subject I location
12 ] Religion |
(1 row)
PostgreSQL предельно строго относится к ошибкам, возникающим при выполнении команд в транзакциях. Даже простейшие ошибки, вроде приведенной в листинге 7.41, переводят транзакцию в аварийное состояние. В этом состоянии запрещается выполнение любых команд, кроме команд завершения транзакции
(COMMIT или ROLLBACK).
Листинг 7.41. Выход из аварийного состояния
booktown=# BEGIN:
BEGIN
booktown=# SELECT * FROM;
ERROR: parser: parse error at or near ";"
booktown=# SELECT * FROM books;
NOTICE: current transaction is aborted, queries ignored until end of transaction
*ABORT STATE*
booktown=# COMMIT;
Использование курсоров
Курсор SQL в PostgreSQL представляет собой доступный только для чтения указатель на итоговый набор выполненной команды SELECT. Курсоры часто используются в приложениях, хранящих информацию о состоянии подключения к серверу PostgreSQL. Создание курсора и работа со ссылкой на полученный итоговый набор позволяет приложению организовать более эффективную выборку разных записей итогового набора без повторного выполнения запроса с другими значениями LIMIT и OFFSET.
В прикладных интерфейсах (API) курсоры часто используются для объединения нескольких запросов с последующим их отслеживанием и управлением ими через ссылку на курсор на уровне приложения. Тем самым предотвращается необходимость хранения всех результатов в памяти приложения.
Курсоры часто обладают абстрактным представлением в прикладных интерфейсах (пример — класс PgCursor в libpq++), хотя приложение может напрямую создавать курсоры и работать с ними при помощи стандартных команд SQL. В этом подразделе описаны обобщенные принципы работы с курсорами в SQL, продемонстрированные на примере клиента psql. В PostgreSQL существуют четыре команды, предназначенные для работы с курсорами: DECLARE, FETCH, MOVE и CLOSE.
Команда DECLARE определяет и одновременно открывает курсор, после чего заполняет его информацией по результатам итогового набора выполненного запроса. Команда FETCH позволяет получить записи из открытого курсора. Команда MOVE перемещает «текущую» позицию курсора в итоговом наборе, а команда CLOSE закрывает курсор.
ПРИМЕЧАНИЕ
Если вас интересует тема использования курсоров в конкретном интерфейсе API, обращайтесь к документации на API.
Объявление курсора
Команда SQL DECLARE создает курсор и выполняет его. Этот процесс также называется открытием курсора. Курсор может быть объявлен только в существующем транзакционном блоке, поэтому перед объявлением курсора должна быть выполнена команда BEGIN. Синтаксис команды DECLARE:
DECLARE курсор [ BINARY ] [ INSENSITIVE ] [ SCROLL ]
CURSOR FOR запрос
[ FOR { READ ONLY | UPDATE [ OF none [. ...]]}]
- DECLARE курсор. Имя создаваемого курсора.
- [ BINARY ]. Ключевое слово BINARY означает, что выходные данные должны возвращаться в двоичном формате вместо стандартного ASCII-кода. Иногда переключение на двоичный формат повышает эффективность курсора, но это относится лишь к пользовательским приложениям, поскольку стандартные клиенты (такие, как psql) работают только с текстовым выводом.
- [ INSENSITIVE ] [ SCROLL ]. Ключевые слова INSENSITIVE и SCROLL существуют для совместимости со стандартом SQL, но они описывают поведение PostgreSQL по умолчанию, поэтому их присутствие не обязательно. Ключевое слово SQL INSENSITIVE обеспечивает независимость данных, возвращенных курсором, от других курсоров или подключении. Поскольку PostgreSQL требует, чтобы курсоры определялись в транзакционных блоках, это требование заведомо выполняется. Ключевое слово SQL SCROLL указывает, что курсор поддерживает одновременную выборку нескольких записей. Этот режим поддерживается в PostgreSQL по умолчанию, даже если ключевое слово SCROLL не указано.
- CURSOR FOR запрос. Запрос, после выполнения которого итоговый набор становится доступным через курсор.
- FOR { READ ONLY | UPDATE [ OF поле [. ...] ] }. В PostgreSQL 7.1.x поддерживаются курсоры, доступные только для чтения (READ ONLY), поэтому секция FOR оказывается лишней.
В листинге 7.42 мы создаем транзакцию командой BEGIN и открываем курсор с именем all_books, ассоциированный с командой SELECT * FROM books.
Листинг 7.42. Объявление курсора
booktown=# BEGIN;
BEGIN
booktown=# DECLARE all_books CURSOR
booktown-# FOR SELECT * FROM books;
SELECT
Сообщение SELECT в конце листинга 7.42 говорит о том, что команда была выполнена успешно, а записи, полученные в результате запроса, стали доступными для курсора a! l_books.
Выборка из курсора
Выборка записей из курсора производится командой FETCH. Синтаксис команды
FETCH:
FETCH [ FORWARD BACKWARD | RELATIVE ]
[ число ALL | NEXT | PRIOR ]
{ IN | FROM } курсор
В этом объявлении курсор — имя курсора, из которого производится выборка записей. Курсор всегда указывает па «текущую» позицию итогового набора выполненной команды, а в выборке могут участвовать записи, находящиеся до или после текущей позиции. Направление выборки определяется ключевыми словами FORWARD и BACKUARD, но умолчанию используется прямая выборка (FORWARD). Ключевое слово RELATIVE не обязательно и поддерживается лишь для совместимости со стандартом SQL92.
ВНИМАНИЕ
В команде также может использоваться ключевое слово ABSOLUTE, но в PostgreSQL 7.1.x возможности абсолютного позиционирования и выборки в курсорах не реализованы. Курсор использует относительное позиционирование и выводит сообщение о том, что абсолютное позиционирование не поддерживается.
За ключевым словом, идентифицирующим направление, может указываться следующий аргумент — количество записей. Допускается указание конкретного числа записей (в виде целочисленной константы) или одного из нескольких ключевых слов. Ключевое слово ALL означает, что команда возвращает все записи, начиная с текущей позиции курсора. С ключевым словом NEXT (используется по умолчанию) команда возвращает следующую запись от текущей позиции курсора. С ключевым словом PRIOR возвращается запись, находящаяся перед текущей позицией курсора.
Ключевые слова IN и FROM эквивалентны, из них в команде должно присутствовать одно.
В листинге 7.43 выбираются первые четыре записи итогового набора, на который ссылается курсор all_books. Направление не указано, поэтому по умолчанию используется ключевое слово FORWARD. Далее команда FETCH с ключевым словом NEXT выбирает пятую запись, после чего команда FETCH с ключевым словом PRIOR снова возвращается к четвертой записи.
Листинг 7.43. Выборка записей из курсора
booktown=# FETCH 4 FROM all_books;
Id | title | authored | suojectjd
7808 | The Shining | 4156 | 9
4513 | Dune 1 1866 | 15
4267 I 2001: A Space Odyssey | 2001 | 15
1608 I The Cat in the Hat j 1809 2
(4 rows)
booktown=# FETCH NEXT FROM all_books;
id | title | authorjd | subjectjd
1590 Bartholomew and the Oobleck 1809 2
(1 row)
booktown=# FETCH PRIOR FROM all_books:
id | title | authorjd subjectjd
1608 | The Cat in the Hat | 1809 | 2
(1 row)
Перемещение курсора
Курсор поддерживает информацию о текущей позиции в итоговом наборе команды SELECT. Перемещение курсора к заданной записи выполняется командой MOVE. Синтаксис команды MOVE:
MOVE [ FORWARD | BACKWARD | RELATIVE ]
[ число ALL | NEXT | PRIOR ]
{ IN | FROM } курсор
Как видно из приведенного объявления, синтаксис команды MOVE очень близок к синтаксису команды FETCH. Впрочем, команда MOVE никаких записей не возвращает и лишь перемещает текущую позицию курсора. Смещение задается целочисленной константой или ключевым словом ALL (перемещение в заданном направлении на максимально возможное расстояние), NEXT или PRIOR. В листинге 7.44 текущая позиция курсора перемещается на 10 записей вперед.
Листинг 7.44. Перемещение текущей позиции курсора
booktown=# MOVE FORWARD 10
booktown-# IN all_books;
MOVE
Закрытие курсора
Команда CLOSE закрывает ранее открытый курсор. Курсор также автоматически закрывается при выходе из транзакционного блока, в котором он находится, при фиксации транзакции командой COMMIT или ее откате командой ROLLBACK. Синтаксис команды CLOSE (курсор — имя закрываемого курсора):
CLOSE курсор
В листинге 7.45 курсор all_books закрывается и освобождает занимаемую им память, вследствие чего данные курсора становятся недоступными.
Листинг 7.45. Закрытие курсора
booktown=# CLOSE al1_books;
CLOSE
booktown=# COMMIT:
COMMIT
