Установка расширения

Файлы с исходным кодом учебного расширение UOM (единицы измерения) расположены в каталоге uom домашнего каталога пользователя student:

student$ ls -l /home/student/uom
total 28
-rw-rw-r-- 1 student student  163 ноя 28  2018 Makefile
-rw-rw-r-- 1 student student 1132 ноя 28  2018 README.md
-rw-rw-r-- 1 student student 1644 ноя 28  2018 uom--1.0--1.1.sql
-rw-rw-r-- 1 student student  956 ноя 28  2018 uom--1.0.sql
-rw-rw-r-- 1 student student  755 ноя 28  2018 uom--1.1--1.2.sql
-rw-rw-r-- 1 student student 2794 ноя 28  2018 uom--1.2.sql
-rw-rw-r-- 1 student student   93 ноя 28  2018 uom.control

Расширение содержит только функции на SQL и не требует сборки. Makefile использует инфраструктуру PGXS для установки расширений:

student$ cat /home/student/uom/Makefile
EXTENSION = uom
DATA = uom--1.0.sql uom--1.2.sql uom--1.0--1.1.sql  uom--1.1--1.2.sql

PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

Если сервер PostgreSQL устанавливался из пакета, то дополнительно должен быть установлен пакет, поддерживающий установку расширений. Имя такого пакета обычно заканчивается на -devel. Пакет устанавливает утилиту pg_config и сборочные файлы инфраструктуры PGXS.

Если же сервер устанавливался из исходных кодов, то все необходимое для инфраструктуры PGXS уже есть.


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

student$ sudo make install -C /home/student/uom PG_CONFIG=/usr/local/pgsql/bin/pg_config
make: Entering directory '/home/student/uom'
/bin/mkdir -p '/usr/local/pgsql/share/extension'
/bin/mkdir -p '/usr/local/pgsql/share/extension'
/usr/bin/install -c -m 644 .//uom.control '/usr/local/pgsql/share/extension/'
/usr/bin/install -c -m 644 .//uom--1.0.sql .//uom--1.2.sql .//uom--1.0--1.1.sql .//uom--1.1--1.2.sql  '/usr/local/pgsql/share/extension/'
make: Leaving directory '/home/student/uom'

Теперь файлы расширения uom должны появиться в каталоге SHAREDIR/extension. Расположение SHAREDIR можно посмотреть утилитой pg_config или одноименной функцией SQL:

=> SELECT setting || '/extension' AS extension_dir 
    FROM pg_config() WHERE name = 'SHAREDIR';
          extension_dir           
----------------------------------
 /usr/local/pgsql/share/extension
(1 row)

Кроме uom здесь расположены файлы других установленных расширений.


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

=> SELECT name, default_version, installed_version
    FROM pg_available_extensions ORDER BY name;
        name        | default_version | installed_version 
--------------------+-----------------+-------------------
 adminpack          | 1.1             | 
 amcheck            | 1.0             | 
 autoinc            | 1.0             | 
 bloom              | 1.0             | 
 btree_gin          | 1.2             | 
 btree_gist         | 1.5             | 
 chkpass            | 1.0             | 
 citext             | 1.4             | 
 cube               | 1.2             | 
 dblink             | 1.2             | 
 dict_int           | 1.0             | 
 dict_xsyn          | 1.0             | 
 earthdistance      | 1.1             | 
 file_fdw           | 1.0             | 
 fuzzystrmatch      | 1.1             | 
 hstore             | 1.4             | 
 insert_username    | 1.0             | 
 intagg             | 1.1             | 
 intarray           | 1.2             | 
 isn                | 1.1             | 
 lo                 | 1.1             | 
 ltree              | 1.1             | 
 moddatetime        | 1.0             | 
 pageinspect        | 1.6             | 
 pg_buffercache     | 1.3             | 
 pg_freespacemap    | 1.2             | 
 pg_prewarm         | 1.1             | 
 pg_stat_statements | 1.6             | 
 pg_trgm            | 1.3             | 
 pg_visibility      | 1.2             | 
 pg_wait_sampling   | 1.1             | 
 pgcrypto           | 1.3             | 
 pgrowlocks         | 1.2             | 
 pgstattuple        | 1.5             | 
 plpgsql            | 1.0             | 1.0
 postgres_fdw       | 1.0             | 
 refint             | 1.0             | 
 seg                | 1.1             | 
 tablefunc          | 1.0             | 
 tcn                | 1.0             | 
 timetravel         | 1.0             | 
 tsm_system_rows    | 1.0             | 
 tsm_system_time    | 1.0             | 
 unaccent           | 1.1             | 
 uom                | 1.2             | 
(45 rows)


Посмотрим на расширение uom.

Список версий расширения:

=> SELECT name, version, installed FROM pg_available_extension_versions 
    WHERE name = 'uom' ORDER BY version;
 name | version | installed 
------+---------+-----------
 uom  | 1.0     | f
 uom  | 1.1     | f
 uom  | 1.2     | f
(3 rows)


Содержимое управляющего файла:

student$ cat /usr/local/pgsql/share/extension/uom.control
default_version = '1.2'
relocatable = true
encoding = UTF8
comment = 'Units of Measurement'

Версия по умолчанию 1.2, но мы начнем с 1.0:

=> CREATE DATABASE admin_extensions;
CREATE DATABASE
=> \c admin_extensions
You are now connected to database "admin_extensions" as user "postgres".
=> CREATE EXTENSION uom VERSION '1.0';
CREATE EXTENSION

uom появляется в списке созданных в текущей базе данных расширений:

=> \dx
                   List of installed extensions
  Name   | Version |   Schema   |           Description           
---------+---------+------------+---------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
 uom     | 1.0     | public     | Единицы измерения. uom--1.0.sql
(2 rows)


Расширение умеет переводить значения из одной единицы длины в другую:

=> SELECT uom2uom(1, 'верста', 'сажень') AS "Саженей в версте";
   Саженей в версте   
----------------------
 500.0000000000000000
(1 row)

Список поддерживаемых единиц измерения записан в таблице:

=> SELECT * FROM uom_ref;
  name  |    k    
--------+---------
 м      |       1
 км     |    1000
 см     |    0.01
 верста |  1066.8
 сажень |  2.1336
 аршин  |  0.7112
 вершок | 0.04445
(7 rows)


Справочная таблица и функция входят в состав расширения. В этом легко убедиться:

=> \dx+ uom
     Objects in extension "uom"
         Object description          
-------------------------------------
 function uom2uom(numeric,text,text)
 table uom_ref
(2 rows)


В файле SQL для версии 1.0 находятся команды создания таблицы и функции:

student$ cat /usr/local/pgsql/share/extension/uom--1.0.sql
\echo Use "CREATE EXTENSION uom" to load this file. \quit

COMMENT ON EXTENSION uom IS 'Единицы измерения. uom--1.0.sql';

-- Справочник единиц измерения
CREATE TABLE uom_ref (
    name       text,
    k          numeric
);
INSERT INTO uom_ref VALUES ('м',1), ('км',1000), ('см',0.01),
    ('верста',1066.8), ('сажень',2.1336), ('аршин',0.7112), ('вершок',0.04445);

-- Конвертируем значение из одной единицы длины в другую
CREATE FUNCTION uom2uom (value numeric, name_from text, name_to text)
    RETURNS numeric LANGUAGE SQL
AS $$ SELECT uom2uom.value *
          (SELECT k FROM uom_ref WHERE name = uom2uom.name_from) /
          (SELECT k FROM uom_ref WHERE name = uom2uom.name_to)     $$;

-- Необходимо для использования функции любым пользователем
GRANT SELECT ON uom_ref TO public;

Первая строка предотвращает выполнение файла напрямую из psql. Механизм расширений считает строки начинающиеся на \echo комментариями, а psql выдаст сообщение и закончит работу, благодаря \quit в конце.


Принадлежность к расширению не дает напрямую удалять объекты:

=> DROP FUNCTION uom2uom(numeric,text,text);
ERROR:  cannot drop function uom2uom(numeric,text,text) because extension uom requires it
HINT:  You can drop extension uom instead.

Но механизм расширений не отслеживает изменения объектов. Делать этого не следует, но добавление столбца в таблицу или изменение функции не вызовет ошибки.


Объекты расширения можно переносить в другую схему (relocatable=true):

=> CREATE SCHEMA util;
CREATE SCHEMA
=> ALTER EXTENSION uom SET SCHEMA util;
ALTER EXTENSION
=> \df util.uom*
                                     List of functions
 Schema |  Name   | Result data type |             Argument data types             |  Type  
--------+---------+------------------+---------------------------------------------+--------
 util   | uom2uom | numeric          | value numeric, name_from text, name_to text | normal
(1 row)

Вернемся в public:

=> ALTER EXTENSION uom SET SCHEMA public;
ALTER EXTENSION

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

В следующей версии 1.1 добавлен тип данных для единиц длины и операторы сравнения для значений этого типа.

Файл с изменениями:

student$ cat /usr/local/pgsql/share/extension/uom--1.0--1.1.sql
\echo Use "ALTER EXTENSION uom UPDATE TO '1.1'" to load this file. \quit

COMMENT ON EXTENSION uom IS 'Единицы измерения. uom--1.0--1.1.sql';

-- Новый тип данных
CREATE TYPE uom AS (
    value numeric,
    name text
);

-- Реализация операторов сравнения для типа
CREATE FUNCTION uom_cmp (a uom, b uom) RETURNS int LANGUAGE SQL
AS 'SELECT CASE WHEN a2b.value > b.value THEN  1 
                WHEN a2b.value < b.value THEN -1 
                ELSE 0 END
    FROM (SELECT uom2uom(a.value, a.name, b.name)) AS a2b(value)';

CREATE FUNCTION uom_lt(a uom, b uom) RETURNS boolean
AS 'SELECT uom_cmp(a, b) = -1' LANGUAGE sql;

CREATE FUNCTION uom_le(a uom, b uom) RETURNS boolean
AS 'SELECT uom_cmp(a, b) IN (-1,0)' LANGUAGE sql;

CREATE FUNCTION uom_eq(a uom, b uom) RETURNS boolean
AS 'SELECT uom_cmp(a, b) = 0' LANGUAGE sql;

CREATE FUNCTION uom_ge(a uom, b uom) RETURNS boolean
AS 'SELECT uom_cmp(a, b) IN (0,1)' LANGUAGE sql;

CREATE FUNCTION uom_gt(a uom, b uom) RETURNS boolean
AS 'SELECT uom_cmp(a, b) = 1' LANGUAGE sql;

CREATE OPERATOR <  (PROCEDURE = uom_lt, LEFTARG = uom, RIGHTARG = uom);
CREATE OPERATOR <= (PROCEDURE = uom_le, LEFTARG = uom, RIGHTARG = uom);
CREATE OPERATOR =  (PROCEDURE = uom_eq, LEFTARG = uom, RIGHTARG = uom);
CREATE OPERATOR >= (PROCEDURE = uom_ge, LEFTARG = uom, RIGHTARG = uom);
CREATE OPERATOR >  (PROCEDURE = uom_gt, LEFTARG = uom, RIGHTARG = uom);

CREATE OPERATOR CLASS uom_ops DEFAULT FOR TYPE uom USING btree AS
    OPERATOR 1 <,
    OPERATOR 2 <=,
    OPERATOR 3 =,
    OPERATOR 4 >=,
    OPERATOR 5 >,
    FUNCTION 1 uom_cmp(uom,uom);

При создании новой версии расширения возможны два сценария:


Убедимся, что мы можем перейти с версии 1.0 на версию 1.1. Список доступных вариантов обновления:

=> SELECT * FROM pg_extension_update_paths('uom') 
    WHERE path IS NOT NULL 
    ORDER BY source, target;
 source | target |     path      
--------+--------+---------------
 1.0    | 1.1    | 1.0--1.1
 1.0    | 1.2    | 1.0--1.1--1.2
 1.1    | 1.2    | 1.1--1.2
(3 rows)


Выполним обновление:

=> ALTER EXTENSION uom UPDATE TO '1.1';
ALTER EXTENSION
=> \dx uom
                  List of installed extensions
 Name | Version | Schema |             Description              
------+---------+--------+--------------------------------------
 uom  | 1.1     | public | Единицы измерения. uom--1.0--1.1.sql
(1 row)


Теперь будем создавать версию 1.1, предварительно удалив расширение. При удалении расширения удаляются и его объекты:

=> DROP EXTENSION uom;
DROP EXTENSION
=> \df uom*
                       List of functions
 Schema | Name | Result data type | Argument data types | Type 
--------+------+------------------+---------------------+------
(0 rows)


Создаем версию 1.1:

=> CREATE EXTENSION uom VERSION '1.1';
CREATE EXTENSION
=> \dx uom
                  List of installed extensions
 Name | Version | Schema |             Description              
------+---------+--------+--------------------------------------
 uom  | 1.1     | public | Единицы измерения. uom--1.0--1.1.sql
(1 row)

Для создания версии 1.1 механизм расширений сначала будет искать файл uom--1.1.sql, которого нет. Но к версии 1.1 можно прийти по цепочке обновлений: сначала создать версию 1.0, затем обновить до 1.1. Это самый короткий путь до версии 1.1, а в нашем примере и единственный.


Посмотрим на тип данных uom. Для него определены операторы сравнения единиц длины:

=> SELECT ( 5, 'аршин' )::uom <  ( 10, 'вершок')::uom,
          ( 1, 'верста')::uom <= (500, 'сажень')::uom,
          ( 1, 'сажень')::uom =  (  3, 'аршин' )::uom,
          ( 1, 'верста')::uom >= (500, 'сажень')::uom,
          (10, 'аршин' )::uom >  (  8, 'м'     )::uom;
 ?column? | ?column? | ?column? | ?column? | ?column? 
----------+----------+----------+----------+----------
 f        | t        | t        | t        | f
(1 row)


Новый тип можно использовать в качестве типа данных для столбца таблицы:

=> CREATE TABLE t (len uom);
CREATE TABLE
=> INSERT INTO t VALUES 
    ((3,'сажень')::uom), ((5,'м')::uom), ((17,'вершок')::uom), ((10,'аршин')::uom);
INSERT 0 4

Данные в таблице можно сортировать:

=> SELECT * FROM t ORDER BY len;
     len     
-------------
 (17,вершок)
 (5,м)
 (3,сажень)
 (10,аршин)
(4 rows)

И использовать индекс-B-дерево:

=> CREATE INDEX ON t USING btree (len);
CREATE INDEX
=> SET enable_seqscan TO off;
SET
=> EXPLAIN (COSTS OFF) SELECT * FROM t ORDER BY len;
              QUERY PLAN              
--------------------------------------
 Index Only Scan using t_len_idx on t
(1 row)


В следующей версии расширения - 1.2 - добавлен вызов функции pg_extension_config_dump.

Теперь pg_dump будет выгружать содержимое таблицы uom_ref. Добавленный в таблицу столбец predefined нужен для определения строк, которые были вставлены в таблицу не скриптом расширения, а уже после создания.

Файл обновления:

student$ cat /usr/local/pgsql/share/extension/uom--1.1--1.2.sql
\echo Use "ALTER EXTENSION uom UPDATE TO '1.2'" to load this file. \quit

COMMENT ON EXTENSION uom IS 'Единицы измерения. uom--1.1--1.2.sql';

-- Добавим признак, что строка добавлена при установке расширения.
ALTER TABLE uom_ref ADD COLUMN predefined boolean; 

UPDATE uom_ref SET predefined = true;

ALTER TABLE uom_ref 
    ALTER COLUMN predefined SET NOT NULL,
    ALTER COLUMN predefined SET DEFAULT false;

-- Если справочник будет пополняться при эксплуатации расширения,
-- то pg_dump должен выгружать новые строки.
SELECT pg_extension_config_dump('uom_ref'::regclass, 'WHERE NOT predefined');

Для перехода на версию 1.2 мы не можем просто удалить расширение и пересоздать его. Помешает столбец len таблицы t:

=> DROP EXTENSION uom;
ERROR:  cannot drop extension uom because other objects depend on it
DETAIL:  table t column len depends on type uom
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Если мы не готовы удалять таблицу (или столбец len), то следует выполнить обновление:

=> ALTER EXTENSION uom UPDATE;
ALTER EXTENSION

Без указания версии обновление выполняется до версии по умолчанию из управляющего файла:

=> \dx uom
                  List of installed extensions
 Name | Version | Schema |             Description              
------+---------+--------+--------------------------------------
 uom  | 1.2     | public | Единицы измерения. uom--1.1--1.2.sql
(1 row)

Конец демонстрации.