LXF87-88:PostgreSQL

Материал из Linuxformat.

Работа с базой

ЧАСТЬ 3: Вы уже неоднократно слышали, что PostgreSQL — это одна из самых мощных открытых СУБД, и сегодня Евгений Балдин расскажет вам, почему.

В этой статье мы предпримем попытку сделать краткий обзор возможностей PostgreSQL. Не надо иллюзий — «объять необъятное невозможно», поэтому многое из интересного осталось за кадром, но все же «попытка — не пытка».

Немного об основах

Применительно к базам данных часто упоминается принцип ACID: атомарность (Аtomicity), целостность (Consistency), локализация пользовательских процессов (Isolation) и устойчивость к ошибкам (Durability).

Для обеспечения совместной работы множества пользователей (concurrency) в целях следования заветам ACID, PostgreSQL использует систему управления версиями или MVCC (Multi-Version Concurrency Control). При подсоединении пользователя MVCC «подсовывает» ему собственную версию или мгновенный снимок (snapshot) базы данных. В этом случае изменения, производимые пользователем, невидимы другими пользователями до тех пор, пока текущая транзакция^[1] (transaction) не подтверждается (commit). Кроме проблем, связанных с ACID, многоверсионность позволяет уменьшить или даже, во многих случаях, исключить необходимость блокировки данных (locks) при чтении. Надежность^[2] (reliability) сохранения данных является одним из основных показателей качества СУБД. Сохранение измененных данных — очень нетривиальная процедура. Дело в том, что диски очень «ме-е-едленные», поэтому прежде чем попасть на диск, данные проходят через промежуточные буферы (cache), начиная от системного кэша файловой системы, заканчивая кэшем на самом диске. Никто не сможет гарантировать, что все в них положенное, в случае возникновения каких-либо проблем, окажется в безопасном постоянном хранилище. Для максимального уменьшения вероятности потери данных PostgreSQL использует журнал транзакций или Write Ahead Log (WAL). Прежде чем записать данные о проведенной транзакции на диск, информация об изменениях заносится в WAL. Если что-то случилось, то данные можно восстановить по журналу. Если данные не попали в журнал, то, соответственно, исчезнет вся транзакция — жалко, конечно, зато целостность не нарушается. Следствием использования WAL является отсутствие необходимости «скидывать» данные на диск с помощью fsync, так как достаточно убедиться, что записан WAL. Это значительно увеличивает производительность в многопользовательской среде с множеством мелких запросов на изменение данных, так как записать один последовательный файл WAL гораздо проще, чем изменять множество таблиц по всем диску. В качестве бонуса журнал транзакций позволяет организовать непрерывное резервное копирование данных (on-line backup) — мечту администратора и возможность «отката» базы данных на любой момент в прошлом (point-in-time recovery) — своеобразную машину времени.

Типы данных

SELECT «список искомых данных»
[ FROM «список источников получения данных» ]
[ WHERE «условное выражение» ]
[ GROUP BY «выражение» [, …]]
[ HAVING «условное выражение» [, …] ]
[ ORDER BY «выражение» [, …] ]
[ LIMIT «число» ]
[ OFFSET «число» ]

Как и положено базе данных, PostgreSQL поддерживает довольно много стандартных типов данных. Более того, пользователь может определять свои собственные типы данных, если он не найдет необходимых примитивов среди стандартных.

Числовые типы

Обычные числовые (numeric) типы представлены целыми числами в два (smallint), четыре (integer) или восемь (bigint) байт длиной, числа с плавающей точкой — в четыре (real) и восемь байт (double precision) длиной. Кроме обычных чисел, в случае real и double поддерживаются значения Infinity, -Infinity и NaN — бесконечность (), минус бесконечность (-) и «не число» (not-a-number), соответственно.

PostgreSQL поддерживает числа с произвольной точностью numeric(precision, scale), где precision — число всех знаков в определяемой величине, а scale — число знаков в дробной части. PostgreSQL позволяет выполнять действия без накопления ошибки с подобными величинами с точностью вплоть до 1000 знаков. Этим типом данных не следует злоупотреблять, так как операции над подобными числами занимают очень много времени.

Битовые поля представлены типами bit(size) — битовая строка фиксированной длины size и bit varying(size) — битовая строка переменной длины с ограничением по размеру size.

К числовым типам PostgreSQL относятся и «псевдотипы» serial и bigserial. Эти типы соответствуют типам integer и bigint, за исключением того, что при записи новых данных в таблицу с колонкой этого типа, значение по умолчанию в ней увеличивается на единицу — то есть это автоматически создаваемая упорядоченная последовательность.

Символьные типы

В стандарте SQL символьный тип определяется как строка определенной длины character(size), где size — длина строки. В дополнение к стандарту, PostgreSQL поддерживает строки переменной длины с ограничением varchar(size) и без ограничения — text.

Бинарные типы

Бинарную строку можно сохранить, используя тип bytea. SQL предполагает, что вся информация передается как текст, поэтому при передаче данных следует экранировать некоторые из символов.

В PostgreSQL есть специальный тип данных Large Objects. По сути дела, это просто способ сохранять любые файлы размером вплоть до 2 Гб прямо в базе данных. Операции с подобными объектами выходит за рамки SQL. Для доступа к Large Objects есть специальный программный интерфейс, смоделированный по образу и подобию обычного чтения/записи файла.

Типы даты/времени

Временем в PostgreSQL заведует тип timestamp или timestamp with time zone — он может хранить дату и время, начиная с 4713 г. до н. э. вплоть до 5874897 г., с точностью в одну микросекунду (μс), занимая восемь байт. Второй упомянутый тип включает часовой пояс и позволяет автоматически учитывать переход на летнее/зимнее время. С таким диапазоном и точностью проблема типа широко разрекламированной Y2K случится не скоро.

Разница между двумя датами представлена типом interval длиной в двенадцать байт, что позволяет хранить информацию о событиях, связанных даже с рождением и смертью Вселенной.

Также есть отдельный тип для календарного времени (date) и просто для времени (time или time with timezone).

PostgreSQL поддерживает множество способов ввода даты и времени. С моей точки зрения, в некоторых случаях СУБД проявляет излишний интеллект, поэтому рекомендую выбрать стандартный ISO, который выглядит примерно так:

test=> -- узнаем текущее время с точностью до секунды
test=> SELECT date_trunc(‘seconds’,timestamp WITH time zone ‘now’);
date_trunc
------------------------
2006-08-26 21:08:14+07

В таком случае ошибиться в порядке месяца и дня становится весьма затруднительно, независимо от того, какая локаль используется системой.

Для типа timestamp определены дополнительные константы:

• epoch — начало эпохи с точки зрения времени Unix (четырехбайтовый time_t): 1970-01-01 00:00:00+00;
• infinity — позже, чем любое возможное из времен;
• -infinity — раньше, чем любое возможное из времен;
• now — здесь и сейчас;
• today — сегодняшняя полночь; аналогично, yesterday — вчерашняя полночь и, tomorrow — завтрашняя полночь.

Логические типы

Логические типы представлены типом boolean. Логично, что он принимает значения либо TRUE (‘t’, ‘true’, ‘y’,’yes’, ‘1’) — «истина», либо FALSE (‘f’, ‘false’, ‘n’, ‘no’, ‘0’) — «ложь». Все просто, за исключением одного «но» — есть еще одна возможность: «значение не определено» (NULL). Собственно говоря, это не особенность типа boolean. При использовании SQL, с тем, что значение может быть не определено, необходимо считаться всегда и везде. Вот такая вот логика — вовсе не двоичная.

Остальные стандартные типы

К оставшимся стандартным типа относятся различные геометрические типы данных: точка (point), линия (line), отрезок (lseg), прямоугольник (box), путь (path), замкнутый путь (polygon) и окружность (circle). Для системных администраторов будут интересны стандартные типы сетевых IPv4 и IPv6-адресов (cidr или inet) и тип МАС-адреса (macaddr).

Более сложные типы реализуются как дополнения. Яркими примерами служат поддержка географических объектов GIS (http://postgis.refractions.net/) и иерархический тип данных ltree (contrib/ltree).

Определение пользовательских типов

Прежде всего следует упомянуть, что PostgreSQL поддерживает массивы. Можно создать массив определенного размера или безразмерный, на основе любого стандартного типа или типа, определенного пользователем. Поддерживаются многомерные массивы и операции над ними, например, «срезы».

test=> --- создаем массив для игры в «крестики-нолики»
test=> CREATE TABLE tictactoe (squares integer[3][3]);
test=> --- |x00| x = 1, 0 = -1
test=> --- |0xx| вставляем информацию о варианте игры
test=> --- | x| крестики начинают и выигрывают
test=> INSERT INTO tictactoe
test-> VALUES (‘{{1,-1,-1},{-1,1,1},{0,0,1}}’);
test=> --- распечатываем сохраненную позицию
test=> SELECT * FROM tictactoe ;
squares
------------------------------
{{1,-1,-1},{-1,1,1},{0,0,1}}
test=> -- распечатываем значение первого столбца
test=> SELECT squares[1:3][1:1] FROM tictactoe ;
squares
----------------
{{1},{-1},{0}}

Композитный тип (composite type) представляет из себя аналог структуры:

test=> CREATE TYPE complex AS (Re real,Im real);

В отличие от стандартных встроенных типов, использование композитного типа пока имеет некоторые ограничения. Например, из них нельзя создавать массивы.

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

Функции

Все стандартные типы имеют свои функции: ведь если есть тип, то с ним нужно работать. Стандартные функций многочисленны^[3] и разнообразны. Одних операторов поиска с использованием регулярных выражений — целых три штуки: собственное расширение PostgreSQL (LIKE и ILIKE), оператор, соответствующий стандарту SQL (SIMILAR TO) и POSIX-совместимый оператор (~ и ~*). Все, что только можно было быстро придумать, уже реализовано. А более сложные случаи, например, модуль для полнотекстового поиска tsearch2 (contrib/tsearch2) находятся в процессе совершенствования. Изобрести что-то, выходящее за рамки стандарта тяжело, но если это случится, вы всегда можете создать свою собственную функцию. При желании, ссылаясь на уже имеющуюся функцию, с помощью команды CREATE OPERATOR можно определить оператор для своих типов данных.

Хранимые процедуры

Для создания новых функций используется оператор CREATE FUNCTION, что вполне предсказуемо. Создаваемые таким образом функции исполняются и хранятся на сервере, отсюда и название — «хранимые процедуры»:

test=> -- Создаем и заполняем таблицу
test=> CREATE TABLE AplusB (A integer, B integer);
test=> INSERT INTO AplusB VALUES (1,1);
test=> INSERT INTO AplusB VALUES (2,2);
test=> INSERT INTO AplusB VALUES (3,3);
test=> -- Создаем новую функцию
test=> CREATE FUNCTION plus(integer, integer) RETURNS integer
test-> LANGUAGE SQL AS ‘SELECT $1 + $2;’;
CREATE FUNCTION
test=> SELECT A,B,plus(A,B) FROM AplusB;
a | b | plus
-----+-----+------
1 | 1 | 2
2 | 2 | 4
3 | 3 | 6
(записей: 3)

PostgreSQL поддерживает перегрузку функций: объектно-ориентированность имеет свои плюсы. Кроме SQL, для создания новых функций можно использовать процедурные языки программирования. Для начала работы с процедурным языком его необходимо инициализировать. По умолчанию, интерфейсы к языкам, отличным от SQL и C, недоступны по соображениям безопасности. Для инициализации языка используется команда createlang. Запустить ее может только администратор базы данных — тот, кто имеет право создавать базы:

# Инициализируем язык PL/pgSQL для базы данных test
> createlang plpgsql test
# делаем то же самое, но для языка PL/Perl
> createlang plperl test

Теперь можно создавать функции с использованием всех прелестей процедурного программирования, вместе с циклами, каковые по понятным причинам отсутствуют в SQL. Ниже продублирована простейшая функция, которая была описана выше, но теперь уже на PL/pgSQL и на PL/Perl:

test=> -- Создаем новую функцию с использование PL/pgSQL
test=> CREATE FUNCTION pgsql_plus(integer, integer) RETURNS integer
test-> LANGUAGE PLPGSQL AS ‘BEGIN RETURN $1+$2; END;’;
CREATE FUNCTION
test=> -- Создаем новую функцию с использование PL/Perl
test=> CREATE FUNCTION perl_plus(integer, integer) RETURNS integer
test-> LANGUAGE PLPERL AS ‘RETURN $_[0]+$_[1]’;
CREATE FUNCTION
test=> -- Проверяем, что все работает
test=> SELECT pgsql_plus(A,B) FROM AplusB;
test=> SELECT plus(A,B),pgsql_plus(A,B),perl_plus(A,B) FROM AplusB;
plus | pgsql_plus | perl_plus
------+------------+-----------
2 | 2 | 2
4 | 4 | 4
6 | 6 | 6
(записей: 3)

В стандартной документации подробно описаны идущие вместе с дистрибутивом языки: PL/pgSQL, PL/Tcl, PL/Perl, PL/Python и, естественно, C/C++ с SQL. Кроме перечисленных здесь, есть поддержка

• plPHP http://plphp.commandprompt.com/,
• PL/java http://gborg.PostgreSQL.org/project/pljava/projdisplay.php,
• PL/R http://www.joeconway.com/plr/,
• PL/Ruby http://raa.ruby-lang.org/project/pl-ruby,
• PL/sh http://plsh.projects.PostgreSQL.org/.

Также есть возможность подключения вашего любимого языка.

Триггеры

Обычно для решения несложных задач можно удовлетвориться сценарием: «что сказано — то и сделано», но в более сложных случаях от СУБД хотелось бы получать более сложные реакции в ответ на внешнее «раздражение». Для управления реакцией СУБД на изменение данных используются триггеры. Для создания триггера используется команда CREATE TRIGGER. Полное описание команды в форме Бэкуса-Наура таково:

CREATE TRIGGER «имя триггера»
{ BEFORE | AFTER } { «событие» [ OR ... ] }
ON «имя таблицы» [ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE «исполняемая функция - реакция»

Реакция на «событие», которое может быть вставкой (INSERT), изменением (UPDATE), или удалением (DELETE) может производится, по выбору, до (BEFORE) или после (AFTER) изменения данных. Выполнение процедуры может производиться для каждой записи (ROW) или для каждого запроса (STATEMENT). Для показательного примера создания триггера возьмем следующую выдуманную задачу: при изменении данных в описанной уже таблице AplusB сумма A и B должна автоматически обновляться в таблице ABresult. Следующее решение чрезвычайно неоптимально, зато работает:

test=> -- Создаем «результирующую» таблицу
test=> CREATE TABLE ABresult (result integer);
test=> -- Создаем функцию, очищающую ABresult и
test=> -- заполняющую все суммой A и B из AplusB.
test=> CREATE FUNCTION ABsumm() returns TRIGGER AS
test-> ‘BEGIN
test’> DELETE FROM ABresult;
test’> INSERT INTO ABresult VALUES (AplusB.A+AplusB.B);
test’> RETURN NULL;
test’> END;’
test-> LANGUAGE ‘plpgsql’;
test=> -- Создаем триггер
test=> CREATE TRIGGER makeABresult
test=> AFTER INSERT OR UPDATE OR DELETE ON AplusB
test=> FOR EACH STATEMENT execute procedure ABsumm();
CREATE TRIGGER
test=> -- Добавляем данных в таблицу AplusB
test=> INSERT INTO AplusB VALUES (100,200);
test=> -- проверяем, что триггер сработал
test=> SELECT * FROM AplusB,ABresult WHERE A+B=result;
a | b | result
-----+-----+--------
1 | 1 | 2
2 | 2 | 4
3 | 3 | 6
100 | 200 | 300
(записей: 4)

Rules

Кроме триггеров, PostgreSQL обладает еще одним способом управления реакции СУБД на запросы — это rules, или «правила». Для создания «правил» используется команда CREATE RULE. Основное отличие «правила» от триггера в том, что триггер — это реакция системы на изменение данных, а «правило» позволяет изменять сам запрос, в том числе и запрос на получение данных (SELECT). В частности, одно из довольно удобных расширений PostgreSQL — представление или виртуальная таблица (view), реализовано с помощью «правил».

Индексы

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

Создание индексов — это ответственность создателя БД. Создание индекса, как можно догадаться, производится с помощью команды

CREATE INDEX:
CREATE [ UNIQUE ] INDEX «имя индекса» ON TABLE [ USING «алгоритм»
]
( { «имя столбца» | ( «выражение» ) } [, ...] )
[ WHERE «условие» ]

Индекс может быть уникальным (UNIQUE). В этом случае при создании индекса и при добавлении данных накладывается дополнительное требование на уникальность параметра, по которому создается индекс.

При создании индекса можно выбрать алгоритм индексации. По умолчанию используется B-tree, но доступны также Hash, R-tree или GiST. Алгоритм GiST (http://www.sai.msu.su/~megera/postgres/gist/) был создан Олегом Бартуновым на пару с Федором Сигаевым. GiST является не просто еще одним алгоритмом — это целый конструктор, позволяющим создавать индексы для принципиально новых типов данных. В PostgreSQL 8.2 будут добавлены еще два метода: bitmap и GIN. Если судить по алгоритмам создания индексов, то PostgreSQL — это одна из самых продвинутых СУБД.

Индекс можно создавать по какому-то из столбцов — это самый простой метод. При указании нескольких колонок создаются многоколоночные индексы. Особо следует отметить возможность создания функциональных индексов — в качестве индекса указывается функция от данных таблицы. С помощью функциональных индексов можно реализовать еще один алгоритм индексации: Reverse index (обращает поле переменной — первый символ считается последним).

Условие (WHERE), накладываемое при создании индекса, позволяет создавать частичные индексы (partial indices). Это полезно в тех случаях, когда индексируемый столбец содержит большое число одинаковых значений, а поиск надо производить по редким «чужеродным» вкраплениям.

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

Целостность данных

Сохранить, записать, а затем быстро достать данные — вещь полезная, но как отследить, что они записаны правильно и без ошибок? Для этого необходимо постоянно следить за целостностью данных в условиях многопользовательской системы.

Транзакции

Транзакция — это единый блок операций, который нельзя разорвать. Блок либо выполняется целиком, либо все отменяется. В условиях параллельного доступа, PostgreSQL распространяет информацию об операциях только по завершению транзакции. Транзакция начинается с оператора BEGIN и заканчивается оператором COMMIT (подтверждение транзакции) или ROLLBACK (отмена транзакции). Возможен режим, когда каждый запрос сам себе является транзакцией: например, такой режим по умолчанию используется в pSQL. Для отмены этого режима достаточно набрать BEGIN. Неудобством при использовании транзакций является то, что в случае ошибки какого-то из запросов приходится отменять всю транзакцию. Для устранения этого недостатка в PostgreSQL 8.x были добавлены точки сохранения (savepoints).

test=> -- начинаем транзакцию
test=> BEGIN;
test=> -- здесь идет блок операторов, который удачно завершается
test=> -- ставим метку
test=> SAVEPOINT savepoint_one;
test=> -- здесь идет блок операторов, в котором произошла ошибка
test=> -- откатываемся до установленной метки,
test=> -- а не отменяем всю транзакцию
test=> ROLLBACK TO savepoint_one;
test=> -- повторяем последний блок
test=> -- завершаем транзакцию
test=> COMMIT;
test=> -- все, теперь изменения доступны всем

Ограничения

Целостность данных обеспечивает не только многоверсионностью (MVCC) PostgreSQL, но и «архитектором» таблиц базы данных. При создании таблицы (CREATE TABLE) или позже можно добавить ограничение (CONSTRAINT) на диапазон записываемых в таблицу данных. Ограничением могут быть как простые арифметические условные выражения, требования уникальности (UNIQUE или PRIMARY KEY), так и более сложные ограничения в виде внешних ключей (FOREIGN KEY).

Если какой-то столбец A является внешним ключом (FOREIGN KEY) по отношению к столбцу B (REFERENCES), то это означает, что только данные, представленные в столбце B, могут появиться в качестве значений столбца A. В случае внешних ключей PostgreSQL осуществляет автоматический контроль ссылочной целостности^[4]. Это довольно интересный механизм, который, в частности, позволяет моделировать иерархические структуры.

Блокировки

Поскольку в условиях параллельного доступа к базе данных каждый пользователь работает со своим мгновенным снимком (следствие MVCC), а не с самой БД, то в принципе можно придумать ситуацию, когда полученные данные «устаревают», так как они были изменены другим пользователем. Если это обстоятельство важно, то PostgreSQL предоставляет полный ассортимент блокировок. С помощью команды LOCK можно заблокировать таблицу, а инструкция SELECT FOR UPDATE позволяет заблокировать отдельные записи. Следует учитывать, что использование блокировок увеличивает шанс взаимной блокировки (deadlock). PostgreSQL умеет определять взаимные блокировки и разрешать их путем прекращения одной из транзакций, но на это уходит время.

Послесловие

Хотелось бы еще раз сказать, что «нельзя объять необъятное». Единственная проблема состоит в том, что конкретно это рассматриваемое нами «необъятное» уже «объято», поэтому за всеми подробностями следует обратиться к стандартной документации, а в качестве бонуса рекомендую хорошую обзорную статью от Олега Бартунова: «Что такое PostgreSQL?»:http://www.sai.msu.su/~Emegera/postgres/talks/what_is_PostgreSQL.html.

1. ↑ Транзакция представляет собой последовательность операций, которая обязана либо выполниться полностью, либо отмениться совсем, как будто это единое целое. При этом, независимо от других параллельно идущих транзакций (isolation), должна сохраняться целостность данных (consistency).
2. ↑ Этот момент отражен в FAQ fido7.ru.os.cmp следующим образом:
   Q51: Народ, а вы стабильным софтом пользоваться не пробовали?
   A51: Пробовали, но мэйнфреймы с дизель-генераторами не везде есть.
3. ↑ Их больше 1500. Полный список можно вывести, набрав в psql команду \df.
4. ↑ Ссылочная целостность — гарантированное отсутствие внешних ключей, ссылающихся на несуществующие записи в этой или других таблицах.
5. ↑ Один из вариантов расшифровки LAMP=Linux+Apache+MySQL+PHP.