Расширенные возможности

3.1. Введение

В предыдущей главе мы рассмотрели основы использования SQL для хранения и доступа к вашим данным в PostgreSQL. Теперь мы рассмотрим некоторые более расширенные возможности SQL которые упрощают управление и предохраняют от потери или повреждения данных. В заключении мы рассмотрим некоторые расширения PostgreSQL.

В данной главе мы будем ссылаться на примеры, которые можно найти в Главе 2 для того, чтобы попробовать изменить или улучшить их так, чтобы они помогли вам, когда вы прочтЈте эту главу. Некоторые примеры из данной главы можно также найти в advanced.sql в каталоге tutorial. Этот файл также содержит некоторые данные для загрузки, которые не будут повторяться здесь. (Смотрите Главу 2.1 чтобы понять как использовать этот файл.)

3.2. Представления (Views)

Вернитесь обратно к запросам в Главе 2.6. Допустим, что комбинированный список записей погоды и местоположения городов представляет определенный интерес для вашего приложения, но вы не хотите выполнять соответствующий запрос каждый раз, когда он вам понадобится. Вы можете создать view(представление) для этого запроса, которое получит имя, которое потом можно использовать в запросе как будто обращение происходит к обычной таблице.

CREATE VIEW myview AS
    SELECT city, temp_lo, temp_hi, prcp, date, location
        FROM weather, cities
        WHERE city = name;

SELECT * FROM myview;

Создание необходимых представлений является одним из ключевых аспектов хорошего дизайна базы данных SQL. Представления позволяют вам скрывать подробности структуры ваших таблиц, которые могут изменяться по мере модернизации вашего приложения, сохраняя старые интерфейсы.

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

3.3. Внешние ключи (Foreign Keys)

Посмотрите на таблицы weather и cities из Главы 2. На лицо следующая проблема: Вы хотите быть уверенными, что каждая из строк в таблице weather имеет соответствующую запись в таблице cities. Это называется обслуживанием ссылочной целостности ваших данных. В простейших СУБД вам потребовалось бы реализовать сперва просмотр таблицы cities, чтобы проверить существование нужной записи и только затем решить вставлять или отвергнуть вставку новых записей данных в таблицу weather. Такое решение создает некоторые проблемы и очень неудобно, так что PostgreSQL может сделать это для вас.

Новое описание этих таблиц будет выглядеть так:

CREATE TABLE cities (
 	city     varchar(80) primary key,
	 location point
);

CREATE TABLE weather (
	city      varchar(80) references cities,
	temp_lo   int,
	temp_hi   int,
	prcp      real,
	date      date
);

Теперь попытайтесь вставить неправильную запись:

INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');

ERROR:  <unnamed> referential integrity violation - key referenced from weather not found in cities

Поведение внешних ключей может быть прекрасно использовано вашим приложением. Мы не ограничиваемся только этим простым примером в данном учебнике, но отсылаем вас за подробностями к PostgreSQL User's Guide Правильное использование внешних ключей существенно увеличивает качество ваших приложений по работе с базами данных, так что мы очень рекомендуем вам научиться пользоваться ими.

3.4. Транзакции

Транзакции являются одним из фундаментальных концептов всех СУБД. Сущность транзакции состоит в связывании нескольких шагов в одну операцию по принципу все-или-ничего. Внутренние промежуточные состояния между шагами не видны для других конкурирующих транзакций и если во время выполнения транзакции случится ошибка, которая помешает транзакции завершится, то в базе данных никаких изменений сделано не будет.

Например, допустим, что есть база данных, которая содержит балансы для нескольких клиентов и общие депозитные балансы для филиалов. Предположим, что мы хотим внести поступление $100.00 от клиента Alice для клиента Bob. Простейшая команда, которая выполняет данную операцию может выглядеть так:

UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
UPDATE branches SET balance = balance - 100.00
    WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice');
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Bob';
UPDATE branches SET balance = balance + 100.00
    WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');

Делали этих команд сейчас не важны; важно что здесь мы имеем дело с несколькими отдельными обновлениями (операторы update), которые реализуют нужную нам операцию. Наши банковские работники захотят сделать так, чтобы все эти обновления происходили сразу или чтобы не происходило ни одно из них. Это обусловлено тем, что в результате какой-либо системной ошибки может получиться так, что Bob получит $100.00, которые не будут вычтены у Alice. Или может случиться так, что у Alice будет вычтена эта сумма, но Bob еЈ не получит. Нам нужна гарантия, что если что-либо пойдет не так во время операций обновления, счетов, то никаких изменений фактически внесено не будет. Такую гарантию можно получить, если сгруппировать операторы update в транзакцию. Транзакция является атомарным действием с точки зрения других транзакций и либо она завершится полностью успешно, либо никакие действия, составляющие транзакцию выполнены не будет.

Мы также хотим гарантировать, что одна полностью завершившаяся и подтверждЈнная СУБД транзакция является действительно сохранЈнной и не может быть потеряна, даже если после еЈ выполнения произойдет крах системы. Например, если мы сохраняем кэш перевода клиента Bob, мы не хотим, чтобы эти деньги клиента Bob потерялись в результате краха системы, который, например, может произойти в тот момент, когда Bob вышел за двери банка. Традиционные СУБД гарантируют что все обновления, осуществляемые в одной транзакции, протоколируются в надежное хранилище (т.е. на диск) перед тем как СУБД сообщит о завершении транзакции.

Другое важное свойство транзакционных СУБД состоит в строгой изоляции транзакций: когда несколько транзакций запускаются конкурентно, каждая из них не видит тех неполных изменений, которые производят другие транзакции. Например, если одна транзакция занята сложением всех балансов филиалов, она не должна учитывать как денег снятых со счета Alice так и денег пришедших на счет Bob. Таким образом транзакции должны выполнять принцип все-или-ничего не только в плане нерушимости тех изменений, которые они производят в базе данных, но и также в плане того, что они видят в момент работы. Обновления, которые вносит открытая транзакция являются невидимыми для других транзакций пока данная транзакция не завершиться, после чего все внесенные ей изменения станут видимыми.

В PostgreSQL транзакция - это список команд SQL, которые находятся внутри блока, начинающегося командой BEGIN и заканчивающегося командой COMMIT. Таким образом наша банковская транзакция будет выглядеть так:

BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
-- и т.д. ....
COMMIT;

Если во время выполнения транзакции мы решаем, что не хотим завершать еЈ (например мы получили извещение о том, что счет Alice отрицательный), то мы вместо команды COMMIT выдаем команду ROLLBACK и все наши изменения от начала транзакции, будут отменены.

PostgreSQL фактически считает каждый оператор SQL запущенным в транзакции. Если вы не указываете команду BEGIN, то каждый отдельный оператор имеет неявную команду BEGIN перед оператором и (при успешной отработке оператора) команду COMMIT после оператора. Группа операторов заключаемая в блок между BEGIN и COMMIT иногда называется транзакционным блоком.

CREATE TABLE capitals (
  name       text,
  population real,
  altitude   int,    -- (in ft)
  state      char(2)
);

CREATE TABLE non_capitals (
  name       text,
  population real,
  altitude   int     -- (in ft)
);

CREATE VIEW cities AS
  SELECT name, population, altitude FROM capitals
    UNION
  SELECT name, population, altitude FROM non_capitals;

CREATE TABLE cities (
  name       text,
  population real,
  altitude   int     -- (in ft)
);

CREATE TABLE capitals (
  state      char(2)
) INHERITS (cities);

SELECT name, altitude
  FROM cities
  WHERE altitude > 500;

   name    | altitude
-----------+----------
 Las Vegas |     2174
 Mariposa  |     1953
 Madison   |      845
(3 rows)
    

SELECT name, altitude
    FROM ONLY cities
    WHERE altitude > 500;

   name    | altitude
-----------+----------
 Las Vegas |     2174
 Mariposa  |     1953
(2 rows)