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

Файлы с исходным кодом учебного расширение 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)

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