Модуль pgpro_sfile позволяет хранить множество больших объектов и предоставляет функциональность, аналогичную большим объектам (LOB) в Oracle. В этом модуле реализован механизм хранения больших объектов в наборе таблиц, управляемых модулем. Каждый объект состоит из порций, или блоков. Блоки разбиты на страницы размером по ~8 КБ. Максимальное количество объектов, блоков и размер объекта в байтах ограничены размером типа bigint (2^63 - 1). Большой объект pgpro_sfile называется объектом sfile.
Объекты sfile хранятся в таблицах, которые создаются модулем pgpro_sfile и находятся под его контролем. В обычных таблицах хранится только идентификатор каждого объекта sfile в виде ссылки на таблицы, созданные модулем. Как только объект sfile создан, его можно загружать, читать, опустошать или удалять. При этом данные операции не влияют на обычные таблицы, в которых объект используется.
Механизм хранения представляет собой набор следующих таблиц хранения:
SF_DESCRIPTOR — реестр, в котором хранятся дескрипторы всех объектов sfile.
SF_PARTITION — реестр таблиц хранения (секций). Хранит информацию о таблицах SF_PAGE_XX.
SF_BLOCK — реестр блоков объектов sfile. Каждый блок может включать несколько страниц объектов sfile.
SF_PAGE_XX — таблицы со страницами данных объектов sfile.
SF_OPTION — таблица с параметрами объектов sfile. На данный момент существует только параметр TABLESPACE, который позволяет указать табличное пространство для хранения объектов sfile.
Все таблицы, кроме SF_OPTION, созданы в специальной схеме pgpro_sfile_data. Таблица SF_OPTION создаётся в ходе установки pgpro_sfile. Таблицы SF_DESCRIPTOR, SF_PARTITION и SF_BLOCK создаются в ходе инициализации pgpro_sfile, а таблицы SF_PAGE_XX создаются, когда объекты sfile записываются в базу данных, и отслеживаются в реестре SF_PARTITION.
В следующих разделах описана структура таблиц хранения.
SF_OPTION #Таблица SF_OPTION содержит следующие поля:
opt_type | smallint | Значение параметра: GLOBAL = 0, TABLE = 1 или OBJECT = 2. На данный момент поддерживается только значение GLOBAL. |
opt_name | name | Уникальное имя параметра. |
opt_value | name | Значение параметра. |
SF_DESCRIPTOR #Таблица SF_DESCRIPTOR содержит следующие поля:
sf_id | bigint | Уникальный идентификатор объекта sfile. |
sf_name | name | Имя объекта sfile. |
sf_persistence | smallint | Тип хранения объекта sfile: RELPERSISTENCE_PERMANENT="p" или RELPERSISTENCE_UNLOGGED="u". |
sf_state | smallint | Набор битов, показывающих состояние объекта sfile. Например: SF_STATE_DELETED=1. |
sys_creation_date | timestamp | Дата и время создания объекта sfile. |
sys_update_date | timestamp | Дата и время последнего обновления объекта sfile. |
sf_json_opts | text | Параметры объекта sfile в виде текста или в формате JSON. |
sf_type | text | Тип объекта sfile, заданный вручную. |
tbs_identity | text | Имя табличного пространства для хранения объекта sfile. |
SF_PARTITION #Таблица SF_PARTITION содержит следующие поля:
part_id | int | Уникальный идентификатор секции. |
part_data_size | bigint | Число блоков, на данный момент хранящихся в секции. |
part_persistence | smallint | Тип хранения данных в секции: RELPERSISTENCE_PERMANENT="p" или RELPERSISTENCE_UNLOGGED="u". |
rel_identity | text | Имя таблицы, в которой хранятся данные секции. |
tbs_identity | text | Имя табличного пространства, в котором хранятся данные секции. |
SF_BLOCK #Таблица SF_BLOCK содержит следующие поля:
sf_id | bigint | Уникальный идентификатор объекта sfile. |
block_id | bigint | Уникальный идентификатор блока в объекте sfile. Сюда записывается значение аргумента a_sf_index функции sf_write. |
part_id | int | Идентификатор секции, в которой хранятся данные блока. |
block_size | int | Размер блока, в байтах. |
block_start_page | int | Порядковый номер первой страницы блока; нумерация блоков начинается с 1. |
block_end_page | int | Порядковый номер последней страницы блока; нумерация блоков начинается с 1. |
sys_creation_date | timestamp | Дата и время создания блока. |
sys_update_date | timestamp | Дата и время последнего обновления блока. |
SF_PAGE_XX #Каждая таблица SF_PAGE_XX содержит следующие поля:
sf_id | bigint | Уникальный идентификатор объекта sfile. |
block_id | bigint | Уникальный идентификатор блока, в котором находится страница. |
page_no | int | Порядковый номер страницы в блоке. |
data | bytea | Буфер для хранения данных объекта sfile. Максимальный объём данных для хранения в буфере — 8096 байт. |
Расширение pgpro_sfile входит в состав Postgres Pro Enterprise. Чтобы его задействовать, создайте расширение следующим запросом:
CREATE EXTENSION pgpro_sfile;
Функции pg_superfile позволяют выполнять различные операции с объектами sfile, такие, как создание, запись, чтение, удаление и многое другое. Для доступа к идентификаторам объектов sfile эти функции используют тип данных sfile. Для хранения идентификаторов в таблицу необходимо добавить новый столбец с типом данных sfile.
sf_initialize() returns void #Инициализирует хранилище объектов sfile. Эта функция создаёт сначала схему pgpro_sfile_data, а затем — необходимые таблицы и последовательности в ней. Вызовите эту функцию сразу после установки расширения.
sf_deinitialize() returns void #Каскадно удаляет схему pgpro_sfile_data. Вызовите эту функцию перед удалением расширения, чтобы удалить все данные объектов sfile. Для вызова этой функции требуются права суперпользователя.
sf_create(a_sf_name text, a_sf_persistence text, a_sf_json_options text[, a_sf_tablespace text]) returns sfile #Создаёт новый объект sfile с именем a_sf_name и возвращает его идентификатор. Объект будет журналируемым или нежурналируемым в зависимости от значения параметра a_sf_persistence: LOGGED или UNLOGGED. Аргумент a_sf_json_options должен содержать файл JSON параметрами. Аргумент a_sf_tablespace позволяет указать табличное пространство, в котором будет храниться объект sfile. Если значение не задано, используется табличное пространство по умолчанию.
Добавляет в отношение SF_DESCRIPTOR дескриптор объекта sfile с нулевым размером. Поэтому для только что созданного объекта вызов функций sf_is_empty и sf_is_valid вернёт значение TRUE. Блоки и секции функцией sf_create не создаются.
sf_create_empty([a_sf_tablespace text]) returns sfile
#Создаёт новый пустой объект sfile с автоматически сгенерированным именем (sf_gen_XX) и пустым списком параметров и возвращает идентификатор объекта. Аргумент a_sf_tablespace позволяет указать табличное пространство, в котором будет храниться объект sfile. Если оно не указано, используется табличное пространство по умолчанию.
sf_write(a_sf sfile, a_sf_data bytea[, a_sf_index bigint]) returns integer #Вставляет новый блок данных a_sf_data с индексом, указанным в аргументе a_sf_index, в объект sfile, идентификатор которого указывается параметром a_sf. Возвращает количество добавленных байтов. В качестве индекса по умолчанию используется текущая временная метка. Передача аргумента a_sf_index <= 0 приводит к ошибке.
Функция сначала проверяет, был ли уже записан объект sfile, чтобы выяснить, доступна ли таблица секций SF_PAGE_XX и достаточно ли в ней свободного места для записи блока. Если подходящая секция в таблице не находится или это первая операция записи, проверяются все остальные секции, после чего первая доступная секция блокируется для записи. Если таковая не находится, создаётся новая и добавляется в реестр SF_PARTITION. Затем в SF_BLOCK инициализируется новая запись для блока, блок разбивается на страницы размером ~8 КБ, и эти страницы записываются в ранее заблокированную секцию PG_PAGE_XX. Когда блок записан, в поле part_data_size таблицы SF_PARTITION корректируется размер данных.
sf_read(a_sf sfile[, a_offset bigint, a_length integer]) returns bytea #Считывает число байтов, указанное в параметре a_length, из объекта sfile, определённого его идентификатором a_sf. Чтение выполняется со смещением, заданным в аргументе a_offset. Можно считать только до ~1 ГБ данных, что является ограничением типа varlena. Значение по умолчанию: ~1 ГБ. Смещение по умолчанию: 0. Возвращает буфер с прочитанными данными.
sf_size(a_sf sfile) returns bigint #Получает размер объекта sfile.
sf_truncate(a_sf sfile) returns bigint #Опустошает объект sfile: удаляет все данные, но оставляет нетронутым сам объект и возвращает новый размер объекта sfile, то есть 0. Поэтому при последующем вызове функций sf_is_valid и sf_is_empty для этого объекта возвращается значение TRUE.
sf_delete(a_sf sfile) returns bigint #Удаляет объект sfile и возвращает объём удалённых данных. Функция удаляет все блоки данных, а затем удаляет дескриптор объекта из SF_DESCRIPTOR. После этого объект становится недоступен, поэтому при вызове функции sf_is_valid для этого объекта возвращается значение FALSE.
sf_describe(a_sf sfile) returns cstring #Получает данные дескриптора объекта sfile и возвращает их в виде текста.
sf_get_json_options(a_sf sfile) returns cstring #Получает файл JSON с параметрами, заданными при создании объекта sfile (см. статью sf_create).
sf_find(a_sf_name text) returns sfilesf_find(a_sf_id bigint) returns sfile #Ищет объект sfile по указанному имени или по указанному идентификатору типа bigint и возвращает его идентификатор. Дополнительную информацию вы можете найти в Примере F.8.
sf_set_option(a_opt_name text, [a_opt_value text, a_opt_type text]) returns void #Задаёт параметр (настройку модуля pgpro_sfile), указанный аргументом a_opt_name, в таблице SF_OPTION. На данный момент поддерживаются только значения TABLESPACE для параметра a_opt_name и GLOBAL для a_opt_type. Значение по умолчанию для a_opt_value: NULL. Значение по умолчанию для a_opt_type: GLOBAL. С параметром TABLESPACE модуль pgpro_sfile проверяет, существует ли указанное табличное пространство, и если нет, то выдаёт ошибку.
sf_get_option(a_opt_name text) returns cstring #Получает параметр модуля pgpro_sfile с указанным в SF_OPTION именем.
sf_delete_option(a_opt_name text) returns void #Удаляет параметр модуля pgpro_sfile с указанным в SF_OPTION именем.
sf_is_valid(a_sf sfile) returns bool #Возвращает значение TRUE, если в аргументе указан существующий объект sfile, и значение FALSE в противном случае.
sf_is_empty(a_sf sfile) returns bool #Возвращает значение TRUE, если в аргументе указан существующий пустой объект sfile, и значение FALSE в противном случае.
sf_is_logged(a_sf sfile) returns bool #Возвращает значение TRUE, если в аргументе указан журналируемый (постоянный) объект sfile, и значение FALSE в противном случае.
sf_trim(a_sf sfile, a_length bigint) returns bigint #Усекает объект sfile, указанный аргументом a_sf, до размера, заданного аргументом a_length. Усечённые данные удаляются. Возвращается обновлённый размер объекта. Если размер объекта меньше, чем a_length, возвращается фактический размер объекта.
sf_set_type(a_sf sfile, a_type text) returns void #Задаёт для объекта sfile, заданного параметром a_sf, пользовательский тип, указанный в аргументе a_type.
sf_get_type(a_sf sfile) returns cstring #Получает пользовательский тип указанного объекта sfile в виде текста.
sf_md5(a_sf sfile) returns text #Вычисляет MD5-хеш для указанного объекта sfile.
Для ускорения взаимодействия с большими объектами, функции чтения sf_read и sf_size и функция записи sf_write могут выполняться параллельно. Эти функции накладывают на объект sfile блокировку типа AccessShareLock, что не запрещает чтение объекта. Функции sf_read и sf_size снимают эту блокировку после выполнения операции, в то время как функция записи sf_write сохраняет её до завершения транзакции, чтобы предотвратить удаление, опустошение, усечение объекта до завершения транзакции. Изменения в таблицах SF_BLOCK и SF_PAGE_XX, внесённые функцией записи sf_write, не видны в параллельном сеансе, и если блокировка AccessShareLock снимается до конца транзакции, то объект sfile может быть удалён в другом сеансе, который не знает о записях, добавленных в таблицы SF_BLOCK и SF_PAGE_XX. В итоге объект sfile будет удалён, а добавленные записи останутся.
В блоки одного объекта может идти запись из разных сеансов, а последовательность записи блоков зависит от времени вызова sf_write. Для управления последовательностью записи блоков используется значение аргумента a_sf_index, а если он не передан, используется значение по умолчанию: временная метка вызова функции. Каждый вызов sf_write накладывает блокировку AccessExclusiveLock на секции, куда записываются блоки, что запрещает запись в блоки одной и той же секции из разных сеансов, поскольку невозможно одновременно менять поле part_data_size таблицы SF_PARTITION. При этом запись блоков объекта sfile из нескольких сеансов в разные секции разрешается.
Функции sf_trim, sf_truncate и sf_delete нельзя выполнять параллельно, поскольку они накладывают блокировку AccessExclusiveLock на объект sfile.
Пример F.7. Простой пример использования модуля pgpro_sfile:
-- Создание расширения pgpro_sfile
CREATE EXTENSION pgpro_sfile;
-- Инициализация хранилища объектов sfile
SELECT sf_initialize();
-- Создание тестовой таблицы
CREATE TABLE test_sfile (id int, l sfile);
-- Создание и сохранение объекта sfile
INSERT INTO test_sfile VALUES (1, sf_create('sf', 'LOGGED', NULL));
-- Запись данных в объект sfile и их проверка
SELECT sf_write(t.l, '1234567890'::bytea) FROM test_sfile t WHERE id = 1;
SELECT encode(b, 'escape'), length(b) FROM (SELECT sf_read(t.l, 0, NULL) b FROM test_sfile t WHERE id = 1) x;
-- Усечение объекта sfile и его проверка
SELECT sf_trim(t.l, 5) FROM test_sfile t WHERE id = 1;
SELECT encode(b, 'escape'), length(b) FROM (SELECT sf_read(t.l, 0, NULL) b FROM test_sfile t WHERE id = 1) x;
-- Удаление таблицы
DROP TABLE test_sfile;
-- Удаление данных из всех объектов sfile
SET client_min_messages = WARNING;
SELECT sf_deinitialize();
RESET client_min_messages;
-- Удаление расширения pgpro_sfile
DROP EXTENSION pgpro_sfile;По завершении работы скрипта выдаётся результат:
CREATE EXTENSION
sf_initialize
---------------
(1 row)
CREATE TABLE
INSERT 0 1
sf_write
----------
10
(1 row)
encode | length
------------+--------
1234567890 | 10
(1 row)
sf_trim
---------
5
(1 row)
encode | length
--------+--------
12345 | 5
(1 row)
DROP TABLE
SET
sf_deinitialize
-----------------
(1 row)Пример F.8. Пример использования приведения типов вместо функции sf_find
-- Создание расширения pgpro_sfile
CREATE EXTENSION pgpro_sfile;
-- Инициализация хранилища объектов sfile
SELECT sf_initialize();
-- Создание объекта sfile
SELECT sf_create('test', 'LOGGED', NULL);
-- Запись в объект sfile по имени
SELECT sf_write(sf_find('test'), '1234567890'::bytea);
-- Запись в объект sfile по идентификатору
SELECT sf_write(1::bigint::sfile, '1234567890'::bytea);По завершении работы скрипта выдаётся результат:
CREATE EXTENSION
sf_initialize
---------------
(1 row)
sf_create
-----------
1
(1 row)
sf_write
----------
10
(1 row)
sf_write
----------
10
(1 row)Postgres Professional, Москва, Россия