За время работы с PostgreSQL накопилось больше ста функций для работы с системными каталогами: pg_class, pg_attribute, pg_constraint и т.д.
Что с ними делать? Используются они относительно редко. Включить в какой-нибудь проект? Красноярский заказчик за такую «ерунду» платить не будет. И все же, а вдруг они полезны еще кому-то кроме автора. И решил выложить их, как прочитанные книги в общедоступный шкаф для желающих.
Кто-то захочет их использовать в своей работе. А кого-то заинтересует отличный от своего опыт работы с системными каталогами.
Но чтобы не превращать публикацию в скучное перечисление, неизвестно зачем созданных функций, решил остановиться на тех из них, которые можно объединить общей целью. Поэтому выбраны функции, которые используются для выдачи расширенного списка характеристик произвольной таблицы базы данных.
Расширенный список характеристик таблицы базы данных возвращает функция admtf_Table_ComplexFeatures, которая в этой статье будет называться головной функцией. Таким образом, статья ограничится рассмотрением функций, которые вызываются в процессе выполнения головной функции.
В первой половине статьи изложены комментарии к реализации функций. Во второй- исходные тексты функций. Тем из читателей, кого интересуют только исходные тексты, предлагаем сразу перейти к Приложению или скачать все скрипты одним архивом.
Для того, чтобы составить представление о том, что здесь понимается под расширенными характеристиками таблицы базы данных, начнем с рассмотрения следующего списка характеристик. Список содержит характеристики таблицы базы данных Street (улицы), возвращенные функцией admtf_Table_ComplexFeatures(‘public’, ‘street’).
Таблица 1. Расширенные характеристики таблицы Street (улицы).
Приведенная выше таблица содержит сокращенный список характеристик таблицы Street. Полный набор характеристик этой таблицы приведен в Дополнительные материалах Приложения 2.
Переборов неприязнь к этому набору букв и цифр, можно заметить, что речь идет об обычных характеристиках таблицы базы данных:
Названии таблицы;
Списка атрибутов таблицы и их типов;
Первичного ключа и списка внешних ключей таблицы вместе с составляющими их атрибутами таблицы;
Списка индексов таблицы.
Уникальность каждой записи из списка характеристик обеспечивают значения полей «категория» и порядковый номер («№») характеристики.
Таблица 2.Категории характеристик таблиц.
Значение категории необходимо для того, чтобы отличать различные группы характеристик друг от друга. А порядковый номер, для того, чтобы отличать характеристики внутри группы.
В таблице базы данных может быть объявлено несколько внешних ключей (FOREIGN KEY) и индексов. Поэтому значение категории для этих характеристик и их потомков содержит порядковый номер. Например, запись с ключом «Категория» = idx02att и «№» = 1 указывает на первый атрибут 2-го индекса.
В выше приведенном списке категорий, место нахождения порядкового номера обозначено как ’99’ .
Замечание 1
исходный код оператора на рисунке
SELECT * FROM admtf_Table_ComplexFeatures(‘pg_catalog’,’pg_class’);
В статье приводятся примеры характеристик таблиц, которые кратко описаны во вспомогательной схеме, созданной специально для демонстрации возможностей функций. Но читатель, создав ту или иную функцию в своей базе данных, может в качестве параметров использовать названия своих схем и таблиц. Более того, в качестве параметра может быть использован, например, каталога pg_class, хотя в этом случае выдается ограниченное число характеристик.
Конец замечания.
Структура головной функции
Рис. 1. Функции, которые вызывает головная функция.
Таблица 3. Назначение функций.
Замечание 2.
Описания функций будут располагаться в указанном выше порядке. Причина в том, что статью придется разбить на несколько частей. А расположенные в таком порядке функции могут использоваться независимо от того, что часть из них будет описана лишь в следующих частях публикации.
Конец замечания.
Функция admtf_Table_Features список характеристик таблицы базы данных
В качестве параметров функция принимает название исходной таблицы (a_TableName) и название схемы, в пределах которой создана таблица (a_SchemaName).
Основные данные функция извлекает из записи каталога pg_class, содержащего кроме записей о таблицах еще и, записи о последовательностях, представлениях, материализованных представлениях, составных типах. Поэтому для выбора таблиц используется условие relkind=‘r’.
INNER JOIN pg_namespace nspc ON tbl.relnamespace=nspc.oid
LEFT OUTER JOIN pg_description dsc ON tbl.oid=dsc.objoid
ANDdsc.objsubid=0
WHERE LOWER(nspc.nspname)=LOWER(a_SchemaName)ANDtbl.relkind='r'
ANDLOWER(tbl.relname)=LOWER(a_TableName);
Дополнительно функция обращается к данным каталогов pg_namespace и pg_description. Первый содержит названия схем базы данных, а второй — комментарии ко всем объектам БД.
Здесь важно обратить внимание на условие objsubid=0. Оно определяет комментарий к таблице, т к. значение поля objoid одинаково как для таблицы, так и для ее атрибутов. Комментарий к атрибуту таблицы содержится в записи, в которой objsubid совпадает с номером этого атрибута.
Таблица 4. Результат выполнения функции admtf_Table_Features(‘public’,’Street’).
Замечание 3
Обратите внимание на число атрибутов таблицы street. Оно значительно отличается от числа атрибутов, указанных во вспомогательной схеме.
Таблица 5. Дополнительные атрибуты таблицы Street.
Дело в том, что PostgreSQL кроме основных атрибутов дополнительно учитывает несколько системных атрибутов, и даже удаленные атрибуты.
Конец замечания
Обычно функция возвращает оценочное число записей в таблице, находящееся в поле reltuples из записи каталога pg_class, но если воспользоваться задать дополнительный параметр a_Mode со значением ‘exactly’, то функция возвратит расчетное значение.
Функция 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, т.е. непосредственно из записи об атрибуте.
Дополнительно функция обращается к данным каталогов 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’).
Версия функции с использованием псевдонима regclass для типа oid
Идентификаторы объектов (OIDs) в PostgreSQL имеют одноименный тип OID, который в настоящий момент реализован как беззнаковое четырёхбайтовое целое число. Но благодаря наличию псевдонимов этого типа, целое число может быть представлено как имя объекта. И наоборот – преобразовать имя объекта к целому числу типа OID.
В качестве примера взгляните на следующий оператор SELECT. В нем необычно извлекаются названия таблицы атрибута и названия его типов — вместо обращения к соответствующим полям каталогов с названиями этих характеристик используются:
WHERE attr.attrelid=('public'||'.'||'Street')::regclass
ANDattr.attnum>0ANDattr.atttypID>0
ORDER BY attr.attnum;
Ниже приведен результат выполнения этого запроса.
Как видите, в списке выводимых значение оператора SELECT до преобразования с применением псевдонимов типа OID все значения кроме названия атрибута числовые, но в результате отображены названия таблицы и типов атрибутов. Типы выводимых значений можно рассмотреть во второй строчке заголовка таблицы.
Кроме того, в разделе WHERE утверждения находится условие attr.attrelid=(‘public’||’.’||’Street’)::regclass, в левой части которого числовое значение, а в правой – строковое, которое преобразуется к числовому с помощью псевдонима regclass.
LEFT OUTER JOIN pg_description dsc ON dsc.objoid=attr.attrelid
ANDdsc.objsubid=attr.attnum
WHERE attr.attrelid=(a_SchemaName||'.'||a_TableName)::regclass
ANDattr.attnum>0ANDattr.atttypID>0
ORDER BY attr.attnum;
С помощью псевдонима regclass из основного утверждения можно убрать соединение с двумя каталогами. Но на производительность функции такое улучшение почти не сказалось – и в той, и в другой версии функция выполняется за 11 ms. Возможно из-за того, что в тестовой таблице мало атрибутов. Замечание 4
Серьезный недостаток условия в форме attr.attrelid=(a_SchemaName||’.’ ||a_TableName)::regclass проявляется в случае, когда в базе данных присутствует схема и/или таблица с необычным названием. Например, «Моя схема» и/или «Моя таблица». Такие значения нужно передавать, заключенными в двойные кавычки или использовать функцию QUOTE_IDENT, иначе функция завершится с системной ошибкой.
исходный код оператора на рисунке
SELECT_Table_A_regclass_error
1
2
3
4
5
6
7
8
9
10
11
12
13
14
/* Выполнение следующего утверждения завершится системным сообщением «ошибка синтаксиса в имени»*/
WHERE attr.attrelid=('"Моя схема"'||'.'||'"Моя таблица"')::regclass ANDattr.attnum>0ANDattr.atttypID>0
ORDER BY attr.attnum;
Поэтому я предпочитаю использовать условия в форме LOWER(nspc.nspname)=LOWER(a_SchemaName) AND LOWER(tbl.relname) =LOWER(a_TableName), которые не приводит к системным ошибкам. Конец замечания
admtf_Table_Constraintes список ограничений таблицы базы данных и их характеристик
В качестве параметров функция принимает название исходной таблицы (a_TableName) и название схемы, в пределах которой создана таблицас(a_SchemaName).
Описание отдельного ограничения представляет собой совокупность записи в pg_class, описывающей его как физическое отношение, и записи в pg_constraint, содержащей данные о специфических характеристиках ограничения.
INNER JOIN pg_namespace nsp ON con.connamespace=nsp.oid
LEFT OUTER JOIN pg_class tbl ON con.conrelid=tbl.oid
LEFT OUTER JOIN pg_class reftbl ON con.confrelid=reftbl.oid
WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName)
ANDLOWER(tbl.relname)=LOWER(a_TableOID)
ORDER BY con.contype DESC,con.conname;
Основные данные (название и тип ограничения) функция извлекает из записи каталога pg_constraint. Из этого же каталога извлекаются характеристики каждого ограничения, которые представлены в форме OID таблиц (conrelid, confrelid) или массивов порядковых номеров атрибутов (conkey, confkey), участвующих в ограничении.
Характеристики ограничений функция возвращает в виде названий таблиц и атрибутов. При этом наименования таблиц извлекаются из записи каталога pg_classпо идентификатору (OID), а наименования атрибутов из записей каталога pg_attribute по идентификатору таблицы и порядковому номеру атрибута. Т.к. порядковые номера хранятся в основном каталоге в форме массива (списка), то списки наименований атрибутов формируются внутри функции с помощью цикла.
Функция возвращает одну особую характеристику – правило проверки значений полей в записях таблицы (ограничение CHECK). Эта характеристика хранится как текстовое значение в поле consrc, каталога pg_constraint.
Таблица 7. Результат выполнения функции admtf_Table_Constraintes (‘public’,’Street’).
Версия admtf_Table_Constraintes без курсора
Предвижу вопросы и замечания на тему использования курсора в основной версии функции.
Главная сложность в том, чтобы организовать соединение (JOIN) таблиц по значениям расположенных в атрибуте типа массив одной из них. Такими массивами в этом случае являются conkey и confkey.
исходный код оператора на рисунке
SELECT_pg_constraint GENERATE_SUBSCRIPTS
1
2
3
4
5
SELECTc.conname,c.contype,c.conkey::SMALLINT[],
GENERATE_SUBSCRIPTS(c.conkey,1)asNo
FROM pg_constraintc
WHEREc.conname='fk_street_locality'
ORDER BY No;
Для решения такой PostgrSQL содержит функции, которые возвращают таблицу из значений указателей на элементы массива. В нашем случае будет использована функция generate_subscripts. Мало того, что она генерирует множество указателей на позицию массива, переданного ей в качестве параметра, она еще превращает одну запись, содержащею массив, в несколько по числу элементов массива. Каждая запись такой таблицы содержит одно уникальное значение – позицию массива.
Таблица 8. Размножение исходной строки с помощью generate_subscripts.
INNER JOIN pg_attribute attr ON attr.attrelid=tbl.oid
ANDattr.attnum=con.conkey[con.No]
INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid
LEFT OUTER JOIN pg_class reftbl ON con.confrelid=reftbl.oid
LEFT OUTER JOIN pg_attribute rattr ON rattr.attrelid=reftbl.oid
ANDrattr.attnum=con.confkey[con.No]
WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName)
ANDLOWER(tbl.relname)=LOWER(a_TableName)
GROUP BY con.conname,con.contype,reftbl.relname,con.consrc
ORDER BY con.contype DESC,con.conname;
Такую таблицу можно соединить с каталогом атрибутов pg_attribute, извлекая из него названия атрибутов по условию attr.attrelid=tbl.oid AND attr.attnum=con.conkey[con.No].
Теперь осталось убрать лишние записи при помощи группировки записей, а из названий атрибутов создать строку.
Создание строки выполняется с помощью агрегирующей функции STRING_AGG, в которой обязательно нужно указать опцию сортировки (ORDER BY), иначе порядок атрибутов может оказаться несоответствующим порядку объявления атрибутов в индексе.
Время выполнения обеих версий функций у меня совпало. На вывод данных в таблице результатов ушло 20 ms.
Функция admtf_Table_Indexes список индексов таблицы базы данных и их характеристик
INNER JOIN pg_class inxcls ON inx.indexrelid=inxcls.oid
INNER JOIN pg_namespace inxnsp ON inxcls.relnamespace=inxnsp.oid
INNER JOIN pg_am inxam ON inxcls.relam=inxam.oid
INNER JOIN pg_class tbl ON inx.indrelid=tbl.oid
INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid
WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName)
ANDLOWER(tbl.relname)=LOWER(a_TableOID)
ORDER BY inxam.amname,inxcls.relname;
Описание отдельного индекса представляет собой совокупность записи в pg_class, описывающей его как физическое отношение, и записи в pg_index, содержащей данные о специфических характеристиках индекса. Дополнительно информация о методах доступа индекса хранится в системном каталоге pg_am.
CASE inxam.amcanorder
1
2
3
4
CASEinxam.amcanorder WHEN trueTHENCASEinx.indoption[inx.No]&1
WHEN1THEN' DESC'
ELSE' ASC'END
ELSE''END;
Из записи каталога pg_index извлекаются признак уникальности индекса (indisunique), признак того, что индекс построен в соответствии с описанием первичного ключа (indisprimary), а также массивы порядковых номеров атрибутов таблицы, по значениям которых строится индекс (indkey) и признаков порядка сортировки значений атрибутов в индексе (indoption).
Из записи каталога с описанием метода доступа индекса pg_am извлекается признак пригодности включенных в индекс данных для сортировки (amcanorder) и название или тип метода доступа индекса (amname).
Другими словами, признак amcanorder указывает на то, можно ли установить порядок сортировки значений входящих в индекс атрибутов. Если amcanorder = true, то порядок сортировки может быть указан, иначе нет. Из этого же рисунка виден смысл значений массива indoption — если правый бит двоичной формы значения содержит 1B, то значение соответствующего атрибута сортируются в убывающем порядке, в противном случае — в возрастающем порядке.
Списки наименований атрибутов, входящих в индекс, а также признаки упорядоченности значений атрибутов, формируются внутри функции с помощью цикла.
Таблица 9. Результат выполнения функции admtf_Table_Indexes (‘public’,’Street’).
Версия функции admtf_Table_Indexes без курсора
Подход к созданию версии функции без курсора полностью совпадает с уже описанным в предыдущем разделе:
размножение записей с помощью generate_subscripts;
последующая группировка записей;
создание списка атрибутов индексов при помощи функции STRING_AGG с опцией ORDER BY.
INNER JOIN pg_class inxcls ON inx.indexrelid=inxcls.oid
INNER JOIN pg_am inxam ON inxcls.relam=inxam.oid
INNER JOIN pg_class tbl ON inx.indrelid=tbl.oid
INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid
INNER JOIN pg_attribute attr ON attr.attrelid=tbl.OID
ANDattr.attnum=inx.indkey[inx.No]
WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName)
ANDLOWER(tbl.relname)=LOWER(a_TableName)
GROUP BY inxcls.relname,inxam.amname,inx.indisunique,inx.indisprimary
ORDER BY inxcls.relname;
Время выполнения обеих версий функций у меня совпало на вывод данных в таблице результатов ушло 20 ms.
Поэтому я больше не буду плодить версии функций, т.к. желающие сами могут переделать их по своему вкусу или обратиться ко мне я бесплатно пришлю измененный вариант.
Замечание 5.
Упрощенная версия функции admtf_Table_Indexes без использования курсора может быть создана с помощью каталога pg_depend. В этом каталоге хранятся записи о зависимости объектов. При этому показывается не только зависимость таблицы от индекса, но и каждого ее атрибуту, который включен в описание индекса.
Таблица 10. Атрибуты каталога pg_depend, необходимые для реализации функции.
Поатрибутное описание зависимости таблицы от индекса создает впечатление, что можно обойтись без generate_subscripts.
Но эта реализация имеет два существенных недостатка:
Для атрибутов в составе индекса нельзя указать порядок сортировки, т.к. порядок сортировки указывается в массиве indoption;
Отсутствует информация о порядке следования атрибутов в описании индекса, т.к. этот порядок задается в массиве indkey.
Замечание 2.
