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

Хранимые процедуры в PostgreSQL могут быть написаны на любом из поддерживаемых встроенных языков. Хранимые процедуры могут быть использованы в триггерах и могут возвращать любой из поддерживаемых типов данных, а также массивы и списки.

Триггеры определяются как функции, которые инициируются операциями манипулирования. Триггеры могут быть назначены до или после операций INSERT, UPDATE или DELETE. Если произошло событие, на которое был назначен триггер, то вызывается закрепленная за этим триггером процедура. Например, операция INSERT может запускать триггер, проверяющий прибавленную запись на соответствии определенным условиям. При написании функций для триггеров могут использоваться разные языки программирования. Триггеры ассоциируются с таблицами и выполняются в алфавитном порядке.

В версии 9.0 введены триггеры на столбцы и, кроме того, при объявлении триггера можно использовать ключевое слово WHEN, которое добавляет дополнительное условие для срабатывания триггера.

Функции

Функции являются блоками кода, выполняемыми на сервере, а не на стороне клиента БД. Иногда функции отождествляются с хранимыми процедурами, однако между этими понятиями есть разница. Хотя они могут создаваться на чистом SQL, реализация дополнительной логики, например, условных переходов и циклов, выходит за рамки собственно SQL и требует использования некоторых языковых расширений. Функции могут писаться на одном из языков:

- встроенный процедурный язык PL/pgSQL, во многом аналогичный языку PL/SQL, что используется в СУБД Oracle;

- скриптовые языки - PL/Lua, PL/LOLCODE, PL/Perl, PL/PHP, PL/Python, PL/Ruby, PL/sh, PL/Tcl и PL/Scheme;

- классические языки - C, C + +, Java (через модуль PL/Java);

- статистический язык R (через модуль PL/R).

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

Начиная с 9.0, можно создавать функции без объявления имени (анонимные блоки) для выполнения блока операторов на любом встроенном языке, который поддерживает PostgreSQL, прямо в командной строке.

Типы данных

PostgreSQL поддерживает большой набор встроенных типов данных:

- численные типы;

- целые;

- с фиксированной точкой;

- с плавающей точкой;

- денежный (отличается специальным форматом вывода, а в остальном аналогичен числам с фиксированной точкой и двумя знаками после запятой);

- символьные типы произвольной длины;

- двоичные типы (включая большой двоичный объект BLOB - Binary Large Object);

- типы «дата/время» (полностью поддерживают разные форматы, точность, форматы вывода, включая последние изменения в часовых поясах);

- логический тип;

- перечислимый тип;

- геометрические примитивы;

- сетевые типы;

- IP і IPv6 –адреса;

- CIDR –формат. CIDR (Classless Inter-Domain Routing) - бесклассовая адресация - метод IP-адресации, позволяющий гибко управлять пространством IP-адресов, не используя жёстких рамок IP-адресации на основе классов сетей;

- МАС –адреса. Уникальный идентификатор, присваиваемый каждой единице сетевого оборудования;

- UUID тип. UUID (Universally Unique Identifier) – это стандарт идентификации, используемый при создании программного обеспечения. Наиболее распространённым использованием данного стандарта является Globally Unique Identifier (GUID) фирмы Microsoft;

- XML тип. XML (Xtensible Markup Language) - текстовый формат, предназначенный для хранения структурированных данных (взамен существующих файлов баз данных), для обмена информацией между программами, а также для создания на его основе более специализированных языков разметки;

- массивы;

- OID –типы. OID (Object identifiers) представляют идентификаторы различных объектов и используются обычно в PostgreSQL как первичные ключи для различных системных таблиц. Эти типы представляются как 4-байтовое целые числа без знака, т.е. имеют достаточно ограниченный диапазон значений, поэтому не могут использоваться в больших базах данных;

- композитные типы (composite type) представляет структуру ряда или записи, т.е. по существу список имен полей и их типов данных;

- псевдотипы (pseudo-types) не могут использоваться в качестве типа данных столбца таблицы или представления, но могут использоваться для объявления аргументов функции или типа результата.

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

Хотя большинство типов данных PostgreSQL взято непосредственно из стандартов SQL, существуют и другие, нестандартные типы данных (например, геометрические и сетевые типы). В таблице 1 перечислены основные базовые типы данных PostgreSQL, а также их синонимы (альтернативные имена).

Таблица 1 – Типы данных PostgreSQL

Тип данных	Описание	Стандарт
Логические и двоичные типы данных
boolean, bool	Отдельная логическая величина (true или false)	SQL99
bit(n)	Битовая последовательность фиксированной длины (n бит)	SQL92
bit varying(n),varbit(n)	Битовая последовательность переменной длины (до n бит)	SQL92
Символьные типы
character(n), char(n)	Символьная строка фиксированной длины (ровно n символов)	SQL89
character varying(n), varchar(n)	Символьная строка переменной длины (до n символов)	SQL92
text	Символьная строка переменной или неограниченной длины	PostgreSQL
Числовые типы
smallint, int2	2-байтовое целое со знаком	SQL89
integer, int, int4	4-байтовое целое со знаком	SQL92
bigint, int8	8-байтовое целое со знаком, до 18 цифр	PostgreSQL
real, float4	4-байтовое вещественное число	SQL89
double precision, floats, float	8-байтовое вещественное число	SQL89
numeric(p.s), decimal(p.s)	Число из р цифр, содержащее s цифр в дробной части	SQL99
money	Фиксированная точность, представление денежных величин	PostgreSQL, считается устаревшим
serial	4-байтовое целое с автоматическим приращением	PostgreSQL
Время и дата
date	Календарная дата (день, месяц и год)	SQL92
time	Время суток	SQL92
time with time zone	Время суток с информацией о часовом поясе	SQL92
timestamp	Дата и время	SQL92
interval	Произвольный интервал времени	SQL92
Геометрические типы
box	Прямоугольник на плоскости	PostgreSQL
line	Бесконечная линия на плоскости	PostgreSQL
Iseg	Отрезок на плоскости	PostgreSQL
circle	Круг с заданным центром и радиусом	PostgreSQL
path	Замкнутая или разомкнутая геометрическая фигура на плоскости	PostgreSQL
point	Точка на плоскости	PostgreSQL
polygon	Замкнутый многоугольник на плоскости	PostgreSQL
Сетевые типы
cidr	Спецификация сети IP	PostgreSQL
inet	Сетевой IP-адрес с необязательными битами подсети	PostgreSQL
macaddr	МАС-адрес (например, аппаратный адрес адаптера Ethernet)	PostgreSQL
Системные типы
oid	Идентификатор объекта (записи)	PostgreSQL
xid	Идентификатор транзакции	PostgreSQL

Помимо встроенных типов данных, пользователь может самостоятельно создавать новые необходимы ему типы и программировать для них механизмы индексирования c помощью методов GiST.

Пользовательские объекты

PostgreSQL может быть расширен пользователем для собственных потребностей практически в любом аспекте. Есть возможность добавлять:

- типы данных и их преобразования;

- домены;

- функции (включая агрегатные);

- индексы;

- операторы (включая переопределение уже существующих);

- процедурные языки.

Как и в SQL Server, данные в базе данных PostgreSQL организованы в нескольких разных объектах:

- домены (domains);

- конфигурация полнотекстового поиска (FTS configuration);

- словари полнотекстового поиска (FTS dictionaries);

- синтаксические анализаторы полнотекстового поиска (FTS parsers);

- шаблоны полнотекстового поиска (FTS templates);

- функции (functions);

- последовательности (sequences);

- таблицы (tables);

- триггеры (trigger functions);

- представления (views).

 

Выводы

В процессе выполнения лабораторной работы я изучил теоретические сведения о СУБД PostgreSQL, узнал важные свойства, возможности и преимущества этой СУБД.

Мне удалось установить последнюю на данный момент версию 9.5.1

 

Контрольные вопросы

1. Для чего предназначена и в чем суть технологии MVCC?

технология МVСС. MVCC (Multiversion Concurrency Control) используется в PostgreSQL для управления конкурентным доступом к данным на многовариантной основе. Эта технология позволяет предотвращать лишние блокировки (locking) операций чтения операциями, производящими обновление записей. PostgreSQL отслеживает все транзакции, выполняемые пользователями базы данных, что позволяет работать с записями без ожидания их освобождения. На практике это означает, что при запросе к БД каждая транзакция видит как бы снимок данных (версию) на момент этого снимка, а не текущее состояние данных. Таким образом, транзакции защищаются от просмотра незафиксированных данных, которые в данный момент могут только формироваться конкурентными транзакциями в тех же самых строках таблицы. Основное преимущество MMVC состоит в том, что чтение данных никогда не блокирует запись, а запись никогда не блокирует чтение.

MMVC позволяет избегать явного блокирования на уровне таблиц и отдельных записей, которое используется в традиционных СУБД, и, таким образом, минимизирует блокирование данных и увеличивает производительность в многопользовательских системах БД. Также реализовано отслеживание взаимных блокировок (deadlocks);

 

 

2. Что такое WAL?

сохраняющая регистрация WAL. WAL ( Write-Ahead Logging) является стандартным методом для обеспечения целостности данных. Сохраняющая регистрация - метод регистрации (журналирования) транзакций, при котором запись в журнале делается до записи данных. Используется также в MS SQL Server. Суть WAL заключается в том, что изменения в файлах данных (таблицы и индексы) должны быть внесены только после записи в журнал (log), в котором фиксируются эти изменения. Эта процедура позволяет не переписывать страницы данных на диске при каждой транзакции, так как в случае аварии мы сможем восстановить базу данных с помощью журнала. Механизм WAL обеспечивает следующие преимущества:

- повышение производительности работы СУБД за счет того, что записываются только внесенные изменения без переписывания всех данных в таблицах;

- повышение надежности храпения данных за счет предыдущего сохранения буферизованных данных в WAL;

возможность отката состояния БД на любой момент времени, путем применения WAL к существующей резервной копии;

 

1. Что представляет собой технология TOAST? В чем особенности ее реализации.