Функции для документирования баз данных PostgreSQL

PostgreSQL_intoDOC
За время работы с PostgreSQL накопилось больше ста функций для работы с системными каталогами: pg_class, pg_attribute, pg_constraint и т.д.
Что с ними делать? Используются они относительно редко. Включить в какой-нибудь проект? Красноярский заказчик за такую «ерунду» платить не будет. И все же, а вдруг они полезны еще кому-то кроме автора. И решил выложить их, как прочитанные книги в общедоступный шкаф для желающих.
Кто-то захочет их использовать в своей работе. А кого-то заинтересует отличный от своего опыт работы с системными каталогами.

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

Расширенный список характеристик таблицы базы данных возвращает функция admtf_Table_ComplexFeatures, которая в этой статье будет называться головной функцией. Таким образом, статья ограничится рассмотрением функций, которые вызываются в процессе выполнения головной функции.

В первой половине статьи изложены комментарии к реализации функций. Во второй- исходные тексты функций. Тем из читателей, кого интересуют только исходные тексты, предлагаем сразу перейти к Приложению или  Скачать все скрипты одним архивом скачать все скрипты одним архивом.


Содержание

О каких расширенных характеристиках идет речь?
Структура головной функции
Функция admtf_Table_Features список характеристик таблицы базы данных
Функция admtf_Table_Attributes список атрибутов таблицы базы данных и их характеристик
Функция admtf_Table_Constraintes список ограничений таблицы базы данных и их характеристик
    Версия функции admtf_Table_Constraintes без курсора
Функция admtf_Table_Indexes список индексов таблицы базы данных и их характеристик
    Версия функции admtf_Table_Indexes без курсора


ПРИЛОЖЕНИЕ 1.
    Создание функции admfn_Table_RowCount
    Создание функции admtf_Table_Features
    Создание функции admtf_Table_Attributes
    Создание функции admtf_Table_Attributes с использованием псевдонима regclass
    Создание функции admtf_Table_Constraintes
    Создание функции admtf_Table_Constraintes без курсора
    Создание функции admtf_Table_Indexes
    Создание версии функции admtf_Table_Indexes без курсора
ПРИЛОЖЕНИЕ 2.Дополнительные материалы
    Вспомогательная схема базы данных
    Полная версия расширенных характеристик таблицы Street (улицы)

О каких расширенных характеристиках идет речь?

Для того, чтобы составить представление о том, что здесь понимается под расширенными характеристиками таблицы базы данных, начнем с рассмотрения следующего списка характеристик. Список содержит характеристики таблицы базы данных Street (улицы), возвращенные функцией admtf_Table_ComplexFeatures(‘public’, ‘street’).

Таблица 1. Расширенные характеристики таблицы Street (улицы).

Table_Feature_Result_Short

Приведенная выше таблица содержит сокращенный список характеристик таблицы Street. Полный набор характеристик этой таблицы приведен в Дополнительные материалах Приложения 2.

Переборов неприязнь к этому набору букв и цифр, можно заметить, что речь идет об обычных характеристиках таблицы базы данных:

  • Названии таблицы;
  • Списка атрибутов таблицы и их типов;
  • Первичного ключа и списка внешних ключей таблицы вместе с составляющими их атрибутами таблицы;
  • Списка индексов таблицы.

Уникальность каждой записи из списка характеристик обеспечивают значения полей «категория» и порядковый номер («№») характеристики.

Таблица 2.Категории характеристик таблиц.

Char_abbreviations

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

В таблице базы данных может быть объявлено несколько внешних ключей (FOREIGN KEY) и индексов. Поэтому значение категории для этих характеристик и их потомков содержит порядковый номер. Например, запись с ключом «Категория» = idx02att и «№» = 1 указывает на первый атрибут 2-го индекса.

В выше приведенном списке категорий, место нахождения порядкового номера обозначено как ’99’ .

Замечание 1

SELECT_Table_CF

В статье приводятся примеры характеристик таблиц, которые кратко описаны во вспомогательной схеме, созданной специально для демонстрации возможностей функций. Но читатель, создав ту или иную функцию в своей базе данных, может в качестве параметров использовать названия своих схем и таблиц. Более того, в качестве параметра может быть использован, например, каталога pg_class, хотя в этом случае выдается ограниченное число характеристик.

Конец замечания.

Структура головной функции

Table_FeatureStructure

Рис. 1. Функции, которые вызывает головная функция.

Таблица 3. Назначение функций.

Table_CFeature_Result_ShortЗамечание 2.

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

Конец замечания.

Функция admtf_Table_Features список характеристик таблицы базы данных

Функция admtf_Table_Features возвращает список характеристик собственно таблицы базы данных. Исходный код можно посмотреть и скачать здесь.

В качестве параметров функция принимает название исходной таблицы (a_TableName) и название схемы, в пределах которой создана таблица (a_SchemaName).

Основные данные функция извлекает из записи каталога pg_class, содержащего кроме записей о таблицах еще и, записи о последовательностях, представлениях, материализованных представлениях, составных типах. Поэтому для выбора таблиц используется условие relkind=‘r’.

SELECT_Table_F_inside

Дополнительно функция обращается к данным каталогов pg_namespace и pg_description. Первый содержит названия схем базы данных, а второй — комментарии ко всем объектам БД.

Здесь важно обратить внимание на условие objsubid=0. Оно определяет комментарий к таблице, т к. значение поля objoid одинаково как для таблицы, так и для ее атрибутов. Комментарий к атрибуту таблицы содержится в записи, в которой objsubid совпадает с номером этого атрибута.

Таблица 4. Результат выполнения функции admtf_Table_Features(‘public’,’Street’).

Table_Feature_Result

Замечание 3

Обратите внимание на число атрибутов таблицы street. Оно значительно отличается от числа атрибутов, указанных во вспомогательной схеме.

Таблица 5. Дополнительные атрибуты таблицы Street.

Table_Street_Attributes
Дело в том, что PostgreSQL кроме основных атрибутов дополнительно учитывает несколько системных атрибутов, и даже удаленные атрибуты.

Конец замечания

Обычно функция возвращает оценочное число записей в таблице, находящееся в поле reltuples из записи каталога pg_class, но если воспользоваться задать дополнительный параметр a_Mode со значением ‘exactly’, то функция возвратит расчетное значение.

Функция admtf_Table_Attributes список атрибутов таблицы базы данных и их характеристик

Функция admtf_Table_Attributes возвращает список атрибутов таблицы базы данных.Исходный код можно посмотреть и скачать здесь.

В качестве параметров функция принимает название исходной таблицы (a_TableName) и название схемы, в пределах которой создана таблица (a_SchemaName).

Основные данные функция извлекает из записи каталогов pg_attribute и pg_type. Первый содержит записи с данными об атрибутах таблиц, представлений, материализованных представлений, составных типов и даже функций. Второй – о характеристиках типах атрибутов.

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

Атрибут таблицы объявлен с пользовательским типом, если в соответствующей записи каталога pg_type поле typbasetype больше 0. В противном случае атрибут имеет базовый тип. Поэтому в предложении FROM каталог pg_type участвует дважды. В первой записи каталога определяется наличие пользовательского типа, если таковой не определен (typbasetype=0), то по этой записи формируется значение базового типа. В противном случае базовый тип определяется из записи, для которой btyp.OID= typ.typbasetype.

Непосредственно строка с базовым типом формируется при помощи функции системного каталога FORMAT_TYPE(type_oid, typemod). Первым параметром которой является записи OID базового типа. Второй параметр – это значение модификатора для типов, которые содержат в себе размер. Например, VARCHAR(100) или NUMERIC(4,2), DECIMAL(4,2). Значение параметра typemod берется из typ.typtypmod в случае, если атрибут имеет пользовательский тип, иначе из attr.atttypmod, т.е. непосредственно из записи об атрибуте.
SELECT_Table_A_inside


Дополнительно функция обращается к данным каталогов pg_class, pg_namespace и pg_description. Первый и второй каталоги используются для поиска атрибутов по названиях схемы и таблицы базы данных.

Третий каталог используется для извлечения комментария к атрибуту таблицы.

Комментарий к атрибуту таблицы находится в записи, в который dsc.objoid содержит OID исходной таблицы, а dsc.objsubid порядковый номер атрибута в таблице, т.е. attr.attnum.

Для того чтобы функция не возвращала системные и удаленные атрибуты, в предложении WHERE установлено условие attr.attnum>0 AND attr.atttypID>0.

Таблица 6. Результат выполнения функции admtf_Table_Attributes (‘public’,’Street’).

Table_A_results

Версия функции с использованием псевдонима regclass для типа oid

Идентификаторы объектов (OIDs) в PostgreSQL имеют одноименный тип OID, который в настоящий момент реализован как беззнаковое четырёхбайтовое целое число. Но благодаря наличию псевдонимов этого типа, целое число может быть представлено как имя объекта. И наоборот – преобразовать имя объекта к целому числу типа OID.

В качестве примера взгляните на следующий оператор SELECT. В нем необычно извлекаются названия таблицы атрибута и названия его типов — вместо обращения к соответствующим полям каталогов с названиями этих характеристик используются:

  • attrelid::regclass(attrelid::regclass:NAME);
  • atttypid::regtype(atttypid::regtype:NAME);
  • typbasetype::regtype(typbasetype::regtype:NAME).

Ниже приведен результат выполнения этого запроса.

SELECT_Table_A_regclass_results v2

Как видите, в списке выводимых значение оператора SELECT до преобразования с применением псевдонимов типа OID все значения кроме названия атрибута числовые, но в результате отображены названия таблицы и типов атрибутов. Типы выводимых значений можно рассмотреть во второй строчке заголовка таблицы.

Кроме того, в разделе WHERE утверждения находится условие attr.attrelid=(‘public’||’.’||’Street’)::regclass, в левой части которого числовое значение, а в правой – строковое, которое преобразуется к числовому с помощью псевдонима regclass.

SELECT_Table_A_inside


С помощью псевдонима regclass из основного утверждения можно убрать соединение с двумя каталогами. Но на производительность функции такое улучшение почти не сказалось – и в той, и в другой версии функция выполняется за 11 ms. Возможно из-за того, что в тестовой таблице мало атрибутов.
Замечание 4

Серьезный недостаток условия в форме attr.attrelid=(a_SchemaName||’.’ ||a_TableName)::regclass проявляется в случае, когда в базе данных присутствует схема и/или таблица с необычным названием. Например, «Моя схема» и/или «Моя таблица». Такие значения нужно передавать, заключенными в двойные кавычки или использовать функцию QUOTE_IDENT, иначе функция завершится с системной ошибкой.

SELECT_Table_A_regclass error


Поэтому я предпочитаю использовать условия в форме LOWER(nspc.nspname)=LOWER(a_SchemaName) AND LOWER(tbl.relname) =LOWER(a_TableName), которые не приводит к системным ошибкам.
Конец замечания

admtf_Table_Constraintes список ограничений таблицы базы данных и их характеристик

Функция admtf_Table_Constraintes возвращает список ограничений (CONSTRAINT) таблицы базы данных и их характеристик. Исходный код можно посмотреть и скачать здесь, а здесь приведена версия функции, в которой не используется курсор.

В качестве параметров функция принимает название исходной таблицы (a_TableName) и название схемы, в пределах которой создана таблицас(a_SchemaName).

Описание отдельного ограничения представляет собой совокупность записи в pg_class, описывающей его как физическое отношение, и записи в pg_constraint, содержащей данные о специфических характеристиках ограничения.

SELECT_Table_C_inside

Основные данные (название и тип ограничения) функция извлекает из записи каталога pg_constraint. Из этого же каталога извлекаются характеристики каждого ограничения, которые представлены в форме OID таблиц (conrelid, confrelid) или массивов порядковых номеров атрибутов (conkey, confkey), участвующих в ограничении.

Характеристики ограничений функция возвращает в виде названий таблиц и атрибутов. При этом наименования таблиц извлекаются из записи каталога pg_class по идентификатору (OID), а наименования атрибутов из записей каталога pg_attribute по идентификатору таблицы и порядковому номеру атрибута. Т.к. порядковые номера хранятся в основном каталоге в форме массива (списка), то списки наименований атрибутов формируются внутри функции с помощью цикла.

Функция возвращает одну особую характеристику – правило проверки значений полей в записях таблицы (ограничение CHECK). Эта характеристика хранится как текстовое значение в поле consrc, каталога pg_constraint.

Таблица 7. Результат выполнения функции admtf_Table_Constraintes (‘public’,’Street’).
Table_Constraintes_Result

Версия admtf_Table_Constraintes без курсора

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

Отвечать не буду –на вкус и цвет товарищей нет. Но приведу версию функции без курсора. Версию реализации функции без использования курсора можно посмотреть и скачать здесь.

Главная сложность в том, чтобы организовать соединение (JOIN) таблиц по значениям расположенных в атрибуте типа массив одной из них. Такими массивами в этом случае являются conkey и confkey.

SELECT_Table_C_gs_inside

Для решения такой PostgrSQL содержит функции, которые возвращают таблицу из значений указателей на элементы массива. В нашем случае будет использована функция generate_subscripts. Мало того, что она генерирует множество указателей на позицию массива, переданного ей в качестве параметра, она еще превращает одну запись, содержащею массив, в несколько по числу элементов массива. Каждая запись такой таблицы содержит одно уникальное значение – позицию массива.

Таблица 8. Размножение исходной строки с помощью generate_subscripts.
generate_subscripts_result
SELECT_Table_C_woc_inside

Такую таблицу можно соединить с каталогом атрибутов pg_attribute, извлекая из него названия атрибутов по условию attr.attrelid=tbl.oid AND attr.attnum=con.conkey[con.No].
Теперь осталось убрать лишние записи при помощи группировки записей, а из названий атрибутов создать строку.

Создание строки выполняется с помощью агрегирующей функции STRING_AGG, в которой обязательно нужно указать опцию сортировки (ORDER BY), иначе порядок атрибутов может оказаться несоответствующим порядку объявления атрибутов в индексе.

Время выполнения обеих версий функций у меня совпало. На вывод данных в таблице результатов ушло 20 ms.

Функция admtf_Table_Indexes список индексов таблицы базы данных и их характеристик

Функция admtf_Table_Indexes возвращает список индексов (INDEX) таблицы базы данных и их характеристик.Исходный код можно посмотреть и скачать здесь, а здесь приведена версия функции, в которой не используется курсор.

В качестве параметров функция принимает название исходной таблицы (a_TableName) и название схемы, в пределах которой создана таблица (a_SchemaName).

SELECT_Table_I_inside

Описание отдельного индекса представляет собой совокупность записи в pg_class, описывающей его как физическое отношение, и записи в pg_index, содержащей данные о специфических характеристиках индекса. Дополнительно информация о методах доступа индекса хранится в системном каталоге pg_am.

Из записи каталога pg_index извлекаются признак уникальности индекса (indisunique), признак того, что индекс построен в соответствии с описанием первичного ключа (indisprimary), а также массивы порядковых номеров атрибутов таблицы, по значениям которых строится индекс (indkey) и признаков порядка сортировки значений атрибутов в индексе (indoption).

Из записи каталога с описанием метода доступа индекса pg_am извлекается признак пригодности включенных в индекс данных для сортировки (amcanorder) и название или тип метода доступа индекса (amname).

Другими словами, признак amcanorder указывает на то, можно ли установить порядок сортировки значений входящих в индекс атрибутов. Если amcanorder = true, то порядок сортировки может быть указан, иначе нет. Из этого же рисунка виден смысл значений массива indoption — если правый бит двоичной формы значения содержит 1B, то значение соответствующего атрибута сортируются в убывающем порядке, в противном случае — в возрастающем порядке.

Списки наименований атрибутов, входящих в индекс, а также признаки упорядоченности значений атрибутов, формируются внутри функции с помощью цикла.

Таблица 9. Результат выполнения функции admtf_Table_Indexes (‘public’,’Street’).

Table_Indexes_Result

Версия функции admtf_Table_Indexes без курсора

Подход к созданию версии функции без курсора полностью совпадает с уже описанным в предыдущем разделе:

  • размножение записей с помощью generate_subscripts;
  • последующая группировка записей;
  • создание списка атрибутов индексов при помощи функции STRING_AGG с опцией ORDER BY.

SELECT_Table_I_woc_inside

Время выполнения обеих версий функций у меня совпало на вывод данных в таблице результатов ушло 20 ms.

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

Замечание 5.

Упрощенная версия функции admtf_Table_Indexes без использования курсора может быть создана с помощью каталога pg_depend. В этом каталоге хранятся записи о зависимости объектов. При этому показывается не только зависимость таблицы от индекса, но и каждого ее атрибуту, который включен в описание индекса.

Таблица 10. Атрибуты каталога pg_depend, необходимые для реализации функции.
pg_depend_Attributes

Поатрибутное описание зависимости таблицы от индекса создает впечатление, что можно обойтись без generate_subscripts.

SELECT_Table_I_depend

Но эта реализация имеет два существенных недостатка:

  • Для атрибутов в составе индекса нельзя указать порядок сортировки, т.к. порядок сортировки указывается в массиве indoption;
  • Отсутствует информация о порядке следования атрибутов в описании индекса, т.к. этот порядок задается в массиве indkey.

Конец замечания.

Смотри также

Функции для документирования баз данных. Окончание.


ПРИЛОЖЕНИЕ 1. Скрипты

Создание функции admfn_Table_RowCount


Создание функции admtf_Table_Features

Комментарии к исходному коду функции можно посмотреть здесь.

Создание функции admtf_Table_Attributes

Комментарии к исходному коду функции можно посмотреть здесь.


Создание функции admtf_Table_Attributes с использованием псевдонима regclass

Комментарии к исходному коду функции можно посмотреть здесь.


Создание функции admtf_Table_Constraintes

Комментарии к исходному коду функции можно посмотреть здесь.


Создание версии функции admtf_Table_Constraintes без курсора

Комментарии к исходному коду функции можно посмотреть здесь.


Создание функции admtf_Table_Indexes

Комментарии к исходному коду функции можно посмотреть здесь.


Создание версии функции admtf_Table_Indexes без курсора

Комментарии к исходному коду функции можно посмотреть здесь.

ПРИЛОЖЕНИЕ 2. Дополнительные материалы

Вспомогательная схема базы данных

SchemaForExample

  • COUNTRY— Классификатор стран мира — ОКСМ (Общероссийский классификатор стран мира);
  • HOUSEADDR— Список номеров домов на улицах населенных пунктов;
  • LCLTYTYPE— Справочник типов населенных пунктов;
  • LOCALITY— Список населенных пунктов;
  • STREET— Список улиц в населенных пунктах;
  • STREETTYPE— Справочник типов улиц;
  • TERRITORY— Список территорий (республик, краев, областей, районов и т.д.);
  • TERRITORYTYPE— Справочник типов территорий.

Полная версия расширенных характеристик таблицы Street (улицы)

Комментарии к понятию расширенные характеристики таблицы можно посмотреть здесь.

Смотри также

Функции для документирования баз данных. Окончание.


Места постоянного размещения статьи:

This entry was posted in Блог and tagged , , , , , , , , , , . Bookmark the permalink.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *