Уровень 3.0

Предусловия:

Изучена лекция «Управление расширениями»

Управление расширениями

Откройте терминал и перейдите в режим выполнения команд от имени пользователя postgres:
```
[student@ServerName ~]$ sudo -iu postgres
```
Смените текущий каталог на каталог хранения файлов расширений:
```
[postgres@ServerName ~]$ cd $(pg_config --sharedir)/extension
```
Вспомним, что каталог с расширениями (extension) находится в каталоге с общими данными Pangolin. Директорию каталога в общих данных можно определить с использованием функции pg_config --sharedir.

Посмотрите путь к текущему каталогу:

[postgres@ServerName extension]$ pwd

/usr/pangolin-major.minor/share/extension

Проверьте в текущем каталоге наличие файлов для какого-либо расширения, например pg_stat_statements:
```
[postgres@ServerName extension]$ ls -1 pg_stat_statements*
```
```
pg_stat_statements--1.0--1.1.sql
pg_stat_statements--1.1--1.2.sql
pg_stat_statements--1.2--1.3.sql
pg_stat_statements--1.3--1.4.sql
pg_stat_statements--1.4--1.5.sql
pg_stat_statements--1.4.sql
pg_stat_statements--1.5--1.6.sql
pg_stat_statements--1.6--1.7.sql
pg_stat_statements--1.7--1.8.sql
pg_stat_statements--1.8--1.9.sql
pg_stat_statements--1.9--1.10.sql
pg_stat_statements.control
```
Вспомним, что в каталоге с расширениями должны находиться управляющие файлы (pg_stat_statements.control), а также файлы-скрипты версий (pg_stat_statements--1.4.sql) и файлы-скрипты обновлений (например, pg_stat_statements--1.4--1.5.sql), если их директория не переопределена в управляющем файле.

Создание расширения

Создайте файл-скрипт версии для нового расширения:

[postgres@ServerName extension]$ cat << EOF > weekend_revenue--1.0.sql
\echo Use "CREATE EXTENSION weekend_revenue" to load this file. \quit
COMMENT ON EXTENSION weekend_revenue IS 'Тип данных - выручка за выходные. Версия 1.0.';

--пользовательский тип данных
CREATE TYPE weekend_revenue AS (saturday_revenue numeric, sunday_revenue numeric);

--опорная функция сравнения значений типа данных
CREATE FUNCTION weekend_revenue_cmp (a weekend_revenue, b weekend_revenue) RETURNS int
AS
        'SELECT CASE WHEN a.saturday_revenue + a.sunday_revenue > b.saturday_revenue + b.sunday_revenue THEN 1
                WHEN a.saturday_revenue + a.sunday_revenue < b.saturday_revenue + b.sunday_revenue THEN -1
                ELSE 0 END;'
LANGUAGE SQL STRICT IMMUTABLE;

--функции для операторов сравнения
CREATE FUNCTION weekend_revenue_lt(a weekend_revenue, b weekend_revenue) RETURNS boolean
        AS 'SELECT weekend_revenue_cmp(a, b) = -1;' LANGUAGE SQL;
CREATE FUNCTION weekend_revenue_le(a weekend_revenue, b weekend_revenue) RETURNS boolean
        AS 'SELECT weekend_revenue_cmp(a, b) IN (-1,0);' LANGUAGE SQL;
CREATE FUNCTION weekend_revenue_eq(a weekend_revenue, b weekend_revenue) RETURNS boolean
        AS 'SELECT weekend_revenue_cmp(a, b) = 0;' LANGUAGE SQL;
CREATE FUNCTION weekend_revenue_ge(a weekend_revenue, b weekend_revenue) RETURNS boolean
        AS 'SELECT weekend_revenue_cmp(a, b) IN (0,1);' LANGUAGE SQL;
CREATE FUNCTION weekend_revenue_gt(a weekend_revenue, b weekend_revenue) RETURNS boolean
        AS 'SELECT weekend_revenue_cmp(a, b) = 1;' LANGUAGE SQL;

--операторы сравнения
CREATE OPERATOR <  (PROCEDURE = weekend_revenue_lt, LEFTARG = weekend_revenue, RIGHTARG = weekend_revenue);
CREATE OPERATOR <= (PROCEDURE = weekend_revenue_le, LEFTARG = weekend_revenue, RIGHTARG = weekend_revenue);
CREATE OPERATOR =  (PROCEDURE = weekend_revenue_eq, LEFTARG = weekend_revenue, RIGHTARG = weekend_revenue);
CREATE OPERATOR >= (PROCEDURE = weekend_revenue_ge, LEFTARG = weekend_revenue, RIGHTARG = weekend_revenue);
CREATE OPERATOR >  (PROCEDURE = weekend_revenue_gt, LEFTARG = weekend_revenue, RIGHTARG = weekend_revenue);
EOF

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

Тип данных предназначен для консолидированного хранения информации о выручке в выходные дни. Его определяют следующие поля:

saturday_revenue — выручка в субботу
sunday_revenue — выручка в воскресенье

Сравнение значений созданного типа данных выполняется путем сравнения сумм выручки за выходные дни каждого из сравниваемых значений типа.

Для задания правила сравнения в состав расширения включена опорная функция weekend_revenue_cmp.

На основе опорной функции в расширении созданы функции для операторов сравнения и, наконец, сами операторы сравнения.

Также в скрипте имеется пара команд \echo и \quit для исключения его ручного выполнения в psql, а также команда создания комментария к расширению (COMMENT ON EXTENSION).

Скрипт с SQL-командами для создания указанных объектов записан в файл weekend_revenue--1.0.sql каталога с расширениями, где "1.0" является именем версии расширения. Вспомним, что версия расширения — это не более чем его текстовый идентификатор.

Создайте управляющий файл для расширения:
```
[postgres@ServerName extension]$ cat << EOF > weekend_revenue.control
# weekend_revenue extension
comment = 'weekend_revenue data type'
default_version = '1.0'
encoding = UTF8
relocatable = true
EOF
```
Управляющий файл weekend_revenue.control размещен в том же каталоге, что и созданный файл-скрипт. В нем явно указаны следующие параметры:
- comment — комментарий к расширению
- default_version — версия по умолчанию, которая будет использована командами CREATE EXTENSION и ALTER EXTENSION UPDATE без явного указания версии
- encoding — кодировка символов в файлах-скриптах. В этом случае ее указание потребовалось, поскольку в файле-скрипте используется кириллица
- relocatable — возможность перемещения расширения между схемами базы данных
Подключитесь в psql к базе данных postgres с ролью postgres:
```
[postgres@ServerName extension]$ psql
```
```
psql (15.5)
Type "help" for help.
```

Создайте базу данных extension_db и подключитесь к ней с ролью postgres:

postgres=# CREATE DATABASE extension_db;

CREATE DATABASE

postgres=# \c extension_db

You are now connected to database "extension_db" as user "postgres".

Убедитесь, что после создания файлов расширение стало доступно для установки в базе данных:
```
extension_db=# SELECT * FROM pg_available_extensions WHERE name = 'weekend_revenue'\gx
```
```
-[ RECORD 1 ]-----+--------------------------
name              | weekend_revenue
default_version   | 1.0
installed_version |
comment           | weekend_revenue data type
```
Представление pg_available_extensions на основе информации из управляющих файлов определяет доступные для установки в базе данных расширения.
Установите расширение в базе данных:
```
extension_db=# CREATE EXTENSION weekend_revenue;
```
```
CREATE EXTENSION
```
Посмотрите с помощью метакоманды \dx+ состав установленного расширения:
```
extension_db=# \dx+ weekend_revenue
```
```
            Objects in extension "weekend_revenue"
                      Object description
---------------------------------------------------------------
 function weekend_revenue_cmp(weekend_revenue,weekend_revenue)
 function weekend_revenue_eq(weekend_revenue,weekend_revenue)
 function weekend_revenue_ge(weekend_revenue,weekend_revenue)
 function weekend_revenue_gt(weekend_revenue,weekend_revenue)
 function weekend_revenue_le(weekend_revenue,weekend_revenue)
 function weekend_revenue_lt(weekend_revenue,weekend_revenue)
 operator <(weekend_revenue,weekend_revenue)
 operator <=(weekend_revenue,weekend_revenue)
 operator =(weekend_revenue,weekend_revenue)
 operator >(weekend_revenue,weekend_revenue)
 operator >=(weekend_revenue,weekend_revenue)
 type weekend_revenue
(12 rows)
```
Информация обо всех объектах из файла-скрипта теперь сохранена в системных каталогах.

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

В том числе, информация об объектах из состава расширения была записана в следующие системные каталоги:
- pg_type — информация о типах данных
- pg_class — информация об отношениях
- pg_attribute — информация о столбцах таблицы
- pg_proc — информация о функциях и процедурах
- pg_operator — информация об операторах
Информация о самом расширении как объекте базы данных была записана в системный каталог pg_extension.

Для примера посмотрим, какая информация хранится об одном из созданных операторов сравнения.
Посмотрите в системных каталогах, какая информация хранится об одном из операторов из состава расширения, например >:
```
extension_db=# SELECT o.oprname, o.oprcode, p.proargnames,
p.proargtypes[0]::regtype a_type, p.proargtypes[1]::regtype b_type,
p.prorettype::regtype, p.prosrc
FROM pg_operator o JOIN pg_proc p ON o.oprcode=p.oid
WHERE oprname = '>' AND oprleft::regtype = 'weekend_revenue'::regtype\gx
```
```
-[ RECORD 1 ]--------------------------------------
oprname     | >
oprcode     | weekend_revenue_gt
proargnames | {a,b}
a_type      | weekend_revenue
b_type      | weekend_revenue
prorettype  | boolean
prosrc      | SELECT weekend_revenue_cmp(a, b) = 1;
```
Видно, что в системных каталогах хранится вся необходимая информация для выполнения операции сравнения > для созданного типа данных, включая исходный код на языке SQL (поле prosrc системного каталога pg_proc).
Удалите какой-нибудь объект из состава расширения, например тот же оператор '>':
```
extension_db=# DROP OPERATOR > (weekend_revenue, weekend_revenue);
```
```
ERROR:  cannot drop operator >(weekend_revenue,weekend_revenue) because extension weekend_revenue requires it
HINT:  You can drop extension weekend_revenue instead.
```
Удаление отдельного объекта из состава расширения невозможно. За этим следит механизм расширений.

Проверка созданного расширения

Создайте простую таблицу со столбцом типа weekend_revenue:
```
extension_db=# CREATE TABLE revenues(id integer, revenue weekend_revenue);
```
```
CREATE TABLE
```
Вставьте три строки в созданную таблицу:
```
extension_db=# INSERT INTO revenues
VALUES (1, (500,100)::weekend_revenue),
(2, (300,700)::weekend_revenue),
(3, (200,900)::weekend_revenue);
```
```
INSERT 0 3
```
Для указания значения составного типа используется конструкция приведения типов данных вида (300,700)::weekend_revenue, где в скобках последовательно указываются значения полей составного типа.
Для каждой строки сравните значение столбца revenue со значением "(500,500)":
```
extension_db=# SELECT id, (revenue).saturday_revenue, (revenue).sunday_revenue,
revenue < (500,500)::weekend_revenue AS "< (500,500)",
revenue = (500,500)::weekend_revenue AS "= (500,500)",
revenue > (500,500)::weekend_revenue AS "> (500,500)"
FROM revenues;
```
```
 id | saturday_revenue | sunday_revenue | < (500,500) | = (500,500) | > (500,500)
----+------------------+----------------+-------------+-------------+-------------
  1 |              500 |            100 | t           | f           | f
  2 |              300 |            700 | f           | t           | f
  3 |              200 |            900 | f           | f           | t
(3 rows)
```
Операторы сравнения отработали корректно, сравнивая суммы выручек за выходные.

Если бы операторы сравнения не были реализованы для созданного типа данных, сравнение значений выполнялось бы на основе лексикографического порядка.

Например, '(200,900)' < '(500,500)', что, конечно, не соответствует заложенной логике работы с типом данных.
Отсортируйте строки в таблице по возрастанию значений столбца revenue:
```
extension_db=# SELECT *, (revenue).saturday_revenue + (revenue).sunday_revenue AS weekend_sum
FROM revenues ORDER BY revenue;
```
```
 id |  revenue  | weekend_sum
----+-----------+-------------
  3 | (200,900) |        1100
  2 | (300,700) |        1000
  1 | (500,100) |         600
(3 rows)
```
Однако сортировка выполнилась не так, как ожидалось. Значения отсортированы в лексикографическом порядке.

Дело в том, что правила сортировки определяются еще одним объектом базы данных под названием «класс операторов».

Класс операторов — это набор операторов и вспомогательных функций, используемых индексными методами доступа для связи с типами данных.

Например, для реализации упорядочения значений в индексе типа «B-дерево» в классе операторов должны как раз содержаться операторы сравнения и опорная функция сравнения.

При этом для выполнения сортировки при последовательном сканировании (без использования индекса) используется тот же класс операторов, что и для метода индексного доступа типа «B-дерево».

Исправим обнаруженную ошибку путем создания необходимого класса операторов в новой версии расширения.

Обновление расширения

Выйдите из сеанса psql:
```
extension_db=# \q
```
Создайте файл-скрипт обновления расширения:
```
[postgres@ServerName extension]$ cat << EOF > weekend_revenue--1.0--1.1.sql
\echo Use "ALTER EXTENSION weekend_revenue UPDATE TO '1.1'" to load this file. \quit
COMMENT ON EXTENSION weekend_revenue IS 'Тип данных - выручка за выходные. Версия 1.1.';

CREATE OPERATOR CLASS weekend_revenue_ops DEFAULT FOR TYPE weekend_revenue USING btree AS
    OPERATOR 1 <,
    OPERATOR 2 <=,
    OPERATOR 3 =,
    OPERATOR 4 >=,
    OPERATOR 5 >,
    FUNCTION 1 weekend_revenue_cmp(weekend_revenue,weekend_revenue);
EOF
```
В переходном скрипте от версии "1.0" к версии "1.1" (weekend_revenue--1.0--1.1.sql) имеется команда создания класса операторов.

При этом при его создании задействованы объекты (тип данных weekend_revenue, операторы сравнения и опорная функция), команды по созданию которых имеются в файле-скрипте для версии "1.0".
Измените в управляющем файле версию расширения по умолчанию:
```
[postgres@ServerName extension]$ sed -i 's/1\.0/1\.1/g' weekend_revenue.control
```
```
[postgres@ServerName extension]$ cat weekend_revenue.control
```
```
# weekend_revenue extension
comment = 'weekend_revenue data type'
default_version = '1.1'
encoding = UTF8
relocatable = true
```
Теперь при создании или обновлении расширения без указания версии будет выбрана актуальная версия с именем "1.1".
Подключитесь в psql к базе данных extension_db с ролью postgres:
```
[postgres@ServerName extension]$ psql -d extension_db
```
```
psql (15.5)
Type "help" for help.
```

Проверьте доступные версии расширения:

extension_db=# SELECT name, version, installed, comment
FROM pg_available_extension_versions WHERE name = 'weekend_revenue';

      name       | version | installed |          comment
-----------------+---------+-----------+---------------------------
 weekend_revenue | 1.0     | t         | weekend_revenue data type
 weekend_revenue | 1.1     | f         | weekend_revenue data type
(2 rows)

Представление pg_available_extension_versions позволяет узнать версии расширения, доступные для установки, а также какая из них уже установлена (installed).

Проверьте возможность обновления расширения с версии "1.0" до версии "1.1":
```
extension_db=# SELECT * FROM pg_extension_update_paths('weekend_revenue');
```
```
 source | target |   path
--------+--------+----------
 1.0    | 1.1    | 1.0--1.1
 1.1    | 1.0    |
(2 rows)
```
С помощью функции pg_extension_update_paths можно узнать, с какой версии на какую возможно обновление.

Если обновление возможно, то поле path содержит цепочку, по которой будут использованы файлы-скрипты для обновления.

В этом случае обновление с версии "1.0" до версии "1.1" возможно, а в обратную сторону нет, поскольку нет соответствующего файла-скрипта обновления.

Еще раз обратим внимание на то, что версия расширения определяется текстовым именем, необходимым только для ее идентификации.
Обновите расширение:
```
extension_db=# ALTER EXTENSION weekend_revenue UPDATE TO '1.1';
```
```
ALTER EXTENSION
```
В этом случае была явно указана версия, до которой необходимо обновиться.

Однако с учетом того, что имя версии было записано в управляющий файл, в команде ALTER EXTENSION можно было ее не указывать.

Проверьте, какая версия расширения теперь установлена:

extension_db=# \dx weekend_revenue

                            List of installed extensions
      Name       | Version | Schema |                  Description
-----------------+---------+--------+-----------------------------------------------
 weekend_revenue | 1.1     | public | Тип данных - выручка за выходные. Версия 1.1.
(1 row)

Расширение успешно обновилось до версии "1.1".

Снова отсортируйте строки в таблице по возрастанию значений столбца revenue:

extension_db=# SELECT *, (revenue).saturday_revenue + (revenue).sunday_revenue AS weekend_sum
FROM revenues ORDER BY revenue;

 id |  revenue  | weekend_sum
----+-----------+-------------
  1 | (500,100) |         600
  2 | (300,700) |        1000
  3 | (200,900) |        1100
(3 rows)

Теперь данные типа weekend_revenue сортируются в правильном порядке.

Завершение

Подключитесь к базе данных postgres:

extension_db=# \c postgres

You are now connected to database "postgres" as user "postgres".

Удалите базу данных extension_db и завершите сеанс psql:
```
postgres=# DROP DATABASE extension_db;
```
```
DROP DATABASE
```
```
postgres=# \q
```

Самопроверка

Вопрос 1

Список файлов, определяющих расширение some_extension, представлен ниже:

/usr/pangolin-6.5/share/extension/some_extension--1.0.sql
/usr/pangolin-6.5/share/extension/some_extension--1.1.sql
/usr/pangolin-6.5/share/extension/some_extension--1.0--1.3.sql
/usr/pangolin-6.5/share/extension/some_extension--1.1--1.4.sql
/usr/pangolin-6.5/share/extension/some_extension--1.3--1.2.sql
/usr/pangolin-6.5/share/extension/some_extension.control

До какой версии может быть обновлено установленное расширение версии "1.0"? Выберите все верные варианты ответа

1.1

1.2

1.3

1.4

Вопрос 2

В psql выполнена следующая последовательность команд:

extension_db=# SELECT * FROM pg_available_extensions WHERE name = 'some_extension';
name       | default_version | installed_version |          comment
-----------------+-----------------+-------------------+---------------------------
some_extension  | 1.2             | 1.0               | some extension
(1 row)

extension_db=# SELECT * FROM pg_extension_update_paths('some_extension');
source | target |   path
--------+--------+----------
1.0    | 1.1    | 1.0--1.1
1.1    | 1.0    |
(2 rows)

extension_db=# ALTER EXTENSION some_extension UPDATE;

Каким будет результат выполнения последней команды?

Расширение "some_extension" будет обновлено до версии "1.1"Расширение "some_extension" будет обновлено до версии "1.2"Ошибка выполнения команды

Вопрос 3

Какие объекты базы данных должны быть включены в состав расширения, содержащего пользовательский тип данных, для обеспечения возможности сортировки данных указанного типа в соответствии с заданными правилами? Выберите все верные варианты ответа:

Операторы сравнения

Функции для операторов сравнения

Метод сортировки

Класс операторов

Семейство операторов

Опорная функция сравнения

Управление расширениями​

Создание расширения​

Проверка созданного расширения​

Обновление расширения​

Завершение​

Самопроверка​