Журнал LinuxFormat - перейти на главную

LXF87-88:PostgreSQL

Материал из Linuxformat
Версия от 21:39, 28 июня 2008; Yaleks (обсуждение | вклад)

(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск
Подписка на печатную версию
Весь 2015 год (12 номеров)
Первое полугодие (6 номеров)
Второе полугодие (6 номеров)
Подписка на электронную версию
Весь 2015 год (12 номеров)
Первое полугодие (6 номеров)
Второе полугодие (6 номеров)
Подшивки старых номеров журнала (печатные версии)
Весь 2014 год (12 номеров)
Первое полугодие (6 номеров)
Второе полугодие (6 номеров)

Содержание

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

ЧАСТЬ 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) – своеобразную машину времени.

Типы данных

Как и положено базе данных, 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. Кроме перечисленных здесь, есть поддержка

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

Триггеры

Обычно для решения несложных задач можно удовлетвориться сце- нарием: «что сказано – то и сделано», но в более сложных случаях от СУБД хотелось бы получать более сложные реакции в ответ на внешнее «раздражение». Для управления реакцией СУБД на изменение данных используются триггеры. Для создания триггера используется команда 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: Пробовали, но мэйнфреймы с дизель-генераторами не везде есть.
Источник — «http://wiki.linuxformat.ru/wiki/index.php?title=LXF87-88:PostgreSQL&oldid=4907»
Персональные инструменты
купить
подписаться
Яндекс.Метрика