СУБД Postgre SQL. История, современные возможности

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

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

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

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

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

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

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

Временем в PostgreSQL заведует тип timestamp или timestamp with time zone—может сохранить дату и время начиная с 4713 г. до н.э. вплоть до 5874897 г. с точностю в одну микросекунду (µс), занимает восемь байт. Второй упомянутый тип включает часовой пояс и позволяет автоматически учитывать переход на летнее/зимнее время. С таким диапазоном и точность проблема типа распиаренной «проблемы 2000 года» случится не скоро. Разница между двумя датами хранится в столбце типа interval —двенадцать байт, поэтому можно хранить информацию о событиях связанных с рождением и смертью вселенной.

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

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

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

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

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

epoch —начало эпохи с точки зрения юниксового времени (четырёхбайтовый

time_t)  1970-01-01 00:00:00+00

infinity —позже, чем любое возможное из времён,

infinity — раньше, чем любое возможное из времён,

now — здесь и сейчас,

today —сегодняшняя полночь, аналогично есть

yesterday —вчерашняя полночь и,

tomorrow —завтрашняя полночь.

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

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

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

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

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

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

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

db=> −−− создаём массив для игры Тик−Так db=> create table tictactoe ( squares integer [ 3 ] [ 3 ] ) ; db=> −−− |x00| x = 1 , 0 = −1 db=> −−− |0xx| вставляем информацию о варианте игры db=> −−− |x| крестики начинают и выигрывают db=> insert into tictactoe db−>values ( ’{{1,−1,−1},{−1,1,1},{0,0,1}} ’ ); db=> −−− распечатываем сохранённую позицию db=> select ∗ from tictactoe ; squares −− −−−−−−−−−−−−−−−−−−−−−−−−−−−− {{1,−1,−1},{−1,1,1},{0,0,1}} db=> −− распечатываем значение первого столбца db=> select squares [ 1 : 3 ] [ 1 : 1 ] from tictactoe ; squares −− −−−−−−−−−−−−−− {{1},{−1},{0}}

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

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

 

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

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

start="2"

  Функции.

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

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

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

db=> −− Создаём и заполняем таблицу db=> create table AplusB (A integer , B integer ); db=> insert INTO AplusB VALUES (1 ,1); db=> insert INTO AplusB VALUES (2 ,2); db=> insert INTO AplusB VALUES (3 ,3); db=> −−Создаём новую функцию db=> CREATE FUNCTION plus ( integer , integer ) RETURNS integer db−>LANGUAGE SQL as ’SELECT␣$1␣+␣$2; ’ ; CREATE FUNCTION db=> 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 для базы данных db > createlang plpgsql db # делаем то же самое , но для языка PL/Perl  > createlang plperl db

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

db=> −−Создаём новую функцию с использование PL/pgSQL db=>CREATE FUNCTION pgsql_plus ( integer , integer ) RETURNS integer db−>LANGUAGE PLPGSQL as ’BEGIN␣return␣$1+$2;␣END; ’ ; CREATE FUNCTION db=> −−Создаём новую функцию с использование PL/Perl db=>CREATE FUNCTION perl_plus ( integer , integer ) RETURNS integer db−>LANGUAGE PLPERL AS ’return␣$_[0]+$_[1] ’ ; CREATE FUNCTION db=> −−Проверяем , что всё работает db=>SELECT pgsql_plus (A,B) FROM AplusB ; db=>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. Кроме перечисленных есть поддержка.

PL/PHP 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/. 

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

2.2.2. Триггеры.

 Обычно, для решения несложных задач можно удовлетвориться сценарием: «что сказано —то и сделано», но в более сложных случаев от СУБД хотелось бы получать более сложные реакции на «раздражение». Для управления реакцией СУБД на изменение данных используются триггеры. Для создания триггера используется команда 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. Следующее решение чрезвычайно не оптимально, зато работает:

db=> −− Создаём «результирующую» таблицу db=> create table ABresult ( result integer ); db=> −− Создаём функцию, очищающую ABresult и db=> −− заполняющую всё суммой A и B из AplusB . db=> create function ABsumm() returns trigger as db−> ’BEGIN db’>                            delete from ABresult ; db’>                            insert into ABresult values (AplusB .A+AplusB .B); db’>                            return NULL; db’>  END;’ db−> language ’plpgsql ’ ; db=> −− Создаём триггер db=> CREATE TRIGGER makeABresult db=>AFTER INSERT or UPDATE or DELETE on AplusB db=>FOR EACH STATEMENT execute procedure ABsumm(); CREATE TRIGGER db=> −− Добавляем данных в таблицу AplusB db=> insert into AplusB VALUES (100 ,200); db=> −− проверяем , что триггер сработал db=> select ∗ from AplusB , ABresult where A+B=result ;       a      |      b     |       result −− −−−+−−−−−+−−−−−−−           1 |           1 |              2           2 |           2 |              4           3 |           3 |              6       100 |       200 |         300 (записей : 4)

 

2.2.3. Правила.

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

start="3"

  Индексы.

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

  Создание индексов —это ответственность создателя БД. Создание индекса, как можно догадаться, производится с помощью команды 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 является не просто ещё одним алгоритмом—это целый конструктор, позволяющий создавать индексы для принципиально новых типов данных. В версии 8.2 PostgreSQL опять же благодаря Олегу и Фёдору был добавлен ещё один метод GIN, а в 8.3 ожидается добавление bitmap-индекса. По алгоритмам создания индексов PostgreSQL одна из самых продвинутых СУБД.

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

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

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

start="4"

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

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