Расширяем возможности PostgreSQL

* лого создано с помощью GigaChat

Всем SQL !

В одной старой своей публикации я показывал, как можно расширить возможности MS SQL с помощью хранимых процедур. Теперь покажу, как можно сделать подобное на продвинутом PostgreSQL. Это моя самая любимая реляционная СУБД, с ней я с 2013-го года, когда это ещё не было мейнстримом. Да и вообще, PostgreSQL - СУБД с большой историей, многогранным функционалом, мощными возможностями, разнообразными дополнениями, открытым кодом, мировым сообществом. А в свете импортозамещения последних тенденций нашего времени остаётся единственной СУБД, которой можно доверять. И даже большие дяди компании переходят на неё.

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

Перед началом наших экспериментов давайте создадим произвольную базу с именем, например, «py_base». Откроем любой менеджер для управления СУБД (платный, бесплатный, какой вам больше нравится, без разницы) или консоль с psql для самых суровых (я использую pgAdmin) и будем писать там свои скрипты.

Мы можем разрабатывать пользовательские методы на привычном нам SQL диалекте.

Допустим, у нас в базе есть таблица «journal» с полями id (автоинкремент), time, msg, созданная примерно по такому сценарию:

CREATE TABLE IF NOT EXISTS journal
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
    "time" timestamp with time zone NOT NULL,
    msg text,
    CONSTRAINT journal_pkey PRIMARY KEY (id)
        USING INDEX TABLESPACE zfs
)

TABLESPACE zfs;

ALTER TABLE IF EXISTS journal
    OWNER to postgres;

У меня база располагается в отдельном табличном пространстве на ZFS пуле, но вы на это не смотрите. [Кстати, если интересует тема про хранение баз на ZFS со всеми его плюшками, пишите в комментах.]

Напишем и выполним следующий скрипт:

CREATE OR REPLACE PROCEDURE log (
  message varchar(1000)
)
LANGUAGE SQL
AS $$
insert into journal (time, msg) 
values (now(), message);
$$;

Этот скрипт создаст процедуру, которая будет вставлять одну запись с переданным вами сообщением в таблицу. Обратите внимание на LANGUAGE SQL. Между двойными знаками доллара расположено тело метода, представляющее собой простой sql-скрипт. Попробуем воспользоваться новой процедурой:

call log('Hello');

Проверим, создалось ли что-либо в нашей таблице:

Как видим, запись добавилась.

Теперь создадим функцию, тоже с SQL телом:

CREATE OR REPLACE FUNCTION info (
)
RETURNS text
LANGUAGE SQL
AS $$
  select version() || '; ' || setting from pg_settings where name = 'data_directory';
$$;

Скрипт внутри функции выбирает версию Постгреса и конкатенирует её с путём, по которому располагаются данные данного инстанса ($PGDATA).

Обратите внимание, что процедура - это функция, которая НЕ возвращает значение. А в данном нашем текущем примере у нас есть блок RETURNS с указанием конкретного типа возвращаемого значения.

Проверим работоспособность функции и что она может нам вернуть:

select info();

Получаем результат выполнения нашей функции:

Усложняемся. Постгрес хорош тем, что помимо пользовательских методов на SQL-языке мы можем создавать свои собственные функции и процедуры на PL/SQL. Это процедурный язык программирования, расширяющий возможности стандартного SQL. В нём у нас уже появляются более продвинутые возможности для создания более сложных алгоритмов.

Напишем процедуру для создания нового пользователя в базе:

CREATE OR REPLACE PROCEDURE create_user (
  login varchar(50)
  ,pwd varchar(50) = ''
)
LANGUAGE plpgsql
AS $$
begin
  EXECUTE format('CREATE USER %I WITH PASSWORD %L LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;', login, pwd);
end;
$$;

Обратите внимание, что язык процедуры уже plpgsql.

Проверим, как процедура отработает:

call create_user('slowpoke', 'безпароля');

Видим нашего только что созданного нового пользователя:

Теперь по аналогии с предыдущим примером создадим функцию на plsql:

CREATE OR REPLACE FUNCTION rebuildindex (
  TableName varchar(256) 
)
RETURNS text
LANGUAGE plpgsql
AS $$
  DECLARE
    SQL varchar;
	eerm varchar;
  BEGIN
	SQL = 'REINDEX TABLE ' || TableName;
	BEGIN  
	  EXECUTE SQL;
	  return 'good job!';
	EXCEPTION
      when others then
	    eerm = SQLERRM;
	    return eerm;
	END;  
  END;
$$;

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

Проверим возможность реиндексации через вызов функции:

select rebuildindex('journal');

Алгоритм функции вернул текст из ветки с успешным выполнением реиндексации.

Ещё больше прокачиваемся. Писать на SQL и PL/SQL - это, конечно, хорошо, но высших алгоритмов, лёгких методов, быстрых скоростей мы такими средствами не получим. Поэтому переходим к серьёзным делам. Ведь самая сила, мощь и натиск заключаются в том, что мы можем с помощью расширений Постгреса создавать пользовательские процедуры и функции на различных языках высокого уровня. Вот тут уже мы практически ничем не ограничены в плане реализуемого функционала. Конечно, игру ГТА мы подобным подходом не разработаем, но будем к этому стремиться. Здесь я покажу примеры пользовательских методов именно на Питоне (нахваливать этот язык не буду, потому что он по умолчанию классный).

Для начала нам надо установить на сервере СУБД пакет "plpython3". Полное его наименование будет примерно таким: "postgres-...ВЕРСИЯ...-plpython3" или "postgresql-plpython3-...ВЕРСИЯ" (зависит от используемого вами Постгреса конкретного вендора). Для сборки от фирмы 1С надо на сервере Постгреса выполнить:

sudo apt install -y $path/postgresql-plpython3-18_18.1-2.1C_amd64.deb

где $path - директория с распакованными аддонами.

Затем надо включить расширение для базы, выполнив SQL-скрипт:

CREATE EXTENSION plpython3u;

Новый поддерживаемый язык должен появиться в списке расширений:

Теперь мы можем создавать свои методы на Python, используя все его возможности. Начнем с процедуры:

CREATE OR REPLACE PROCEDURE cmd (
  command text
)
LANGUAGE plpython3u
AS $$
import os
os.system(command)
$$;

Обратите внимание, что язык здесь уже plpython3u . Процедура выполняет команду системы от имени, под которым запущен Постгрес (по умолчанию, пользователь postgres).

Проверим процедуру, попытаемся создать пустой файл:

call cmd('touch /tmp/virus');

Посмотрим на список файлов в директории /tmp

Файл появился, владелец postgres. Но несколько раз подумайте рассудительно, прежде чем создавать подобные процедуры в проде. Это может понизить вашу секьюрность.

Теперь сделаем функцию на питонячем движке:

CREATE OR REPLACE FUNCTION deltab (
  src text
)
RETURNS void
LANGUAGE plpython3u
AS $$

tab = b'	'
ret = len(tab) - 1
chunk_size = 8 * 1024

edit = False

with open(src, 'rb') as f:
    while chunk := f.read(chunk_size):
        i = chunk.find(tab)

        if i != -1:
            edit = True
            break

        if len(chunk) < chunk_size:
            break
        else:
            f.seek(-ret, 1)

if edit:
    pos_r = 0
    pos_w = 0

    with open(src, 'rb+') as f:
        while chunk := f.read(chunk_size):
            pos_r = f.tell()

            new_chunk = chunk.replace(tab, b'')
            f.seek(pos_w)
            f.write(new_chunk[:-ret])
            pos_w = f.tell()

            f.seek(pos_r)

            if len(chunk) < chunk_size:
                f.seek(pos_w)
                f.write(new_chunk[-ret:])
                f.truncate(f.tell())
                f.flush()
                break
            else:
                f.seek(-ret, 1)
                
$$;

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

Можете сами проверить:

perform deltab('/tmp/bigfile');

Здесь мы вызываем функцию через PERFORM, т.к. функция не возвращает значения возвращает void .

Если вы повторяли примеры за мной, то у вас должно получиться так:

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

И ещё важный момент: процедура вызывается по вашему запросу, а функция может быть применена к каждой записи в таблице БД. В своей публикации я показывал, как переносить файлы из базы 1С во внешнюю базу, причём в исходной базе файлы "жили" внутри хранилища значений, а в базе назначения они уже были пожаты в ZIP. Можно разработать функцию, которая будет читать бинари из таблицы, представляющие картинки, и парсить оттуда, например, автомобильные номера с помощью питоновской библиотеки компьютерного зрения. Или из бинарей, в которых хранятся телефонные разговоры, одним select'ом получать текстовые расшифровки. Ну и т.д. и т.п.

Если вы дочитали до сюда, наверное, у вас закрался вопрос: а как это вообще применить к 1С ?

Легко и просто! Тут нам помогут внешние источники данных. Но так как ВИДы поддерживают только функции, то нам для начала надо "переконвертировать" наши созданные процедуры в функции. Для этого надо заменить PROCEDURE на FUNCTION и добавить RETURNS void . Придётся либо удалить процедуры, либо создавать функции с новыми именами, т.к. внутри базы используется сквозное наименование объектов БД. Я выбрал первый путь:

Переходим в конфигуратор и создаём новый внешний источник данных. С 24-й платформы ВИДы можно создать в расширении. Идём в раздел функций. Жмём плюсик. Эска предлагает добавить функцию. Указываем выбор из списка:

Далее нужно ввести строку подключения и учётные данные для подключения к СУБД:

Продублирую строку для ленивых удобного ввода:

Driver={PostgreSQL UNICODE};Server=192.168.234.75;Port=5432;Database=py_base;Uid=postgres;Pwd=<CENSORED>;

Выбираем все (нужные) функции в нашей схеме:

Обратите внимание, что функции, возвращающие void, для эски считаются процедурами!

В итоге получаем такой внешний источник данных:

Теперь кодим кодерский код для вызова функций (я приведу 1 пример):

Результат = ВнешниеИсточникиДанных.МП_ВИД.public_info();
Сообщить(Результат);

о-ла-ла! оно работает!

Накидал тестовое мини-расширение с одним ВИДом и одной обработкой, в которой можно затестить данные функции.

В обработке всего 13 значащих строк (сам удивляюсь). Скачать расширение (для 8.3.24+) бесплатно вы можете на моём ГитХабе в репе "freeware".

Что самое главное в подобном подходе - так это то, что мы никоим образом НЕ нарушаем лицензионное соглашение 1С, т.к. мы НЕ лезем в эсную базу, потому что все функции у нас располагаются в сторонней базе, которую мы подключаем средствами самой 1С.

Эксперименты проводились на самых свежих Debian (13.3), PostgreSQL от жёлтого вендора (18.1-2.1C), pgAdmin 4 (9.12) . Не зависит от платформы 1С (почти, если не брать в учёт древние релизы старше 10+ лет).

Кому понравилось или было хоть трошки полезно, поддержите меня рублём лайком!

Всем цифрового счастья!