Транзакции в PostgreSQL версии 8.0

Несомненно, транзакции очень хороши, но транзакции в предыдущих версиях PostgreSQL пропагандировали лозунг – “все, или ничего”, останавливая транзакцию, если ошибка произошла в ее пределах. К счастью, новая версия PostgreSQL 8 позволяет взглянуть на это подругому, добавляя “savepoints” (точки сохранения), позволяя откатить назад только часть транзакции и восстановиться от ошибки изящно.

Одна из очень хороших особенностей PostgreSQL – транзакции. Они предотвращают случайную потерю данных или их искажение.

Например, скажем, вы хотите удалить записи в таблице. В PostgreSQL команда выглядит так:

template1=# DELETE FROM foo;


Однако, данная команда удаляет все записи в таблице. Это, вероятно, вовсе не то, чего вы хотите, и, если вы не использовали транзакцию, восстановление из резервной копии будет единственным способом восстановить базу. Используя транзакции, вернуть данные очень просто:

BEGIN;
DELETE FROM foo;
DELETE 50


Параметр BEGIN заставляет базу начать транзакцию. Поскольку, скоро вы понимаете, что забыли включить параметр WHERE, и удалили все столбцы, то вам необходимо будет сделать обратную перемотку и восстановить данные:

BEGIN;
DELETE FROM foo;
DELETE 50
ROLLBACK;


Есть один недостаток в такой реализации транзакций – если внутри транзакции произошла ошибка, вы вынуждены сделать перемотку. Команда перемотки должна будет выполнена в пределах транзакции, до того, как произведены какие-либо команды в пределах соединения. Как только перемотка будет выполнена, вы должны будете повторить действия снова в том виде, который не будет вызывать ошибку. Это правило включает в себя и ошибки пользователей: типа удаления всех отчетов в таблице; синтаксические ошибки: типа попытки выбрать из столбца, который не существует. Например:

BEGIN;
UPDATE foo SET bar = (SELECT count(*) FROM baz));
INSERT INTO foo (column1) SELECT column2 FROM bar;
ОШИБКА: отношение "bar" не существует
CREATE TABLE bar (column1 text, column2 float);
ОШИБКА:  текущая транзакция прервана
команды, игнорируемые до конца блока


Из-за ошибки вы будете делать перемотку и вся ваша текущая работа будет потеряна. Этот специфический момент транзакций PostgreSQL особенно раздражает в период отладки и тестирования.

Точки сохранения – путь к спасению.

Новая версия PostgreSQL 8 обращается к этой проблеме с помощью точек сохранения - 'savepoints'. Точки сохранения - это именованные метки, которые разбивают транзакцию на этапы, заставляя базу сохранять данные в рамках каждого этапа. В случае ошибки, мы можем определить этап на котором она возникла, и сделать перемотку только до предыдущей точки сохранения, не теряя при этом работу, которая лежит перед savepoint с ошибкой. Поскольку кажная savepoint имеет свое имя, то и обращаться к ней необходимо по ее имени.

Чтобы инициализировать savepoint, вы должны быть в пределах операционного блока:

template1=# BEGIN;

BEGIN
template1=# INSERT INTO foo(column1,column2,column3) VALUES (1,2,0);
INSERT 17231 1
template1=# INSERT INTO foo(column1,column2,column3) VALUES (1,2,0);
INSERT 17232 1
template1=# INSERT INTO foo(column1,column2,column3) VALUES (1,2,0);
INSERT 17233 1
template1=# SELECT * FROM foo;
column1 | column2 | column3
---------+--------+--------
1 |       2 |       0
1 |       2 |       0
1 |       2 |       0
(3 rows)

template1=# SAVEPOINT main_values_inserted;
SAVEPOINT
template1=# INSERT INTO foo(column1,column2,column3) VALUES (1,2,1/0);
ОШИБКА:  деление на ноль
ОШИБКА:  деление на ноль


В предыдущей версии PostgreSQL, вы потеряли бы все INSERT данные в предыдущем коде (после возникновения ошибки деления на ноль в последнем утверждении INSERT), но в версии 8 вы можете сделать перемотку до определенного savepoint. Обратите внимание, что код содержит savepoint с именем 'main_values_inserted'. Для обратной перемотки до этой savepoint вы можете написать:

template1=# ROLLBACK TO main_values_inserted;
ROLLBACK


Выполняя перемотку до этой savepoint вы сохраняете все, что было до нее, теряя только работу выполненную после savepoint. После перемотки вы можете продолжить свою работу без полного повтора транзакции:

template1=# INSERT INTO foo(column1,column2,column3) VALUES (5,9,10);
INSERT 17234 1
template1=# INSERT INTO foo(column1,column2,column3) VALUES (5,9,10);
INSERT 17235 1
template1=# INSERT INTO foo(column1,column2,column3) VALUES (5,9,10);
INSERT 17236 1
template1=# INSERT INTO foo(column1,column2,column3) VALUES (5,9,10);
INSERT 17237 1
template1=# SAVEPOINT secondary_values_inserted;
SAVEPOINT
template1=# SELECT * FROM foo;
column1 | column2 | column3
---------+--------+--------
1 |       2 |       0
1 |       2 |       0
1 |       2 |       0
5 |       9 |      10
5 |       9 |      10
5 |       9 |      10
5 |       9 |      10
(7 rows)
template1=# SAVEPOINT all_values_inserted;
SAVEPOINT
template1=# DELETE FROM foo;
DELETE 7
template1=# SELECT * FROM foo;
column1 | column2 | column3
---------+--------+--------
(0 rows)


Ой. Также, как и в первом примере этой статьи, вы фактически хотели удалить только одну строку где 'column = 1'. Но вы можете сделать перемотку ко второй savepoint 'all_values_inserted' в коде выше.

template1=# ROLLBACK TO all_values_inserted;
ROLLBACK
template1=# SELECT * FROM foo;
column1 | column2 | column3
---------+--------+--------
1 |       2 |       0
1 |       2 |       0
1 |       2 |       0
5 |       9 |      10
5 |       9 |      10
5 |       9 |      10
5 |       9 |      10
(7 rows)


Обратите внимание, это восстановило все ваши данные. Теперь вы можете запустить корректные команды для удаления.

template1=# DELETE FROM foo WHERE column1 = 1;
DELETE 3
template1=# SELECT * FROM foo;
column1 | column2 | column3
---------+--------+--------
5 |       9 |      10
5 |       9 |      10
5 |       9 |      10
5 |       9 |      10
(4 rows)


Наконец, вы можете завершить вашу транзакцию. Последняя команда SELECT показывает, что целостность данных после всех этих вставок и обратных перемоток не повреждена.

template1=# COMMIT;
COMMIT
template1=# select * from foo;
column1 | column2 | column3
---------+--------+--------
5 |       9 |      10
5 |       9 |      10
5 |       9 |      10
5 |       9 |      10
(4 rows)


Джошуа Д. Дрейк – Президент Command Prompt, Inc. Компания занимается поддержкой PostgreSQL программированием. Он также соавтор 'Практического PostgreSQL' от издательства O'reilly и Партнеров.

Статьи по теме

Оптимизация и тюнинг производительности MariaDB MySQL сервера внутри Docker

Содержание: Установка и знакомство с MySQLTuner Установка MySQL Tuning Primer Script Первичная оптимизация MySQL сервера ...

2023-11-08

Работа с базами данных MySQL в ISPManager 5 + Docker

В ISPManager 5 добавлена контейнерная виртуализация Docker для использования альтернативных версий MySQL. Цитата из документации ISPManager 5: ...

2023-10-27

«Облачные» хранилища данных: в ожидании доверия

Информационные технологии активно мигрируют в сторону «облаков». Эксперты прогнозируют, что одним из главных приоритетов для CIO в ближ...

2011-11-05

ГОСТ 34.602-89. на Техническое задание по разработке web проекта

ТЕХНИЧЕСКОЕ ЗАДАНИЕ НА СОЗДАНИЕ АВТОМАТИЗИРОВАННОЙ СИСТЕМЫ Information technology. Set of standards for automated systems. Technical directions ...

2010-12-28

Orange color: теория и практика

Знаете ли Вы, чем оранжевый цвет отличается от других ? Если нет, то знайте, что разница в скорости восприятия глазом отдельных цветов ра...

2010-12-27

FAQ по MS SQL 7.0

11. Как запускать-останавливать SQL Server через командную строку? Мне это нужно для использования в скриптах.Остановка:NET STOP MSSQLSERVERили если вы...

2010-09-17

Виртуальная реализация SQL Server 2005

Ранее в материале «Разбираем “айсберг” sql server 2005» читателям предлагалось воспользоваться опубликованной в internet серией информационных...

2010-09-17

SQL Server в вопросах и ответах

В: У меня на разных компьютерах установлено два sql server 7.0. Для обмена данными между ними я использовал data transformation services (dts), чтобы импортиро�...

2010-09-17

Один из вариантов соглашения об именах объектов MS SQL Server

Помните, что имена объектов sql сервера не могут превышать 30 символов. Не используйте зарезервированные слова, специальные символы, и не н...

2010-09-17

Создание и удаление БД в MS SQL Server

В sql server'е базы данных хранятся в виде обычных файлов на диске. Как минимум на одну БД приходится таких файлов 2: *.mdf и *.ldf. В первом хранятся...

2010-09-17

Создание и удаление таблиц в MS SQL Server

Все данные в БД sql server хранятся в таблицах.Таблицы состоят из колонок, объединяющих значения одного типа, и строк - записей в таблице. В од�...

2010-09-17

Единая аутентификация Windows NT/2000 и Oracle

При использовании Windows NT/2000 как платформы для работы Oracle, и на стороне сервера и на стороне клиентов, имеется возможность использовать ед...

2010-09-17

Oracle / PHP FAQ

Что такое PHP и что необходимо иметь для работы с Oracle?PHP - рекурсивный акроним "PHP Hypertext Preprocessor". Это интерпретируемый язык программирования ...

2010-09-17

Oracle и PHP - это очень просто

ВведениеСУБД Oracle, когда против желания фирмы-изготовителя, а когда согласно – в зависимости от направления политических ветров в конкр�...

2010-09-17

Сжатие данных в целях экономии места и ускорения работы Oracle

Используйте сжатие данных таблицы для экономии места на диске и повышения производительности запросов. В большинстве систем поддержки ...

2010-09-17