sistemnyj_katalog_v

Системный каталог в Postgresql

Системный каталог - набор таблиц и представлений описывающий все объекты кластера баз данных

Системные каталог

Системный каталог - набор таблиц и представлений описывающий все объекты кластера баз данных
pg_catalog - основная схема
information_schema - представление
В каждой базе данных кластера создается свой набор таблиц системного каталога, но существует объекты каталога, которые считаются общими для всего кластера, например список баз данных в кластере

Описания таблиц и представлений хранятся в таблице pg_class, а описание столбцов располагаются в отдельной таблице pg_attribute. Чтобы получить список столбцов конкретной таблицы, надо соединить pg_class и pg_attribute:


--Описание pg_class
\d pg_class


SELECT a.attname, a.atttypid
FROM pg_attribute a
WHERE a.attrelid = (
  SELECT oid FROM pg_class WHERE relname = 'employees'
)
AND a.attnum > 0;

Используя reg-типы, запрос можно написать проще, без явного обращения к pg_class:


SELECT a.attname, a.atttypid
FROM pg_attribute a
WHERE a.attrelid = 'employees'::regclass
AND a.attnum > 0;

В предыдущем примере мы преобразовали строку 'employees' к типу oid. Аналогично мы можем вывести oid как текстовое значение:


SELECT a.attname, a.atttypid::regtype
FROM pg_attribute a
WHERE a.attrelid = 'employees'::regclass
AND a.attnum > 0;

Полный список reg-типов


\dT reg*

Все таблицы и представления системного каталога начинаютсяс префикса pg_
Названия столбцов имеют трехбуквенный префикс, который, как правило, соответствует имени таблицы. После префикса нет знака подчеркивания. Имеются некоторые исключения из этого правила, например, столбец oid и другие


postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=#
testdb=#
testdb=# CREATE TABLE employees (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text,
manager integer
);
CREATE TABLE
testdb=#
testdb=#
testdb=#
testdb=# CREATE VIEW top_managers AS
testdb-# SELECT * FROM employees WHERE manager IS NULL;
CREATE VIEW
testdb=#
testdb=# CREATE TEMP TABLE emp_salaries (
testdb(# employee integer,
testdb(# salary numeric
testdb(# );
CREATE TABLE
testdb=#
testdb=#
testdb=# -- Просмотр информации о базе данных
testdb=# SELECT * FROM pg_database WHERE datname = 'testdb' \gx
-[ RECORD 1 ]-+------------
oid           | 16384
datname       | testdb
datdba        | 10
encoding      | 6
datcollate    | ru_RU.UTF-8
datctype      | ru_RU.UTF-8
datistemplate | f
datallowconn  | t
datconnlimit  | -1
datlastsysoid | 13798
datfrozenxid  | 726
datminmxid    | 1
dattablespace | 1663
datacl        |

testdb=#


testdb=#
testdb=# --Просмотр информации о схеме
testdb=# SELECT * FROM pg_namespace WHERE nspname = 'public' \gx
-[ RECORD 1 ]---------------------------------
oid      | 2200
nspname  | public
nspowner | 10
nspacl   | {postgres=UC/postgres,=UC/postgres}

testdb=#

Важная таблица pg_class хранит описание ряда объектов, таблиц, представлений, индексов, последовательностей. Все эти объекты в postgresql называются отношениями ( relation ), поэтому имеют префикс rel в названиях столбцов


testdb=#
testdb=# --Информация об отношениях
testdb=# SELECT relname, relkind, relnamespace, relfilenode, relowner, relpersistence
FROM pg_class WHERE relname ~ '^(emp|top)';
     relname      | relkind | relnamespace | relfilenode | relowner | relpersistence
------------------+---------+--------------+-------------+----------+----------------
 employees_id_seq | S       |         2200 |       16393 |       10 | p
 employees        | r       |         2200 |       16394 |       10 | p
 employees_pkey   | i       |         2200 |       16399 |       10 | p
 top_managers     | v       |         2200 |           0 |       10 | p
 emp_salaries     | r       |        16405 |       16407 |       10 | t
(5 rows)

testdb=#

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


--Информация о таблицах
testdb=# SELECT schemaname, tablename, tableowner
FROM pg_tables WHERE schemaname ~ '(public|pg_temp.+)';
 schemaname |  tablename   | tableowner
------------+--------------+------------
 public     | testdb       | postgres
 public     | employees    | postgres
 pg_temp_3  | emp_salaries | postgres
(3 rows)

testdb=#


--Описание таблицы pg_tables
\d+ pg_tables


testdb=#
testdb=# --Информация о представлениях
testdb=# SELECT *
FROM pg_views WHERE schemaname = 'public';
 schemaname |   viewname   | viewowner |              definition
------------+--------------+-----------+--------------------------------------
 public     | top_managers | postgres  |  SELECT employees.id,               +
            |              |           |     employees.name,                 +
            |              |           |     employees.manager               +
            |              |           |    FROM employees                   +
            |              |           |   WHERE (employees.manager IS NULL);
(1 row)

testdb=#


testdb=# --Информация об атрибутах
testdb=# \d pg_attribute
 attrelid       | oid       |           | not null |
 attname        | name      |           | not null |
 atttypid       | oid       |           | not null |
 attstattarget  | integer   |           | not null |
 attlen         | smallint  |           | not null |
 attnum         | smallint  |           | not null |
 attndims       | integer   |           | not null |
 attcacheoff    | integer   |           | not null |
 atttypmod      | integer   |           | not null |
 attbyval       | boolean   |           | not null |
....
...
..
.

pg_database - базы данных
pg_tablespace - табличные пространства
pg_authid, pg_auth_members - роли
pg_db_role_settings - настройки ролей и БД
pg_pltemplate - шаблоны процедурных языков
pg_shdepend, pg_shdescription - зависимости и описания для общих объектов
pg_locks, pg_prepared_statements - текущее состояние сервера
pg_stat* - собранная статистика
pg_settings - для просмотра установленных параметров
\dv pg_catalog.* - полный список

Структура системного каталога

Все команды psql, описывающие объекты, обращаются к таблицам системного каталога.
Чтобы увидеть эти запросы, следует установить переменную psql ECHO_HIDDEN


testdb=# \set ECHO_HIDDEN on
testdb=#
testdb=# \dt employees
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','t','s','')
  AND c.relname OPERATOR(pg_catalog.~) '^(employees)$' COLLATE pg_catalog.default
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

 public | employees | table | postgres

testdb=#
testdb=#
testdb=# \unset ECHO_HIDDEN
testdb=#

Ссылочная целостность обеспечивается с помощью ограничений, похожих на внешние ключи, но немного более сложных: ссылающийся столбец может быть массивом ссылающихся элементов, а неопределенность может обозначаться в нем нулем. Получить список таких псевдо-внешних ключей можно функцией pg_get_catalog_foreign_keys(). Например, pg_attribute "ссылается" на:


testdb=# SELECT *
FROM   pg_get_catalog_foreign_keys()
WHERE  fktable = 'pg_attribute'::regclass;
 pg_attribute | {attrelid}     | pg_class     | {oid}  | f        | f
 pg_attribute | {atttypid}     | pg_type      | {oid}  | f        | t
 pg_attribute | {attcollation} | pg_collation | {oid}  | f        | t

testdb=#

fktable, fkcols — ссылающаяся таблица и ее столбцы;
pktable, pkcols — ключ, на который ссылаются;
is_array — является ли ссылающийся столбец массивом;
is_opt — может ли ссылающийся столбец содержать 0.

Специальные типы данных

oid - (object identifier) уникальный идентификатор объекта в таблице сестемного каталога
reg* - псевдоним oid для некоторых таблиц системного каталога(Например regclass для pg_class), позволяют преобразовывать имена объектов в oid и обратно
Типы-псевдонимы OID сами по себе не вводят новых операций и отличаются только специализированными функциями ввода/вывода. Эти функции могут принимать и выводить не просто числовые значения, как тип oid, а символические имена системных объектов. Эти типы позволяют упростить поиск объектов по значениям OID. Например, чтобы выбрать из pg_attribute строки, относящиеся к таблице mytable, можно написать:
Преобразователь вводимого значения типа regclass находит таблицу согласно заданному пути поиска схем, так что он делает «всё правильно» автоматически


#Аналогично приведя идентификатор таблицы к типу regclass, можно получить символическое представление числового кода
SELECT * FROM pg_attribute WHERE attrelid = 'mytable'::regclass;
#Вместо
SELECT * FROM pg_attribute
  WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'mytable');

Источник: https://postgrespro.ru/docs/postgresql/16/datatype-oid